ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 基于AWR实现STATSPACK报告(2-系统效率)

基于AWR实现STATSPACK报告(2-系统效率)

原创 Linux操作系统 作者:redhouser 时间:2011-07-01 16:48:25 0 删除 编辑

Instance Efficiency Percentages
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
            Buffer Nowait %:   99.89       Redo NoWait %:  100.00
            Buffer  Hit   %:   95.39    In-memory Sort %:  100.00
            Library Hit   %:   99.52        Soft Parse %:   99.46
         Execute to Parse %:   76.88         Latch Hit %:   99.35
Parse CPU to Parse Elapsd %:    1.25     % Non-Parse CPU:   99.94
++++-分析-++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Buffer Nowait %-->round(100*(1-:bfwt/:gets),2) where:
bfwt-->total number of waits for all buffers
SELECT sum(e.wait_count) - sum(b.wait_count)
  FROM dba_hist_waitstat b, dba_hist_waitstat e
 WHERE b.dbid = e.dbid
   AND b.instance_number = e.instance_number
   AND b.dbid = &dbid
   AND b.instance_number = &inst_num
   AND b.snap_id = &bid
   AND e.snap_id = &eid

gets-->total number of buffers gets from cache
SELECT sum(e.VALUE) - sum(b.VALUE)
  FROM dba_hist_sysstat b, dba_hist_sysstat e
 WHERE b.dbid = e.dbid
   AND b.instance_number = e.instance_number
   AND b.dbid = &dbid
   AND b.instance_number = &inst_num
   AND b.snap_id = &bid
   AND e.snap_id = &eid
   AND b.stat_name = e.stat_name
   AND b.stat_name in('consistent gets from cache','db block gets from cache');

Redo NoWait %-->round(100*(1-:rlsr/:rent),2) where:
rlsr-->redo log space requests:
SELECT e.VALUE - b.VALUE
  FROM dba_hist_sysstat b, dba_hist_sysstat e
 WHERE b.dbid = e.dbid
   AND b.instance_number = e.instance_number
   AND b.dbid = &dbid
   AND b.instance_number = &inst_num
   AND b.snap_id = &bid
   AND e.snap_id = &eid
   AND b.stat_name = e.stat_name
   AND b.stat_name ='redo log space requests';
rent-->redo entries:
SELECT e.VALUE - b.VALUE
  FROM dba_hist_sysstat b, dba_hist_sysstat e
 WHERE b.dbid = e.dbid
   AND b.instance_number = e.instance_number
   AND b.dbid = &dbid
   AND b.instance_number = &inst_num
   AND b.snap_id = &bid
   AND e.snap_id = &eid
   AND b.stat_name = e.stat_name
   AND b.stat_name ='redo entries';

Buffer  Hit   %-->round(100*(1 - :phyrc/:gets),2) where:
phyrc-->physical reads cache:
SELECT e.VALUE - b.VALUE
  FROM dba_hist_sysstat b, dba_hist_sysstat e
 WHERE b.dbid = e.dbid
   AND b.instance_number = e.instance_number
   AND b.dbid = &dbid
   AND b.instance_number = &inst_num
   AND b.snap_id = &bid
   AND e.snap_id = &eid
   AND b.stat_name = e.stat_name
   AND b.stat_name ='physical reads cache';
gets-->total number of buffers gets from cache
SELECT sum(e.VALUE) - sum(b.VALUE)
  FROM dba_hist_sysstat b, dba_hist_sysstat e
 WHERE b.dbid = e.dbid
   AND b.instance_number = e.instance_number
   AND b.dbid = &dbid
   AND b.instance_number = &inst_num
   AND b.snap_id = &bid
   AND e.snap_id = &eid
   AND b.stat_name = e.stat_name
   AND b.stat_name in('consistent gets from cache','db block gets from cache');

In-memory Sort %-->round(100*:srtm/(:srtd+:srtm),2) where:
srtm-->sorts (memory)
SELECT e.VALUE - b.VALUE
  FROM dba_hist_sysstat b, dba_hist_sysstat e
 WHERE b.dbid = e.dbid
   AND b.instance_number = e.instance_number
   AND b.dbid = &dbid
   AND b.instance_number = &inst_num
   AND b.snap_id = &bid
   AND e.snap_id = &eid
   AND b.stat_name = e.stat_name
   AND b.stat_name ='sorts (memory)';
srtd-->sorts (disk)
SELECT e.VALUE - b.VALUE
  FROM dba_hist_sysstat b, dba_hist_sysstat e
 WHERE b.dbid = e.dbid
   AND b.instance_number = e.instance_number
   AND b.dbid = &dbid
   AND b.instance_number = &inst_num
   AND b.snap_id = &bid
   AND e.snap_id = &eid
   AND b.stat_name = e.stat_name
   AND b.stat_name ='sorts (disk)';

Library Hit   %-->round(100*:lhtr,2) where:
lhtr-->Library cache hit ratio=(ehsum - bhsum) / (epsum - bpsum)
(ehsum - bhsum)-->
SELECT sum(e.pinhits) - sum(b.pinhits)
  FROM dba_hist_librarycache b, dba_hist_librarycache e
 WHERE b.dbid = e.dbid
   AND b.instance_number = e.instance_number
   AND b.dbid = &dbid
   AND b.instance_number = &inst_num
   AND b.snap_id = &bid
   AND e.snap_id = &eid;  
(epsum - bpsum)-->
SELECT sum(e.pins) - sum(b.pins)
  FROM dba_hist_librarycache b, dba_hist_librarycache e
 WHERE b.dbid = e.dbid
   AND b.instance_number = e.instance_number
   AND b.dbid = &dbid
   AND b.instance_number = &inst_num
   AND b.snap_id = &bid
   AND e.snap_id = &eid;  

Soft Parse %-->round(100*(1-:hprs/:prse),2) where:
prse-->parse count (total)
SELECT e.VALUE - b.VALUE
  FROM dba_hist_sysstat b, dba_hist_sysstat e
 WHERE b.dbid = e.dbid
   AND b.instance_number = e.instance_number
   AND b.dbid = &dbid
   AND b.instance_number = &inst_num
   AND b.snap_id = &bid
   AND e.snap_id = &eid
   AND b.stat_name = e.stat_name
   AND b.stat_name ='parse count (total)';
hprs-->parse count (hard)
SELECT e.VALUE - b.VALUE
  FROM dba_hist_sysstat b, dba_hist_sysstat e
 WHERE b.dbid = e.dbid
   AND b.instance_number = e.instance_number
   AND b.dbid = &dbid
   AND b.instance_number = &inst_num
   AND b.snap_id = &bid
   AND e.snap_id = &eid
   AND b.stat_name = e.stat_name
   AND b.stat_name ='parse count (hard)';


Execute to Parse %-->round(100*(1-:prse/:exe),2) where:
prse-->parse count (total)
SELECT e.VALUE - b.VALUE
  FROM dba_hist_sysstat b, dba_hist_sysstat e
 WHERE b.dbid = e.dbid
   AND b.instance_number = e.instance_number
   AND b.dbid = &dbid
   AND b.instance_number = &inst_num
   AND b.snap_id = &bid
   AND e.snap_id = &eid
   AND b.stat_name = e.stat_name
   AND b.stat_name ='parse count (hard)';
exe-->execute count
SELECT e.VALUE - b.VALUE
  FROM dba_hist_sysstat b, dba_hist_sysstat e
 WHERE b.dbid = e.dbid
   AND b.instance_number = e.instance_number
   AND b.dbid = &dbid
   AND b.instance_number = &inst_num
   AND b.snap_id = &bid
   AND e.snap_id = &eid
   AND b.stat_name = e.stat_name
   AND b.stat_name ='execute count';

Latch Hit %-->round(100*(1-:lhr),2) where:
lhr-->latch hit ratio=( elmis - blmis ) / ( elget - blget )
( elmis - blmis )-->
SELECT sum(e.misses) - sum(b.misses)
  FROM dba_hist_latch b, dba_hist_latch e
 WHERE b.dbid = e.dbid
   AND b.instance_number = e.instance_number
   AND b.dbid = &dbid
   AND b.instance_number = &inst_num
   AND b.snap_id = &bid
   AND e.snap_id = &eid;
( elget - blget )-->
SELECT sum(e.gets) - sum(b.gets)
  FROM dba_hist_latch b, dba_hist_latch e
 WHERE b.dbid = e.dbid
   AND b.instance_number = e.instance_number
   AND b.dbid = &dbid
   AND b.instance_number = &inst_num
   AND b.snap_id = &bid
   AND e.snap_id = &eid;

Parse CPU to Parse Elapsd %-->round(100*:prscpu/:prsela,2) where:
prscpu-->parse time cpu
SELECT e.VALUE - b.VALUE
  FROM dba_hist_sysstat b, dba_hist_sysstat e
 WHERE b.dbid = e.dbid
   AND b.instance_number = e.instance_number
   AND b.dbid = &dbid
   AND b.instance_number = &inst_num
   AND b.snap_id = &bid
   AND e.snap_id = &eid
   AND b.stat_name = e.stat_name
   AND b.stat_name ='parse time cpu';
prsela-->parse time elapsed
SELECT e.VALUE - b.VALUE
  FROM dba_hist_sysstat b, dba_hist_sysstat e
 WHERE b.dbid = e.dbid
   AND b.instance_number = e.instance_number
   AND b.dbid = &dbid
   AND b.instance_number = &inst_num
   AND b.snap_id = &bid
   AND e.snap_id = &eid
   AND b.stat_name = e.stat_name
   AND b.stat_name ='parse time elapsed';

% Non-Parse CPU-->round(100*(1-(:prscpu/:tcpu)),2) where:
prscpu-->parse time cpu
SELECT e.VALUE - b.VALUE
  FROM dba_hist_sysstat b, dba_hist_sysstat e
 WHERE b.dbid = e.dbid
   AND b.instance_number = e.instance_number
   AND b.dbid = &dbid
   AND b.instance_number = &inst_num
   AND b.snap_id = &bid
   AND e.snap_id = &eid
   AND b.stat_name = e.stat_name
   AND b.stat_name ='parse time cpu';
tcpu-->CPU used by this session
SELECT e.VALUE - b.VALUE
  FROM dba_hist_sysstat b, dba_hist_sysstat e
 WHERE b.dbid = e.dbid
   AND b.instance_number = e.instance_number
   AND b.dbid = &dbid
   AND b.instance_number = &inst_num
   AND b.snap_id = &bid
   AND e.snap_id = &eid
   AND b.stat_name = e.stat_name
   AND b.stat_name ='CPU used by this session';
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

 Shared Pool Statistics        Begin   End
                               ------  ------
             Memory Usage %:   67.50   66.38
    % SQL with executions>1:   67.49   78.05
  % Memory for SQL w/exec>1:   76.06   86.14
++++-分析-++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Memory Usage %-->100*(1-:bfrm/:bspm) where:
bfrm-->
SELECT bytes
  FROM dba_hist_sgastat t
 WHERE t.dbid = &dbid
   AND t.instance_number = &inst_num
   AND t.snap_id = &bid
   AND t.NAME = 'free memory'
   AND t.pool IN ('shared pool', 'all pools');
bspm=bsp-->
SELECT VALUE
  FROM dba_hist_parameter t
 WHERE t.dbid = &dbid
   AND t.instance_number = &inst_num
   AND t.snap_id = &bid
   AND t.parameter_name = 'shared_pool_size';

% SQL with executions>1-->100*(1-single_use_sql/total_sql) where:
single_use_sql-->
SELECT single_use_sql
  FROM dba_hist_sql_summary t
 WHERE t.dbid = &dbid
   AND t.instance_number = &inst_num
   AND t.snap_id = &bid;
total_sql-->
SELECT total_sql
  FROM dba_hist_sql_summary t
 WHERE t.dbid = &dbid
   AND t.instance_number = &inst_num
   AND t.snap_id = &bid;

% Memory for SQL w/exec>1-->100*(1-single_use_sql_mem/total_sql_mem) where:
single_use_sql_mem-->
SELECT single_use_sql_mem
  FROM dba_hist_sql_summary t
 WHERE t.dbid = &dbid
   AND t.instance_number = &inst_num
   AND t.snap_id = &bid;
total_sql_mem-->
SELECT total_sql_mem
  FROM dba_hist_sql_summary t
 WHERE t.dbid = &dbid
   AND t.instance_number = &inst_num
   AND t.snap_id = &bid;
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Top 5 Timed Events                                                    Avg %Total
~~~~~~~~~~~~~~~~~~                                                   wait   Call
Event                                            Waits    Time (s)   (ms)   Time
----------------------------------------- ------------ ----------- ------ ------
gc buffer busy                                 138,659      11,485     83   69.3
CPU time                                                     3,248          19.6
db file scattered read                         663,066         380      1    2.3
db file sequential read                        416,636         348      1    2.1
gc cr multi block request                    1,176,466         240      0    1.4
++++-分析-++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
select event_name
     , waits
     , time
     , avwait
     , pctwtt
  from (select event_name, waits, time, pctwtt, avwait
          from (select e.event_name                               event_name
                     , e.total_waits - nvl(b.total_waits,0)  waits
                     , (e.time_waited_micro - nvl(b.time_waited_micro,0))/1000000  time
                     , decode ( (e.total_waits - nvl(b.total_waits, 0)), 0, to_number(NULL)
                             ,    ( (e.time_waited_micro - nvl(b.time_waited_micro,0)) / 1000.0 )
                                / (e.total_waits - nvl(b.total_waits,0))
                             )        avwait
                     , decode(&twt + &tcpu*10000, 0, 0,
                                100
                              * (e.time_waited_micro - nvl(b.time_waited_micro,0))
                              / (&twt + &tcpu*10000)                       
                              )                              pctwtt
                 from dba_hist_system_event b
                    , dba_hist_system_event e
                where b.snap_id(+)          = &snap_id1
                  and e.snap_id             = &snap_id2
                  and b.dbid(+)             = &dbid
                  and e.dbid                = &dbid
                  and b.instance_number(+)  = &instance_number
                  and e.instance_number     = &instance_number
                  and b.event_id(+)            = e.event_id
                  and e.total_waits         > nvl(b.total_waits,0)
                  and e.wait_class<>'Idle'
               union all
               select 'CPU time'                              event
                    , to_number(null)                         waits
                    , &tcpu/100                               time
                    , to_number(null)                         avwait
                    , decode(&twt + &tcpu*10000, 0, 0,
                               100
                             * &tcpu*10000
                             / (&twt + &tcpu*10000)
                            )                                 pctwait
                 from dual
                where &tcpu > 0
               )
         order by time desc, waits desc
       )
 where rownum <= 5;

twt-->total wait time for all non-idle events
SELECT sum(e.time_waited_micro) - sum(b.time_waited_micro)
  FROM dba_hist_system_event b, dba_hist_system_event e
 WHERE b.dbid = e.dbid
   AND b.instance_number = e.instance_number
   AND b.dbid = &dbid
   AND b.instance_number = &inst_num
   AND b.snap_id = &bid
   AND e.snap_id = &eid
   AND b.wait_class<>'Idle'
   AND e.wait_class<>'Idle';

tcpu-->CPU used by this session
SELECT e.VALUE - b.VALUE
  FROM dba_hist_sysstat b, dba_hist_sysstat e
 WHERE b.dbid = e.dbid
   AND b.instance_number = e.instance_number
   AND b.dbid = &dbid
   AND b.instance_number = &inst_num
   AND b.snap_id = &bid
   AND e.snap_id = &eid
   AND b.stat_name = e.stat_name
   AND b.stat_name ='CPU used by this session';

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Host CPU  (CPUs: 32)
~~~~~~~~              Load Average
                      Begin     End      User  System    Idle     WIO     WCPU
                    ------- -------   ------- ------- ------- ------- --------
                      0.00    0.01       4.06    1.28    94.67    0.49    4.99
++++-分析-++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
bncpu-->NUM_CPUS:
SELECT VALUE
  FROM dba_hist_osstat t
 WHERE t.dbid = &dbid
   AND t.instance_number = &inst_num
   AND t.snap_id = &bid
   AND t.stat_id = 0;
blod/elod-->LOAD:
SELECT VALUE
  FROM dba_hist_osstat t
 WHERE t.dbid = &dbid
   AND t.instance_number = &inst_num
   AND t.snap_id = &bid
   AND t.stat_id = 15;

User-->100*(:utic   / :ttic)
System-->100*(:stic   / :ttic)
Idle-->100*(:itic   / :ttic)
WIO-->100*(:iotic  / :ttic)
WCPU-->100*(:oscpuw / :ttic)
其中:
utic-->USER_TIME
SELECT e.VALUE - b.VALUE
  FROM dba_hist_osstat b, dba_hist_osstat e
 WHERE b.dbid = e.dbid
   AND b.instance_number = e.instance_number
   AND b.dbid = &dbid
   AND b.instance_number = &inst_num
   AND b.snap_id = &bid
   AND e.snap_id = &eid
   AND b.stat_id = e.stat_id
   AND b.stat_id = 3;
stic-->SYS_TIME
SELECT e.VALUE - b.VALUE
  FROM dba_hist_osstat b, dba_hist_osstat e
 WHERE b.dbid = e.dbid
   AND b.instance_number = e.instance_number
   AND b.dbid = &dbid
   AND b.instance_number = &inst_num
   AND b.snap_id = &bid
   AND e.snap_id = &eid
   AND b.stat_id = e.stat_id
   AND b.stat_id = 4;
itic-->IDLE_TIME
SELECT e.VALUE - b.VALUE
  FROM dba_hist_osstat b, dba_hist_osstat e
 WHERE b.dbid = e.dbid
   AND b.instance_number = e.instance_number
   AND b.dbid = &dbid
   AND b.instance_number = &inst_num
   AND b.snap_id = &bid
   AND e.snap_id = &eid
   AND b.stat_id = e.stat_id
   AND b.stat_id = 1;
iotic-->IOWAIT_TIME - solaris:
SELECT e.VALUE - b.VALUE
  FROM dba_hist_osstat b, dba_hist_osstat e
 WHERE b.dbid = e.dbid
   AND b.instance_number = e.instance_number
   AND b.dbid = &dbid
   AND b.instance_number = &inst_num
   AND b.snap_id = &bid
   AND e.snap_id = &eid
   AND b.stat_id = e.stat_id
   AND b.stat_id = 5;
oscpuw-->OS_CPU_WAIT_TIME - solaris:
SELECT e.VALUE - b.VALUE
  FROM dba_hist_osstat b, dba_hist_osstat e
 WHERE b.dbid = e.dbid
   AND b.instance_number = e.instance_number
   AND b.dbid = &dbid
   AND b.instance_number = &inst_num
   AND b.snap_id = &bid
   AND e.snap_id = &eid
   AND b.stat_id = e.stat_id
   AND b.stat_id = 13;
btic-->BUSY_TIME:
SELECT e.VALUE - b.VALUE
  FROM dba_hist_osstat b, dba_hist_osstat e
 WHERE b.dbid = e.dbid
   AND b.instance_number = e.instance_number
   AND b.dbid = &dbid
   AND b.instance_number = &inst_num
   AND b.snap_id = &bid
   AND e.snap_id = &eid
   AND b.stat_id = e.stat_id
   AND b.stat_id = 2;
ttic-->total ticks (cs),= btic + itic,
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Instance CPU
~~~~~~~~~~~~
              % of total CPU for Instance:   4.21
              % of busy  CPU for Instance:  78.92
  %DB time waiting for CPU - Resource Mgr:
++++-分析-++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
% of total CPU for Instance-->100* ((:dbcpu+:bgcpu)/1000000)/(:ttics)
% of busy  CPU for Instance-->100* ((:dbcpu+:bgcpu)/1000000)/ ((:btic)/100)
%DB time waiting for CPU - Resource Mgr-->100*(round(:rwtic/:dbtim))
其中:
dbcpu-->
SELECT e.VALUE - b.VALUE
  FROM dba_hist_sys_time_model b, dba_hist_sys_time_model e
 WHERE b.dbid = e.dbid
   AND b.instance_number = e.instance_number
   AND b.dbid = &dbid
   AND b.instance_number = &inst_num
   AND b.snap_id = &bid
   AND e.snap_id = &eid
   AND b.stat_name = e.stat_name
   AND b.stat_name ='DB CPU';

bgcpu-->
SELECT e.VALUE - b.VALUE
  FROM dba_hist_sys_time_model b, dba_hist_sys_time_model e
 WHERE b.dbid = e.dbid
   AND b.instance_number = e.instance_number
   AND b.dbid = &dbid
   AND b.instance_number = &inst_num
   AND b.snap_id = &bid
   AND e.snap_id = &eid
   AND b.stat_name = e.stat_name
   AND b.stat_name ='background cpu time';

btic-->BUSY_TIME:
SELECT e.VALUE - b.VALUE
  FROM dba_hist_osstat b, dba_hist_osstat e
 WHERE b.dbid = e.dbid
   AND b.instance_number = e.instance_number
   AND b.dbid = &dbid
   AND b.instance_number = &inst_num
   AND b.snap_id = &bid
   AND e.snap_id = &eid
   AND b.stat_id = e.stat_id
   AND b.stat_id = 2;
itic-->IDLE_TIME:
SELECT e.VALUE - b.VALUE
  FROM dba_hist_osstat b, dba_hist_osstat e
 WHERE b.dbid = e.dbid
   AND b.instance_number = e.instance_number
   AND b.dbid = &dbid
   AND b.instance_number = &inst_num
   AND b.snap_id = &bid
   AND e.snap_id = &eid
   AND b.stat_id = e.stat_id
   AND b.stat_id = 1;
ttics-->total ticks (s),(btic+itic)/100
dbtim-->
SELECT e.VALUE - b.VALUE
  FROM dba_hist_sys_time_model b, dba_hist_sys_time_model e
 WHERE b.dbid = e.dbid
   AND b.instance_number = e.instance_number
   AND b.dbid = &dbid
   AND b.instance_number = &inst_num
   AND b.snap_id = &bid
   AND e.snap_id = &eid
   AND b.stat_name = e.stat_name
   AND b.stat_name ='DB time';

rwtic-->RSRC_MGR_CPU_WAIT_TIME:
SELECT e.VALUE - b.VALUE
  FROM dba_hist_osstat b, dba_hist_osstat e
 WHERE b.dbid = e.dbid
   AND b.instance_number = e.instance_number
   AND b.dbid = &dbid
   AND b.instance_number = &inst_num
   AND b.snap_id = &bid
   AND e.snap_id = &eid
   AND b.stat_id = e.stat_id
   AND b.stat_id = 14;

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++


Memory Statistics                       Begin          End
~~~~~~~~~~~~~~~~~                ------------ ------------
                  Host Mem (MB):     65,536.0     65,536.0
                   SGA use (MB):      4,096.0      4,096.0
                   PGA use (MB):        694.5        697.6
    % Host Mem used for SGA+PGA:          7.3          7.3
++++-分析-++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Host Mem (MB)-->bpmem/1024/1024:
SELECT VALUE
  FROM dba_hist_osstat t
 WHERE t.dbid = &dbid
   AND t.instance_number = &inst_num
   AND t.snap_id = &bid
   AND t.stat_id = 1008;

SGA use (MB)-->bsgaalloc/1024/1024:
bsgaalloc-->
SELECT sum(value)
  FROM dba_hist_sga t
 WHERE t.dbid = &dbid
   AND t.instance_number = &inst_num
   AND t.snap_id = &bid;

PGA use (MB)-->bpgaalloc/1024/1024:
bpgaalloc-->
SELECT value
  FROM dba_hist_pgastat t
 WHERE t.dbid = &dbid
   AND t.instance_number = &inst_num
   AND t.snap_id = &bid
   AND t.name='total PGA allocated';

% Host Mem used for SGA+PGA:-->100*(bpgaalloc + bsgaalloc)/bpmem
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Time Model System Stats  DB/Inst: ORADB/oraDB1  Snaps: 66-67
-> Ordered by % of DB time desc, Statistic name

Statistic                                       Time (s) % of DB time
----------------------------------- -------------------- ------------
sql execute elapsed time                        16,696.4         98.3
DB CPU                                           3,803.2         22.4
parse time elapsed                                 145.9           .9
hard parse elapsed time                             59.1           .3
repeated bind elapsed time                           2.5           .0
sequence load elapsed time                           2.2           .0
connection management call elapsed                   0.7           .0
PL/SQL compilation elapsed time                      0.7           .0
hard parse (sharing criteria) elaps                  0.6           .0
PL/SQL execution elapsed time                        0.6           .0
...
++++-分析-++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
SELECT statnam,
       tdif / 1024 / 1024 tdifs,
       decode(order_col, 0, 100 * tdif / &DBtime, to_number(NULL)) pctdb,
       order_col
  FROM (SELECT b.stat_name statnam,
               (e.VALUE - b.VALUE) tdif,
               decode(b.stat_name,
                      'DB time',
                      1,
                      'background cpu time',
                      2,
                      'background elapsed time',
                      2,
                      0) order_col
          FROM dba_hist_sys_time_model e, dba_hist_sys_time_model b
         WHERE b.snap_id = &bid
           AND e.snap_id = &eid
           AND b.dbid = &dbid
           AND e.dbid = &dbid
           AND b.instance_number = &inst_num
           AND e.instance_number = &inst_num
           AND b.stat_id = e.stat_id
           AND e.VALUE - b.VALUE > 0)
 ORDER BY order_col, decode(pctdb, NULL, tdifs, pctdb) DESC;

DBtime/1000/1000-->
SELECT e.VALUE - b.VALUE
  FROM dba_hist_sys_time_model b, dba_hist_sys_time_model e
 WHERE b.dbid = e.dbid
   AND b.instance_number = e.instance_number
   AND b.dbid = &dbid
   AND b.instance_number = &inst_num
   AND b.snap_id = &bid
   AND e.snap_id = &eid
   AND b.stat_name = e.stat_name
   AND b.stat_name ='DB time';
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++


 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/18922393/viewspace-701147/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2011-05-26

  • 博文量
    211
  • 访问量
    810051