基於AWR實現STATSPACK報告(2-系統效率)

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 %--&gtround(100*(1-:bfwt/:gets),2) where:
bfwt--&gttotal 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--&gttotal 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 %--&gtround(100*(1-:rlsr/:rent),2) where:
rlsr--&gtredo 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--&gtredo 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   %--&gtround(100*(1 - :phyrc/:gets),2) where:
phyrc--&gtphysical 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--&gttotal 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 %--&gtround(100*:srtm/(:srtd+:srtm),2) where:
srtm--&gtsorts (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--&gtsorts (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   %--&gtround(100*:lhtr,2) where:
lhtr--&gtLibrary cache hit ratio=(ehsum - bhsum) / (epsum - bpsum)
(ehsum - bhsum)--&gt
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)--&gt
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 %--&gtround(100*(1-:hprs/:prse),2) where:
prse--&gtparse 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--&gtparse 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 %--&gtround(100*(1-:prse/:exe),2) where:
prse--&gtparse 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--&gtexecute 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 %--&gtround(100*(1-:lhr),2) where:
lhr--&gtlatch hit ratio=( elmis - blmis ) / ( elget - blget )
( elmis - blmis )--&gt
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 )--&gt
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 %--&gtround(100*:prscpu/:prsela,2) where:
prscpu--&gtparse 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--&gtparse 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--&gtround(100*(1-(:prscpu/:tcpu)),2) where:
prscpu--&gtparse 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--&gtCPU 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 %--&gt100*(1-:bfrm/:bspm) where:
bfrm--&gt
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--&gt
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--&gt100*(1-single_use_sql/total_sql) where:
single_use_sql--&gt
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--&gt
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--&gt100*(1-single_use_sql_mem/total_sql_mem) where:
single_use_sql_mem--&gt
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--&gt
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--&gttotal 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--&gtCPU 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--&gtNUM_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--&gtLOAD:
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--&gt100*(:utic   / :ttic)
System--&gt100*(:stic   / :ttic)
Idle--&gt100*(:itic   / :ttic)
WIO--&gt100*(:iotic  / :ttic)
WCPU--&gt100*(:oscpuw / :ttic)
其中：
utic--&gtUSER_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--&gtSYS_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--&gtIDLE_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--&gtIOWAIT_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--&gtOS_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--&gtBUSY_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--&gttotal 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--&gt100* ((:dbcpu+:bgcpu)/1000000)/(:ttics)
% of busy  CPU for Instance--&gt100* ((:dbcpu+:bgcpu)/1000000)/ ((:btic)/100)
%DB time waiting for CPU - Resource Mgr--&gt100*(round(:rwtic/:dbtim))
其中：
dbcpu--&gt
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--&gt
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--&gtBUSY_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--&gtIDLE_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--&gttotal ticks (s),(btic+itic)/100
dbtim--&gt
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--&gtRSRC_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)--&gtbpmem/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)--&gtbsgaalloc/1024/1024:
bsgaalloc--&gt
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)--&gtbpgaalloc/1024/1024:
bpgaalloc--&gt
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:--&gt100*(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--&gt
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';
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++