ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 基于AWR实现STATSPACK报告(6-实例组件)

基于AWR实现STATSPACK报告(6-实例组件)

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

Instance Activity Stats  DB/Inst: ORADB/oraDB1  Snaps: 66-67
Statistic                                      Total     per Second    per Trans
--------------------------------- ------------------ -------------- ------------
CPU used by this session                     324,813          107.6          3.3
CPU used when call started                   324,277          107.5          3.3
CR blocks created                             28,894            9.6          0.3
Cached Commit SCN referenced                       0            0.0          0.0
Commit SCN cached                                  0            0.0          0.0
DB time                                    2,011,619          666.5         20.2
DBWR checkpoint buffers written               55,278           18.3          0.6
...
++++-分析-++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
SELECT b.stat_name st,
       e.VALUE - b.VALUE dif,
       round((e.VALUE - b.VALUE) / &ela, 2) ps,
       round((e.VALUE - b.VALUE) / &tran, 2) pt
  FROM dba_hist_sysstat b, dba_hist_sysstat e
 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_name = e.stat_name
   AND e.stat_name NOT IN
       ('logons current', 'opened cursors current',
        'workarea memory allocated', 'session cursor cache count')
   AND e.VALUE >= b.VALUE
   AND e.VALUE > 0
 ORDER BY st;
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++


Instance Activity Stats  DB/Inst: ORADB/oraDB1  Snaps: 66-67
-> Statistics with absolute values (should not be diffed)
Statistic                             Begin Value       End Value
--------------------------------- --------------- ---------------
logons current                                249             224
opened cursors current                      2,830           2,594
session cursor cache count                 27,675          28,892
++++-分析-++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
SELECT b.stat_name st, b.VALUE begin_value, e.VALUE end_value
  FROM dba_hist_sysstat b, dba_hist_sysstat e
 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_name = e.stat_name
   AND e.stat_name IN
       ('logons current', 'opened cursors current',
        'workarea memory allocated', 'session cursor cache count')
   AND (b.VALUE > 0 OR e.VALUE > 0);
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++


Instance Activity Stats  DB/Inst: ORADB/oraDB1  Snaps: 66-67
-> Statistics identified by '(derived)' come from sources other than SYSSTAT
Statistic                                      Total  per Hour
--------------------------------- ------------------ ---------
log switches (derived)                             1      1.19
++++-分析-++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
SELECT 'log switches (derived)' st,
       e.sequence# - b.sequence# dif,
       (e.sequence# - b.sequence#) / (&ela / 3600) ph
  FROM dba_hist_thread e, dba_hist_thread 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.thread# = e.thread#
   AND b.thread_instance_number = e.thread_instance_number
   AND e.thread_instance_number = &inst_num;
其中:
ela-->
SELECT (CAST(e.end_interval_time AS DATE) -
       CAST(b.end_interval_time AS DATE)) * 1440 * 60
  FROM dba_hist_snapshot b, dba_hist_snapshot 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;
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

 

OS Statistics  DB/Inst: ORADB/oraDB1  Snaps: 66-67
-> ordered by statistic type (CPU use, Virtual Memory, Hardware Config), Name
Statistic                                  Total
------------------------- ----------------------
NUM_LCPUS                                      0
NUM_VCPUS                                      0
BUSY_TIME                                516,300
IDLE_TIME                              9,162,519
IOWAIT_TIME                               47,632
SYS_TIME                                 123,425
USER_TIME                                392,875
LOAD                                           0
OS_CPU_WAIT_TIME                         482,700
PHYSICAL_MEMORY_BYTES             68,719,476,736
NUM_CPUS                                      32
NUM_CPU_CORES                                 16
...
++++-分析-++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
SELECT osn.stat_name osn,
       decode(osn.stat_id,
              0,
              e.VALUE -- NUM_CPUS
             ,
              15,
              e.VALUE -- LOAD
             ,
              16,
              e.VALUE -- NUM_CPU_CORES
             ,
              17,
              e.VALUE -- NUM_CPU_SOCKETS
             ,
              1008,
              e.VALUE -- PHYSICAL_MEMORY_BYTES
             ,
              e.VALUE - b.VALUE) dif,
       (to_number(decode(sign(instrb(osn.stat_name, 'TIME')), 1, 1, 0)) +
       to_number(decode(sign(instrb(osn.stat_name, 'LOAD')), 1, 2, 0)) +
       to_number(decode(sign(instrb(osn.stat_name, 'CPU_WAIT')), 1, 3, 0)) +
       to_number(decode(sign(instrb(osn.stat_name, 'VM_')), 1, 4, 0)) +
       to_number(decode(sign(instrb(osn.stat_name, 'PHYSICAL_MEMORY')),
                         1,
                         5,
                         0)) +
       to_number(decode(sign(instrb(osn.stat_name, 'NUM_CPU')), 1, 6, 0))) styp
  FROM dba_hist_osstat b, dba_hist_osstat e, dba_hist_osstat_name osn
 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 osn.stat_id = e.stat_id
   AND osn.dbid = e.dbid
   AND osn.stat_name NOT LIKE 'AVG_%'
   AND e.VALUE >= b.VALUE
   AND e.VALUE > 0
 ORDER BY styp, osn;
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++


Session Wait Events  
...
Session Time Model Statistics
...
Session Statistics
...
++++-分析-++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
AWR中与SESSION相关的视图只有:
DBA_HIST_ACTIVE_SESS_HISTORY--与SESSION相关
DBA_HIST_SESS_TIME_STATS--与SESSION_TYPE相关
DBA_HIST_SESSMETRIC_HISTORY--与SESSION相关
没有类似stats$sesstat、stats$sess_time_model、stats$session_event的数据。
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++


Tablespace IO Stats  DB/Inst: ORADB/oraDB1  Snaps: 66-67
->ordered by IOs (Reads + Writes) desc
Tablespace
------------------------------
                 Av      Av     Av                    Av        Buffer Av Buf
         Reads Reads/s Rd(ms) Blks/Rd       Writes Writes/s      Waits Wt(ms)
-------------- ------- ------ ------- ------------ -------- ---------- ------
NET_TRANS
     2,103,126     697    0.9     2.2        3,387        1          1   10.0
USERS
           751       0    1.2     1.1          169        0          0    0.0
SYSTEM
           900       0    1.7     1.0           12        0          1    0.0
NET_SIGN
            98       0    6.6     1.0          753        0          2    0.0
UNDOTBS1
             3       0    0.0     1.0          582        0          2    0.0
SYSAUX
             6       0    1.7     1.0            0        0          0    0.0
TEMP
             1       0    0.0     1.0            1        0          0    0.0
UNDOTBS2
             1       0    0.0     1.0            0        0     39,603    1.2
++++-分析-++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
SELECT e.tsname,
       SUM(e.phyrds - nvl(b.phyrds, 0)) reads,
       SUM(e.phyrds - nvl(b.phyrds, 0)) / &ela rps,
       decode(SUM(e.phyrds - nvl(b.phyrds, 0)),
              0,
              0,
              (SUM(e.readtim - nvl(b.readtim, 0)) /
              SUM(e.phyrds - nvl(b.phyrds, 0))) * 10) atpr,
       decode(SUM(e.phyrds - nvl(b.phyrds, 0)),
              0,
              to_number(NULL),
              SUM(e.phyblkrd - nvl(b.phyblkrd, 0)) /
              SUM(e.phyrds - nvl(b.phyrds, 0))) bpr,
       SUM(e.phywrts - nvl(b.phywrts, 0)) writes,
       SUM(e.phywrts - nvl(b.phywrts, 0)) / &ela wps,
       SUM(e.wait_count - nvl(b.wait_count, 0)) waits,
       decode(SUM(e.wait_count - nvl(b.wait_count, 0)),
              0,
              0,
              (SUM(e.TIME - nvl(b.TIME, 0)) /
              SUM(e.wait_count - nvl(b.wait_count, 0))) * 10) atpwt,
       SUM(e.phyrds - nvl(b.phyrds, 0)) +
       SUM(e.phywrts - nvl(b.phywrts, 0)) ios
  FROM dba_hist_filestatxs e, dba_hist_filestatxs b
 WHERE b.snap_id(+) = &bid
   AND e.snap_id = &eid
   AND b.dbid(+) = &dbid
   AND e.dbid = &dbid
   AND b.dbid(+) = e.dbid
   AND b.instance_number(+) = &inst_num
   AND e.instance_number = &inst_num
   AND b.instance_number(+) = e.instance_number
   AND b.tsname(+) = e.tsname
   AND b.filename(+) = e.filename
   AND ((e.phyrds - nvl(b.phyrds, 0)) + (e.phywrts - nvl(b.phywrts, 0))) > 0
 GROUP BY e.tsname
UNION ALL
SELECT e.tsname tbsp,
       SUM(e.phyrds - nvl(b.phyrds, 0)) reads,
       SUM(e.phyrds - nvl(b.phyrds, 0)) / &ela rps,
       decode(SUM(e.phyrds - nvl(b.phyrds, 0)),
              0,
              0,
              (SUM(e.readtim - nvl(b.readtim, 0)) /
              SUM(e.phyrds - nvl(b.phyrds, 0))) * 10) atpr,
       decode(SUM(e.phyrds - nvl(b.phyrds, 0)),
              0,
              to_number(NULL),
              SUM(e.phyblkrd - nvl(b.phyblkrd, 0)) /
              SUM(e.phyrds - nvl(b.phyrds, 0))) bpr,
       SUM(e.phywrts - nvl(b.phywrts, 0)) writes,
       SUM(e.phywrts - nvl(b.phywrts, 0)) / &ela wps,
       SUM(e.wait_count - nvl(b.wait_count, 0)) waits,
       decode(SUM(e.wait_count - nvl(b.wait_count, 0)),
              0,
              0,
              (SUM(e.TIME - nvl(b.TIME, 0)) /
              SUM(e.wait_count - nvl(b.wait_count, 0))) * 10) atpwt,
       SUM(e.phyrds - nvl(b.phyrds, 0)) +
       SUM(e.phywrts - nvl(b.phywrts, 0)) ios
  FROM DBA_HIST_TEMPSTATXS e, DBA_HIST_TEMPSTATXS b
 WHERE b.snap_id(+) = &bid
   AND e.snap_id = &eid
   AND b.dbid(+) = &dbid
   AND e.dbid = &dbid
   AND b.dbid(+) = e.dbid
   AND b.instance_number(+) = &inst_num
   AND e.instance_number = &inst_num
   AND b.instance_number(+) = e.instance_number
   AND b.tsname(+) = e.tsname
   AND b.filename(+) = e.filename
   AND ((e.phyrds - nvl(b.phyrds, 0)) + (e.phywrts - nvl(b.phywrts, 0))) > 0
 GROUP BY e.tsname
 ORDER BY ios DESC;
其中:
ela-->
SELECT (CAST(e.end_interval_time AS DATE) -
       CAST(b.end_interval_time AS DATE)) * 1440 * 60
  FROM dba_hist_snapshot b, dba_hist_snapshot 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;
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++


File IO Stats  DB/Inst: ORADB/oraDB1  Snaps: 66-67
->Mx Rd Bkt: Max bucket time for single block read
->ordered by Tablespace, File
Tablespace               Filename
------------------------ ----------------------------------------------------
                        Av   Mx                                             Av
                 Av     Rd   Rd    Av                    Av        Buffer BufWt
         Reads Reads/s (ms)  Bkt Blks/Rd       Writes Writes/s      Waits  (ms)
-------------- ------- ----- --- ------- ------------ -------- ---------- ------
NET_DATA              /dev/rPO_NET_DATA
       308,793     102   1.3 ###     2.9       16,310        5     61,792  178.5

NET_INDEX             /dev/rPO_NET_INDEX
        65,000      22   2.7 ###     1.0       41,537       14         69    1.9

SYSAUX                   /dev/rPO_AUX1
             6       0   1.7   8     1.0            0        0          0

SYSTEM                   /dev/rPO_SYS1
           900       0   1.7 ###     1.0           12        0          1    0.0
...
++++-分析-++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
***由于无stats$file_histogram、stats$temp_histogram,删除mrt/max read time列的统计
SELECT e.tsname,
       e.filename,
       e.phyrds - nvl(b.phyrds, 0) reads,
       (e.phyrds - nvl(b.phyrds, 0)) / &ela rps,
       decode((e.phyrds - nvl(b.phyrds, 0)),
              0,
              to_number(NULL),
              ((e.readtim - nvl(b.readtim, 0)) /
              (e.phyrds - nvl(b.phyrds, 0))) * 10) atpr,
       decode((e.phyrds - nvl(b.phyrds, 0)),
              0,
              to_number(NULL),
              (e.phyblkrd - nvl(b.phyblkrd, 0)) /
              (e.phyrds - nvl(b.phyrds, 0))) bpr,
       e.phywrts - nvl(b.phywrts, 0) writes,
       (e.phywrts - nvl(b.phywrts, 0)) / &ela wps,
       e.wait_count - nvl(b.wait_count, 0) waits,
       decode((e.wait_count - nvl(b.wait_count, 0)),
              0,
              to_number(NULL),
              ((e.TIME - nvl(b.TIME, 0)) /
              (e.wait_count - nvl(b.wait_count, 0))) * 10) atpwt
  FROM dba_hist_filestatxs e, dba_hist_filestatxs b
 WHERE b.snap_id(+) = &bid
   AND e.snap_id = &eid
   AND b.dbid(+) = &dbid
   AND e.dbid = &dbid
   AND b.dbid(+) = e.dbid
   AND b.instance_number(+) = &inst_num
   AND e.instance_number = &inst_num
   AND b.instance_number(+) = e.instance_number
   AND b.tsname(+) = e.tsname
   AND b.filename(+) = e.filename
   AND ((e.phyrds - nvl(b.phyrds, 0)) + (e.phywrts - nvl(b.phywrts, 0))) > 0
UNION ALL
SELECT e.tsname,
       e.filename,
       e.phyrds - nvl(b.phyrds, 0) reads,
       (e.phyrds - nvl(b.phyrds, 0)) / &ela rps,
       decode((e.phyrds - nvl(b.phyrds, 0)),
              0,
              to_number(NULL),
              ((e.readtim - nvl(b.readtim, 0)) /
              (e.phyrds - nvl(b.phyrds, 0))) * 10) atpr,
       decode((e.phyrds - nvl(b.phyrds, 0)),
              0,
              to_number(NULL),
              (e.phyblkrd - nvl(b.phyblkrd, 0)) /
              (e.phyrds - nvl(b.phyrds, 0))) bpr,
       e.phywrts - nvl(b.phywrts, 0) writes,
       (e.phywrts - nvl(b.phywrts, 0)) / &ela wps,
       e.wait_count - nvl(b.wait_count, 0) waits,
       decode((e.wait_count - nvl(b.wait_count, 0)),
              0,
              to_number(NULL),
              ((e.TIME - nvl(b.TIME, 0)) /
              (e.wait_count - nvl(b.wait_count, 0))) * 10) atpwt
  FROM dba_hist_tempstatxs e, dba_hist_tempstatxs b
 WHERE b.snap_id(+) = &bid
   AND e.snap_id = &eid
   AND b.dbid(+) = &dbid
   AND e.dbid = &dbid
   AND b.dbid(+) = e.dbid
   AND b.instance_number(+) = &inst_num
   AND e.instance_number = &inst_num
   AND b.instance_number(+) = e.instance_number
   AND b.tsname(+) = e.tsname
   AND b.filename(+) = e.filename
   AND ((e.phyrds - nvl(b.phyrds, 0)) + (e.phywrts - nvl(b.phywrts, 0))) > 0
 ORDER BY tsname, filename;
其中:
ela-->
SELECT (CAST(e.end_interval_time AS DATE) -
       CAST(b.end_interval_time AS DATE)) * 1440 * 60
  FROM dba_hist_snapshot b, dba_hist_snapshot 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;
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++


File Read Histogram Stats  DB/Inst: ORADB/oraDB1  Snaps: 66-67
->Number of single block reads in each time range
->ordered by Tablespace, File
Tablespace               Filename
------------------------ ----------------------------------------------------
    0 - 2 ms     2 - 4 ms    4 - 8 ms     8 - 16 ms   16 - 32 ms       32+ ms
------------ ------------ ------------ ------------ ------------ ------------
NET_DATA              /dev/rPO_NET_DATA
      48,879        1,186        2,290          442          145          288

NET_INDEX             /dev/rPO_NET_INDEX
      45,953        2,707       12,980        2,617          235          516

SYSAUX                   /dev/rPO_AUX1
           5            0            1            0            0            0

SYSTEM                   /dev/rPO_SYS1
         755           29           99           16            5            8

UNDOTBS1                 /dev/rPO_UNDO1_A
           2            0            0            0            0            0
                         /dev/rPO_UNDO2_A
           1            0            0            0            0            0
...
++++-分析-++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
***由于无stats$file_histogram、stats$temp_histogram,删除mrt/max read time列的统计
select fse.tsname
     , fse.filename
     , sum(case when     (0 <= e.singleblkrdtim_milli)
                     and (e.singleblkrdtim_milli <= 2)
                then (e.singleblkrds - nvl(b.singleblkrds,0)) else 0 end) to2
     , sum(case when     ( e.singleblkrdtim_milli = 4)
                then (e.singleblkrds - nvl(b.singleblkrds,0)) else 0 end) to4
     , sum(case when     ( e.singleblkrdtim_milli = 8)
                then (e.singleblkrds - nvl(b.singleblkrds,0)) else 0 end) to8
     , sum(case when     ( e.singleblkrdtim_milli = 16)
                then (e.singleblkrds - nvl(b.singleblkrds,0)) else 0 end) to16
     , sum(case when     ( e.singleblkrdtim_milli = 32)
                then (e.singleblkrds - nvl(b.singleblkrds,0)) else 0 end) to32
     , sum(case when   32 < e.singleblkrdtim_milli
                then (e.singleblkrds - nvl(b.singleblkrds,0)) else 0 end) over32
  from stats$file_histogram e
     , stats$file_histogram b
     , stats$filestatxs     fse
 where b.snap_id(+)         = :bid
   and e.snap_id            = :eid
   and e.dbid               = :dbid
   and b.dbid(+)            = e.dbid
   and e.instance_number    = :inst_num
   and b.instance_number(+) = e.instance_number
   and b.file#(+)           = e.file#
   and b.singleblkrdtim_milli(+) = e.singleblkrdtim_milli
   and fse.snap_id          = e.snap_id
   and fse.dbid             = e.dbid
   and fse.instance_number  = e.instance_number
   and fse.file#            = e.file#
   and (e.singleblkrds - nvl(b.singleblkrds,0)) > 0
   and '&&file_histogram' = 'Y'
 group by fse.tsname
        , fse.filename
union all
select tse.tsname
     , tse.filename
     , sum(case when     (0 <= e.singleblkrdtim_milli)
                     and (e.singleblkrdtim_milli <= 2)
                then (e.singleblkrds - nvl(b.singleblkrds,0)) else 0 end) to2
     , sum(case when     ( e.singleblkrdtim_milli = 4)
                then (e.singleblkrds - nvl(b.singleblkrds,0)) else 0 end) to4
     , sum(case when     ( e.singleblkrdtim_milli = 8)
                then (e.singleblkrds - nvl(b.singleblkrds,0)) else 0 end) to8
     , sum(case when     ( e.singleblkrdtim_milli = 16)
                then (e.singleblkrds - nvl(b.singleblkrds,0)) else 0 end) to16
     , sum(case when     ( e.singleblkrdtim_milli = 32)
                then (e.singleblkrds - nvl(b.singleblkrds,0)) else 0 end) to32
     , sum(case when   32 < e.singleblkrdtim_milli
                then (e.singleblkrds - nvl(b.singleblkrds,0)) else 0 end) over32
  from stats$temp_histogram e
     , stats$temp_histogram b
     , stats$tempstatxs     tse
 where b.snap_id(+)         = :bid
   and e.snap_id            = :eid
   and e.dbid               = :dbid
   and b.dbid(+)            = e.dbid
   and e.instance_number    = :inst_num
   and b.instance_number(+) = e.instance_number
   and b.file#(+)           = e.file#
   and b.singleblkrdtim_milli(+) = e.singleblkrdtim_milli
   and tse.snap_id          = e.snap_id
   and tse.dbid             = e.dbid
   and tse.instance_number  = e.instance_number
   and tse.file#            = e.file#
   and (e.singleblkrds - nvl(b.singleblkrds,0)) > 0
   and '&&file_histogram' = 'Y'
 group by tse.tsname
        , tse.filename;
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

 

Buffer Pool Statistics  DB/Inst: ORADB/oraDB1  Snaps: 66-67
-> Standard block size Pools  D: default,  K: keep,  R: recycle
-> Default Pools for other block sizes: 2k, 4k, 8k, 16k, 32k
-> Buffers: the number of buffers.  Units of K, M, G are divided by 1000
                                                            Free Writ     Buffer
            Pool         Buffer     Physical    Physical  Buffer Comp       Busy
P   Buffers Hit%           Gets        Reads      Writes   Waits Wait      Waits
--- ------- ---- -------------- ------------ ----------- ------- ---- ----------
D      406K   95    120,760,945    5,573,882      74,655       0    0    138,231
++++-分析-++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
select replace(e.block_size/1024||'k', &bs/1024||'k', substr(e.name,1,1)) name
    ,  lpad(case
              when e.set_msize <= 9999
                   then to_char(e.set_msize)||' '
              when trunc((e.set_msize)/1000) <= 9999
                   then to_char(trunc((e.set_msize)/1000))||'K'
              when trunc((e.set_msize)/1000000) <= 9999
                   then to_char(trunc((e.set_msize)/1000000))||'M'
              when trunc((e.set_msize)/1000000000) <= 9999
                   then to_char(trunc((e.set_msize)/1000000000))||'G'
              when trunc((e.set_msize)/1000000000000) <= 9999
                   then to_char(trunc((e.set_msize)/1000000000000))||'T'
              else substr(to_char(trunc((e.set_msize)/1000000000000000))||'P', 1, 5) end
            , 7, ' ') numbufs
     , decode(   e.db_block_gets     - nvl(b.db_block_gets,0)
              +  e.consistent_gets   - nvl(b.consistent_gets,0)
             , 0, to_number(null)
             , (100* (1 - (  (e.physical_reads - nvl(b.physical_reads,0))
                           / (  e.db_block_gets     - nvl(b.db_block_gets,0)
                              + e.consistent_gets   - nvl(b.consistent_gets,0))
                          )
                     )
               )
             )                                                poolhr
     ,    e.db_block_gets    - nvl(b.db_block_gets,0)
       +  e.consistent_gets  - nvl(b.consistent_gets,0)       buffs
     , e.physical_reads      - nvl(b.physical_reads,0)        phread
     , e.physical_writes     - nvl(b.physical_writes,0)       phwrite
     , e.free_buffer_wait    - nvl(b.free_buffer_wait,0)      fbwait
     , e.write_complete_wait - nvl(b.write_complete_wait,0)   wcwait
     , e.buffer_busy_wait    - nvl(b.buffer_busy_wait,0)      bbwait
  from DBA_HIST_BUFFER_POOL_STAT b
     , DBA_HIST_BUFFER_POOL_STAT e
 where b.snap_id(+)         = &bid
   and e.snap_id            = &eid
   and b.dbid(+)            = &dbid
   and e.dbid               = &dbid
   and b.dbid(+)            = e.dbid
   and b.instance_number(+) = &inst_num
   and e.instance_number    = &inst_num
   and b.instance_number(+) = e.instance_number
   and b.id(+)              = e.id
 order by e.name;
其中:
bs-->db_block_size
  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 = 'db_block_size';
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++


Instance Recovery Stats  DB/Inst: ORADB/oraDB1  Snaps: 66-67
-> B: Begin snapshot,  E: End snapshot
  Targt Estd                                  Log File  Log Ckpt    Log Ckpt
  MTTR  MTTR   Recovery   Actual    Target      Size     Timeout    Interval
   (s)   (s)   Estd IOs  Redo Blks Redo Blks Redo Blks  Redo Blks  Redo Blks
- ----- ----- ---------- --------- --------- ---------- --------- ------------
B     0    17       8526     36563    207744    1883700    207744
E     0    16       8081     36154    196848    1883700    196848
++++-分析-++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
select 'B'                            beg
     , target_mttr                    tm
     , estimated_mttr                 em
     , recovery_estimated_ios         rei
     , actual_redo_blks               arb
     , target_redo_blks               trb
     , log_file_size_redo_blks        lfrb
     , log_chkpt_timeout_redo_blks    lctrb
     , log_chkpt_interval_redo_blks   lcirb
     , snap_id                        snid
  from DBA_HIST_INSTANCE_RECOVERY b
 where b.snap_id         = &bid
   and b.dbid            = &dbid
   and b.instance_number = &inst_num
union all
select 'E'                            beg
     , target_mttr                    tm
     , estimated_mttr                 em
     , recovery_estimated_ios         rei
     , actual_redo_blks               arb
     , target_redo_blks               trb
     , log_file_size_redo_blks        lfrb
     , log_chkpt_timeout_redo_blks    lctrb
     , log_chkpt_interval_redo_blks   lcirb
     , snap_id                        snid
  from DBA_HIST_INSTANCE_RECOVERY e
 where e.snap_id         = &eid
   and e.dbid            = &dbid
   and e.instance_number = &inst_num
order by snid;
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++


Buffer Pool Advisory  DB/Inst: ORADB/oraDB1  End Snap: 67
-> Only rows with estimated physical reads >0 are displayed
-> ordered by Pool, Block Size, Buffers For Estimate
                                   Est
                                  Phys      Estimated                   Est
    Size for  Size      Buffers   Read     Phys Reads     Est Phys % dbtime
P    Est (M) Factr  (thousands)  Factr    (thousands)    Read Time  for Rds
--- -------- ----- ------------ ------ -------------- ------------ --------
D        336    .1           40    3.2         43,324       20,519     29.9
D        672    .2           81    1.5         20,804        8,022     11.7
D      1,008    .3          121    1.3         17,559        6,221      9.1
D      1,344    .4          161    1.2         16,045        5,381      7.8
D      1,680    .5          201    1.1         15,126        4,871      7.1
D      2,016    .6          242    1.1         14,547        4,550      6.6
D      2,352    .7          282    1.0         14,278        4,400      6.4
D      2,688    .8          322    1.0         14,028        4,262      6.2
D      3,024    .9          363    1.0         13,792        4,131      6.0
D      3,360   1.0          403    1.0         13,613        4,031      5.9
D      3,376   1.0          405    1.0         13,605        4,027      5.9
D      3,696   1.1          443    1.0         13,461        3,947      5.8
...
++++-分析-++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
***dba_hist_db_cache_advice中没有estd*列,删除;bcsf列有简化
SELECT REPLACE(block_size / 1024 || 'k',
               &bs / 1024 || 'k',
               substr(NAME, 1, 1)) bpool,
       decode(block_size, &bs, 1, 2) order_def_bs,
       size_for_estimate sfe,
       size_factor bcsf,
       buffers_for_estimate / 1000 bfe
--, estd_physical_read_factor     eprf
--, estd_physical_reads/1000      epr
--, estd_physical_read_time       eprt
--, estd_pct_of_db_time_for_reads epdbt
  FROM dba_hist_db_cache_advice
 WHERE snap_id = &eid
   AND dbid = &dbid
   AND instance_number = &inst_num
-- and estd_physical_reads > 0
 ORDER BY order_def_bs, block_size, NAME, buffers_for_estimate;
其中:
bs-->db_block_size
  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 = 'db_block_size';
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++


Buffer wait Statistics  DB/Inst: ORADB/oraDB1  Snaps: 66-67
-> ordered by wait time desc, waits desc
Class                                              Waits Total Wait Time (s)
-------------------------------------------- ----------- -------------------
Avg Time (ms)
-------------
data block                                        62,192              11,122
          179
undo block                                        75,508                 102
            1
1st level bmb                                          4                   0
            0
undo header                                            1                   0
            0
++++-分析-++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
select e.class
     , e.wait_count  - nvl(b.wait_count,0)       icnt
     , (e.time        - nvl(b.time,0))/100       itim
     ,10*  (e.time       - nvl(b.time,0))
         / (e.wait_count - nvl(b.wait_count,0))  iavg 
  from DBA_HIST_WAITSTAT b
     , DBA_HIST_WAITSTAT e
 where b.snap_id         = &bid
   and e.snap_id         = &eid
   and b.dbid            = &dbid
   and e.dbid            = &dbid
   and b.dbid            = e.dbid
   and b.instance_number = &inst_num
   and e.instance_number = &inst_num
   and b.instance_number = e.instance_number
   and b.class           = e.class
   and b.wait_count      < e.wait_count
 order by itim desc, icnt desc;
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++


PGA Aggr Target Stats  DB/Inst: ORADB/oraDB1  Snaps: 66-67
-> B: Begin snap   E: End snap (rows identified with B or E contain data
   which is absolute i.e. not diffed over the interval)
-> PGA cache hit % - percentage of W/A (WorkArea) data processed only in-memory
-> Auto PGA Target - actual workarea memory target
-> W/A PGA Used    - amount of memory used for all Workareas (manual + auto)
-> %PGA W/A Mem    - percentage of PGA memory allocated to workareas
-> %Auto W/A Mem   - percentage of workarea memory controlled by Auto Mem Mgmt
-> %Man W/A Mem    - percentage of workarea memory under manual control
PGA Cache Hit % W/A MB Processed Extra W/A MB Read/Written
--------------- ---------------- -------------------------
          100.0           22,582                         0
                                             %PGA  %Auto   %Man
  PGA Aggr  Auto PGA   PGA Mem    W/A PGA    W/A    W/A    W/A   Global Mem
  Target(M) Target(M)  Alloc(M)   Used(M)    Mem    Mem    Mem    Bound(K)
- --------- --------- ---------- ---------- ------ ------ ------ ----------
B     4,096     3,380      694.5        0.6     .1  100.0     .0    419,430
E     4,096     3,372      697.6        0.0     .0     .0     .0    419,430
++++-分析-++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
--PGA Cache Hit %:
SELECT 100 * (e.bytes - nvl(b.bytes, 0)) /
       (e.bytes - nvl(b.bytes, 0) + e.bytesrw - nvl(b.bytesrw, 0)) calc_cache_pct,
       (e.bytes - nvl(b.bytes, 0)) / 1024 / 1024 tbp,
       (e.bytesrw - nvl(b.bytesrw, 0)) / 1024 / 1024 tbrw
  FROM (SELECT SUM(CASE
                     WHEN NAME = 'bytes processed' THEN
                      VALUE
                     ELSE
                      0
                   END) bytes,
               SUM(CASE
                     WHEN NAME = 'extra bytes read/written' THEN
                      VALUE
                     ELSE
                      0
                   END) bytesrw
          FROM dba_hist_pgastat e1
         WHERE e1.snap_id = &eid
           AND e1.dbid = &dbid
           AND e1.instance_number = &inst_num
           AND e1.NAME IN ('bytes processed', 'extra bytes read/written')) e,
       (SELECT SUM(CASE
                     WHEN NAME = 'bytes processed' THEN
                      VALUE
                     ELSE
                      0
                   END) bytes,
               SUM(CASE
                     WHEN NAME = 'extra bytes read/written' THEN
                      VALUE
                     ELSE
                      0
                   END) bytesrw
          FROM dba_hist_pgastat b1
         WHERE b1.snap_id = &bid
           AND b1.dbid = &dbid
           AND b1.instance_number = &inst_num
           AND b1.NAME IN ('bytes processed', 'extra bytes read/written')) b
 WHERE e.bytes - nvl(b.bytes, 0) > 0;

--Warning:pga_aggregate_target设置太小,导致'over allocation count'增加
select 'Warning:  pga_aggregate_target was set too low for current workload, as this' nl
     , '          value was exceeded during this interval.  Use the PGA Advisory view'    nl
     , '          to help identify a different value for pga_aggregate_target.'                nl
  from DBA_HIST_PGASTAT   e
     , DBA_HIST_PGASTAT   b
     , DBA_HIST_PARAMETER p
 where e.snap_id             = &eid
   and e.dbid                = &dbid
   and e.instance_number     = &inst_num
   and e.name                = 'over allocation count'
   and b.snap_id(+)          = &bid
   and b.dbid(+)             = e.dbid
   and b.instance_number(+)  = e.instance_number
   and b.name(+)             = e.name
   and e.value > nvl(b.value,0)
   and p.snap_id             = &eid
   and p.dbid                = &dbid
   and p.instance_number     = &inst_num
   and p.parameter_name      = 'workarea_size_policy'
   and p.value               = 'AUTO';

SELECT 'B' snap,
       to_number(p.VALUE) / 1024 / 1024 pgaat,
       mu.pat / 1024 / 1024 pat,
       mu.pga_alloc / 1024 / 1024 tot_pga_allo,
       (mu.pga_used_auto + mu.pga_used_man) / 1024 / 1024 tot_tun_used,
       100 * (mu.pga_used_auto + mu.pga_used_man) / pga_alloc pct_tun,
       decode(mu.pga_used_auto + mu.pga_used_man,
              0,
              0,
              100 * mu.pga_used_auto / (mu.pga_used_auto + mu.pga_used_man)) pct_auto_tun,
       decode(mu.pga_used_auto + mu.pga_used_man,
              0,
              0,
              100 * mu.pga_used_man / (mu.pga_used_auto + mu.pga_used_man)) pct_man_tun,
       mu.glob_mem_bnd / 1024 glo_mem_bnd
  FROM (SELECT SUM(CASE
                     WHEN NAME = 'total PGA allocated' THEN
                      VALUE
                     ELSE
                      0
                   END) pga_alloc,
               SUM(CASE
                     WHEN NAME = 'total PGA used for auto workareas' THEN
                      VALUE
                     ELSE
                      0
                   END) pga_used_auto,
               SUM(CASE
                     WHEN NAME = 'total PGA used for manual workareas' THEN
                      VALUE
                     ELSE
                      0
                   END) pga_used_man,
               SUM(CASE
                     WHEN NAME = 'global memory bound' THEN
                      VALUE
                     ELSE
                      0
                   END) glob_mem_bnd,
               SUM(CASE
                     WHEN NAME = 'aggregate PGA auto target' THEN
                      VALUE
                     ELSE
                      0
                   END) pat
          FROM dba_hist_pgastat pga
         WHERE pga.snap_id = &bid
           AND pga.dbid = &dbid
           AND pga.instance_number = &inst_num) mu,
       dba_hist_parameter p
 WHERE p.snap_id = &bid
   AND p.dbid = &dbid
   AND p.instance_number = &inst_num
   AND p.parameter_name = 'pga_aggregate_target'
   AND p.VALUE != '0'
UNION ALL
SELECT 'E' snap,
       to_number(p.VALUE) / 1024 / 1024 pgaat,
       mu.pat / 1024 / 1024 pat,
       mu.pga_alloc / 1024 / 1024 tot_pga_allo,
       (mu.pga_used_auto + mu.pga_used_man) / 1024 / 1024 tot_tun_used,
       100 * (mu.pga_used_auto + mu.pga_used_man) / pga_alloc pct_tun,
       decode(mu.pga_used_auto + mu.pga_used_man,
              0,
              0,
              100 * mu.pga_used_auto / (mu.pga_used_auto + mu.pga_used_man)) pct_auto_tun,
       decode(mu.pga_used_auto + mu.pga_used_man,
              0,
              0,
              100 * mu.pga_used_man / (mu.pga_used_auto + mu.pga_used_man)) pct_man_tun,
       mu.glob_mem_bnd / 1024 glo_mem_bnd
  FROM (SELECT SUM(CASE
                     WHEN NAME = 'total PGA allocated' THEN
                      VALUE
                     ELSE
                      0
                   END) pga_alloc,
               SUM(CASE
                     WHEN NAME = 'total PGA used for auto workareas' THEN
                      VALUE
                     ELSE
                      0
                   END) pga_used_auto,
               SUM(CASE
                     WHEN NAME = 'total PGA used for manual workareas' THEN
                      VALUE
                     ELSE
                      0
                   END) pga_used_man,
               SUM(CASE
                     WHEN NAME = 'global memory bound' THEN
                      VALUE
                     ELSE
                      0
                   END) glob_mem_bnd,
               SUM(CASE
                     WHEN NAME = 'aggregate PGA auto target' THEN
                      VALUE
                     ELSE
                      0
                   END) pat
          FROM dba_hist_pgastat pga
         WHERE pga.snap_id = &eid
           AND pga.dbid = &dbid
           AND pga.instance_number = &inst_num) mu,
       dba_hist_parameter p
 WHERE p.snap_id = &eid
   AND p.dbid = &dbid
   AND p.instance_number = &inst_num
   AND p.parameter_name = 'pga_aggregate_target'
   AND p.VALUE != '0'
 ORDER BY snap;
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++


PGA Aggr Target Histogram  DB/Inst: ORADB/oraDB1  Snaps: 66-67
-> Optimal Executions are purely in-memory operations
    Low    High
Optimal Optimal    Total Execs Optimal Execs 1-Pass Execs M-Pass Execs
------- ------- -------------- ------------- ------------ ------------
     2K      4K         33,739        33,739            0            0
    64K    128K             15            15            0            0
   128K    256K              6             6            0            0
   256K    512K              6             6            0            0
   512K   1024K          2,599         2,599            0            0
     1M      2M          1,219         1,219            0            0
     2M      4M            355           355            0            0
     4M      8M            178           178            0            0
     8M     16M            191           191            0            0
    16M     32M            476           476            0            0
    32M     64M             45            45            0            0
    64M    128M             18            18            0            0
++++-分析-++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
SELECT CASE
         WHEN e.low_optimal_size >= 1024 * 1024 * 1024 * 1024 THEN
          lpad(round(e.low_optimal_size / 1024 / 1024 / 1024 / 1024) || 'T',
               7)
         WHEN e.low_optimal_size >= 1024 * 1024 * 1024 THEN
          lpad(round(e.low_optimal_size / 1024 / 1024 / 1024) || 'G', 7)
         WHEN e.low_optimal_size >= 1024 * 1024 THEN
          lpad(round(e.low_optimal_size / 1024 / 1024) || 'M', 7)
         WHEN e.low_optimal_size >= 1024 THEN
          lpad(round(e.low_optimal_size / 1024) || 'K', 7)
         ELSE
          lpad(e.low_optimal_size || 'B', 7)
       END low_o,
       CASE
         WHEN e.high_optimal_size >= 1024 * 1024 * 1024 * 1024 THEN
          lpad(round(e.high_optimal_size / 1024 / 1024 / 1024 / 1024) || 'T',
               7)
         WHEN e.high_optimal_size >= 1024 * 1024 * 1024 THEN
          lpad(round(e.high_optimal_size / 1024 / 1024 / 1024) || 'G', 7)
         WHEN e.high_optimal_size >= 1024 * 1024 THEN
          lpad(round(e.high_optimal_size / 1024 / 1024) || 'M', 7)
         WHEN e.high_optimal_size >= 1024 THEN
          lpad(round(e.high_optimal_size / 1024) || 'K', 7)
         ELSE
          e.high_optimal_size || 'B'
       END high_o,
       e.total_executions - nvl(b.total_executions, 0) tot_e,
       e.optimal_executions - nvl(b.optimal_executions, 0) opt_e,
       e.onepass_executions - nvl(b.onepass_executions, 0) one_e,
       e.multipasses_executions - nvl(b.multipasses_executions, 0) mul_e
  FROM dba_hist_sql_workarea_hstgrm e, dba_hist_sql_workarea_hstgrm b
 WHERE e.snap_id = &eid
   AND e.dbid = &dbid
   AND e.instance_number = &inst_num
   AND b.snap_id(+) = &bid
   AND b.dbid(+) = e.dbid
   AND b.instance_number(+) = e.instance_number
   AND b.low_optimal_size(+) = e.low_optimal_size
   AND b.high_optimal_size(+) = e.high_optimal_size
   AND e.total_executions - nvl(b.total_executions, 0) > 0
 ORDER BY e.low_optimal_size;
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++


PGA Memory Advisory  DB/Inst: ORADB/oraDB1  End Snap: 67
-> When using Auto Memory Mgmt, minimally choose a pga_aggregate_target value
   where Estd PGA Overalloc Count is 0
                                       Estd Extra    Estd PGA   Estd PGA
PGA Target    Size           W/A MB   W/A MB Read/      Cache  Overalloc
  Est (MB)   Factr        Processed Written to Disk     Hit %      Count
---------- ------- ---------------- ---------------- -------- ----------
       512     0.1         95,647.2          1,640.1     98.0          0
     1,024     0.3         95,647.2          1,640.1     98.0          0
     2,048     0.5         95,647.2          1,640.1     98.0          0
     3,072     0.8         95,647.2          1,640.1     98.0          0
     4,096     1.0         95,647.2              0.0    100.0          0
     4,915     1.2         95,647.2              0.0    100.0          0
...
++++-分析-++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
select pga_target_for_estimate/1024/1024  pga_t
     , pga_target_factor                  pga_tf
     , bytes_processed/1024/1024          byt_p
     , estd_extra_bytes_rw/1024/1024      byt_rw
     , estd_pga_cache_hit_percentage      epchp
     , estd_overalloc_count               eoc
  from DBA_HIST_PGA_TARGET_ADVICE e
 where snap_id             = &eid
   and dbid                = &dbid
   and instance_number     = &inst_num
 order by pga_target_for_estimate;
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

 


Process Memory Summary Stats  DB/Inst: ORADB/oraDB1  Snaps: 66-67
-> B: Begin snap   E: End snap
-> All rows below contain absolute values (i.e. not diffed over the interval)
-> Max Alloc is Maximum PGA Allocation size at snapshot time
   Hist Max Alloc is the Historical Max Allocation for still-connected processes
-> Num Procs or Allocs:  For Begin/End snapshot lines, it is the number of
   processes. For Category lines, it is the number of allocations
-> ordered by Begin/End snapshot, Alloc (MB) desc
                                                                   Hist   Num
                                           Avg    Std Dev   Max    Max   Procs
              Alloc     Used    Freeabl   Alloc    Alloc   Alloc  Alloc    or
   Category   (MB)      (MB)      (MB)     (MB)    (MB)    (MB)    (MB)  Allocs
-- -------- --------- --------- -------- -------- ------- ------- ------ ------
B  --------     694.7     341.3    161.2      2.8     4.4      22    159    250
   Other        522.5                         2.1     4.5      22     22    250
   Freeable     161.3        .0               1.0      .2       2           169
   SQL            6.1       2.8                .0      .0       0    154    177
   PL/SQL         4.8       3.9                .0      .0       0      0    249
E  --------     698.0     349.9    136.8      3.1     5.2      38    159    225
   Other        550.1                         2.4     5.3      37     37    225
   Freeable     136.6        .0                .9      .3       2           147
   SQL            7.0       3.2                .0      .1       0    154    155
   PL/SQL         4.4       3.4                .0      .0       0      0    224
++++-分析-++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
***本部分AWR中只有DBA_HIST_PROCESS_MEM_SUMMARY,与stats$process_memory_rollup类似,缺少stats$process_rollup,在AWR中无法实现。
但该部分代码实现使用了很多技巧,值得借鉴:
在snap中获取数据部分:
     insert into stats$process_rollup
     select *
       from (select *
               from (select l_snap_id
                          , p_dbid
                          , p_instance_number
                          , nvl(pid, -9)          pid
                          , nvl(serial#, -9)      serial#
                          , decode(pid, null, null, max(spid))           spid
                          , decode(pid, null, null, max(program))        program
                          , decode(pid, null, null, max(background))     background
                          , sum(pga_used_mem)     pga_used_mem
                          , sum(pga_alloc_mem)    pga_alloc_mem
                          , sum(pga_freeable_mem) pga_freeable_mem
                          , max(pga_alloc_mem)    max_pga_alloc_mem
                          , max(pga_max_mem)      max_pga_max_mem
                          , decode(pid, null, avg(pga_alloc_mem), null)    avg_pga_alloc_mem
                          , decode(pid, null, stddev(pga_alloc_mem), null) stddev_pga_alloc_mem
                          , decode(pid, null, count(pid), null)            num_processes
                       from v$process
                      where program != 'PSEUDO'
                      group by grouping sets ( (pid, serial#), () )
                    )
              where pid = -9
                 or pga_alloc_mem >= (p_def_proc_mem_th *1024*1024)
              order by pga_alloc_mem desc
            )
      where rownum <= (p_def_num_procs + 1);
      -->***通过使用group by grouping sets,同时获取了单个与汇总信息,汇总信息pid=-9.

      insert into stats$process_memory_rollup
      select *
       from (select l_snap_id                 snap_id  -- required for 3824971
                  , p_dbid                    dbid
                  , p_instance_number         instance_number
                  , nvl(pm.pid, -9)           pid
                  , nvl(pm.serial#, -9)       serial#
                  , pm.category
                  , sum(pm.allocated)         allocated
                  , sum(pm.used)              used
                  , max(pm.allocated)         max_allocated
                  , max(pm.max_allocated)     max_max_allocated
                  , decode(pid, null, avg(pm.allocated), null)    avg_allocated
                  , decode(pid, null, stddev(pm.allocated), null) stddev_allocated
                  , decode(pid, null ,sum(decode(allocated, 0, 0, 1)), null) non_zero_allocations
               from v$process_memory pm
              group by grouping sets (  (pm.pid, pm.serial#, pm.category)
                                      , (pm.category) )
            ) g
      where  g.pid = -9                                       -- category summary rows
         or (g.pid, g.serial#) in (select pr.pid, pr.serial#  -- detail rows
                                     from stats$process_rollup pr
                                    where pr.snap_id         = l_snap_id
                                      and pr.dbid            = p_dbid
                                      and pr.instance_number = p_instance_number
                                  );
      -->***通过使用group by grouping sets,同时获取了单个与分类汇总信息,分类汇总信息pid=-9.

select *
  from (select decode(snap_id, :bid, 'B', :eid, 'E')  b_or_e
             , 1                                      ord_col
             , snap_id                                snid
             , '---------'                            cat
             , pga_alloc_mem/&&btomb                  tot_alloc_mb
             , pga_used_mem/&&btomb                   tot_used_mb
             , pga_freeable_mem/&&btomb               tot_free_pga_mb
             , avg_pga_alloc_mem/&&btomb              avg_alloc_mb
             , stddev_pga_alloc_mem/&&btomb           stddev_alloc_mb
             , max_pga_alloc_mem/&&btomb              max_alloc_mb
             , max_pga_max_mem/&&btomb                max_max_alloc_mb
             , num_processes                          nza
          from stats$process_rollup
         where snap_id           in (:bid, :eid)
           and dbid               = :dbid
           and instance_number    = :inst_num
           and pid                = -9
        union all
        select decode(snap_id, :bid, 'B', :eid, 'E')  b_or_e
             , 2                                      ord_col
             , snap_id                                snid
             , category                               cat
             , allocated/&&btomb                      tot_alloc_mb
             , used/&&btomb                           tot_used_mb
             , to_number(null)                        tot_free_pga_mb
             , avg_allocated /&&btomb                 avg_alloc_mb
             , stddev_allocated/&&btomb               stddev_alloc_mb
             , max_allocated/&&btomb                  max_alloc_mb
             , max_max_allocated/&&btomb              max_max_alloc_mb
             , non_zero_allocations                   nza
          from stats$process_memory_rollup
         where snap_id            in (:bid, :eid)
           and dbid                = :dbid
           and instance_number     = :inst_num
           and pid                 = -9
       )
 order by snid, ord_col, tot_alloc_mb desc;
-->***只取pid=-9的汇总信息,分别为所有进程汇总与分类汇总

--Top Process Memory (by component):
select *
  from (select decode(snap_id, :bid, 'B', :eid, 'E') b_or_e
             , snap_id                      snid
             , 1                            ord_col
             , pid                          pid
             , rpad(substr( program, instrb(program,'(') +1
                          , instrb(program, ')')-1-instrb(program,'(')) || ' '
                   , 13, '-')               cat
             , pga_alloc_mem/&&btomb        tot_alloc_mb
             , pga_used_mem/&&btomb         tot_used_mb
             , pga_freeable_mem/&&btomb     tot_free_pga_mb
             , max_pga_alloc_mem/&&btomb    max_alloc_mb
             , max_pga_max_mem/&&btomb      max_max_alloc_mb
             , pga_alloc_mem/&&btomb        tot_alloc_mb2
          from stats$process_rollup
         where snap_id             in (:bid, :eid)
           and dbid                = :dbid
           and instance_number     = :inst_num
           and pid                != -9
       union all
       select decode(pmr.snap_id, :bid, 'B', :eid, 'E') b_or_e
            , pmr.snap_id                    snid
            , 2                              ord_col
            , pmr.pid                        pid
            , pmr.category                   cat
            , pmr.allocated/&&btomb          tot_alloc_mb
            , pmr.used/&&btomb               tot_used_mb
            , to_number(null)                pga_free_mb
            , pmr.max_allocated/&&btomb      max_alloc_mb
            , pmr.max_max_allocated/&&btomb  max_max_alloc_mb
            , pr.pga_alloc_mem/&&btomb       tot_alloc_mb2
         from stats$process_memory_rollup pmr
            , stats$process_rollup        pr
        where pmr.snap_id             in (:bid, :eid)
          and pmr.dbid                = :dbid
          and pmr.instance_number     = :inst_num
          and pmr.pid                != -9
          and pr.snap_id = pmr.snap_id
          and pr.dbid    = pmr.dbid
          and pr.instance_number = pmr.instance_number
          and pr.pid             = pmr.pid
          and pmr.serial#        = pmr.serial#
       )
 order by snid, tot_alloc_mb2 desc, pid, ord_col,tot_alloc_mb desc;
-->***获取单个进程信息(pid!= -9),第一部分获取进程汇总信息,第二部分获取进程分类汇总信息
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++


Top Process Memory (by component)  DB/Inst: ORADB/oraDB1  Snaps: 66-67
-> ordered by Begin/End snapshot, Alloc (MB) desc

                         Alloc   Used   Freeabl     Max      Hist Max
      PId Category       (MB)    (MB)     (MB)   Alloc (MB) Alloc (MB)
-- ------ ------------- ------- ------- -------- ---------- ----------
B      20 LGWR --------    22.0    10.4       .0       22.0       22.0
          Other            22.0                        22.0       22.0
          PL/SQL             .0      .0                  .0         .0
       30 ARC1 --------    21.9    10.4       .0       21.9       21.9
          Other            21.9                        21.9       21.9
          PL/SQL             .0      .0                  .0         .0
E     159  ------------    38.2    17.7       .7       38.2       38.2
          Other            37.4                        37.4       37.4
          Freeable           .7      .0                  .7
          PL/SQL             .1      .1                  .1         .1
          SQL                .0      .0                  .0         .3
++++-分析-++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
-->参考Process Memory Summary Stats部分说明
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++


Enqueue activity  DB/Inst: ORADB/oraDB1  Snaps: 66-67
-> only enqueues with waits are shown
-> Enqueue stats gathered prior to 10g should not be compared with 10g data
-> ordered by Wait Time desc, Waits desc
Enqueue Type (Request Reason)
------------------------------------------------------------------------------
    Requests    Succ Gets Failed Gets       Waits Wt Time (s)  Av Wt Time(ms)
------------ ------------ ----------- ----------- ------------ --------------
WL-Being Written Redo Log
           3            2           1           2            3       1,634.50
US-Undo Segment
       3,080        3,080           0       2,941            1            .37
TM-DML
     159,593      159,591           0         659            0            .50
TX-Transaction (index contention)
         161          161           0          69            0           1.84
...
++++-分析-++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
SELECT /*+ ordered */
 e.eq_type || '-' || to_char(nvl(l.NAME, ' ')) ||
 decode(upper(e.req_reason),
        'CONTENTION',
        NULL,
        '-',
        NULL,
        ' (' || e.req_reason || ')') ety,
 e.total_req# - nvl(b.total_req#, 0) reqs,
 e.succ_req# - nvl(b.succ_req#, 0) sreq,
 e.failed_req# - nvl(b.failed_req#, 0) freq,
 e.total_wait# - nvl(b.total_wait#, 0) waits,
 (e.cum_wait_time - nvl(b.cum_wait_time, 0)) / 1000 wttm,
 decode((e.total_wait# - nvl(b.total_wait#, 0)),
        0,
        to_number(NULL),
        ((e.cum_wait_time - nvl(b.cum_wait_time, 0)) /
        (e.total_wait# - nvl(b.total_wait#, 0)))) awttm
  FROM dba_hist_enqueue_stat e, dba_hist_enqueue_stat b, v$lock_type l
 WHERE b.snap_id(+) = &bid
   AND e.snap_id = &eid
   AND b.dbid(+) = &dbid
   AND e.dbid = &dbid
   AND b.dbid(+) = e.dbid
   AND b.instance_number(+) = &inst_num
   AND e.instance_number = &inst_num
   AND b.instance_number(+) = e.instance_number
   AND b.eq_type(+) = e.eq_type
   AND b.req_reason(+) = e.req_reason
   AND e.total_wait# - nvl(b.total_wait#, 0) > 0
   AND l.TYPE(+) = e.eq_type
 ORDER BY wttm DESC, waits DESC;
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++


Undo Segment Summary  DB/Inst: ORADB/oraDB1  Snaps: 66-67
-> Min/Max TR (mins) - Min and Max Tuned Retention (minutes)
-> STO - Snapshot Too Old count,  OOS - Out Of Space count
-> Undo segment block stats:
   uS - unexpired Stolen,   uR - unexpired Released,   uU - unexpired reUsed
   eS - expired   Stolen,   eR - expired   Released,   eU - expired   reUsed

Undo   Num Undo       Number of  Max Qry     Max Tx Min/Max   STO/  uS/uR/uU/
 TS# Blocks (K)    Transactions  Len (s)      Concy TR (mins) OOS   eS/eR/eU
---- ---------- --------------- -------- ---------- --------- ----- -----------
   1        5.9          98,588    3,137         10 5760/5760 0/0   0/0/0/0/0/0
++++-分析-++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
SELECT undotsn,
       SUM(undoblks) / 1000 undob,
       SUM(txncount) txcnt,
       MAX(maxquerylen) maxq,
       MAX(maxconcurrency) maxc,
       round(MIN(tuned_undoretention) / 60, 1) || '/' ||
       round(MAX(tuned_undoretention) / 60, 1) mintun,
       SUM(ssolderrcnt) || '/' || SUM(nospaceerrcnt) snolno,
       SUM(unxpstealcnt) || '/' || SUM(unxpblkrelcnt) || '/' ||
       SUM(unxpblkreucnt) || '/' || SUM(expstealcnt) || '/' ||
       SUM(expblkrelcnt) || '/' || SUM(expblkreucnt) blkst
  FROM dba_hist_undostat
 WHERE dbid = &dbid
   AND instance_number = &inst_num
   AND snap_id >= &bid
   AND snap_id <= &eid
 GROUP BY undotsn;
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

 

Undo Segment Stats  DB/Inst: ORADB/oraDB1  Snaps: 66-67
-> Most recent 35 Undostat rows, ordered by End Time desc
                Num Undo    Number of Max Qry  Max Tx Tun Ret STO/  uS/uR/uU/
End Time          Blocks Transactions Len (s)   Concy  (mins) OOS   eS/eR/eU
------------ ----------- ------------ ------- ------- ------- ----- -----------
15-Dec 10:59       1,205       20,436   3,137       8   5,760 0/0   0/0/0/0/0/0
15-Dec 10:49       1,156       19,366   2,537       9   5,760 0/0   0/0/0/0/0/0
15-Dec 10:39       1,128       19,511   1,937       6   5,760 0/0   0/0/0/0/0/0
15-Dec 10:29       1,133       20,138   1,624      10   5,760 0/0   0/0/0/0/0/0
15-Dec 10:19       1,236       19,137   1,023       7   5,760 0/0   0/0/0/0/0/0
++++-分析-++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
SELECT undotsn, endt, undob, txcnt, maxq, maxc, mintun, snolno, blkst
  FROM (SELECT undotsn,
               to_char(end_time, 'DD-Mon HH24:MI') endt,
               undoblks undob,
               txncount txcnt,
               maxquerylen maxq,
               maxconcurrency maxc,
               tuned_undoretention / 60 mintun,
               ssolderrcnt || '/' || nospaceerrcnt snolno,
               unxpstealcnt || '/' || unxpblkrelcnt || '/' || unxpblkreucnt || '/' ||
               expstealcnt || '/' || expblkrelcnt || '/' || expblkreucnt blkst
          FROM dba_hist_undostat
         WHERE dbid = &dbid
           AND instance_number = &inst_num
           AND snap_id >= &bid
           AND snap_id <= &eid
         ORDER BY begin_time DESC)
 WHERE rownum < 10;
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++


Latch Activity  DB/Inst: ORADB/oraDB1  Snaps: 66-67
->"Get Requests", "Pct Get Miss" and "Avg Slps/Miss" are statistics for
  willing-to-wait latch get requests
->"NoWait Requests", "Pct NoWait Miss" are for no-wait latch get requests
->"Pct Misses" for both should be very close to 0.0
                                           Pct    Avg   Wait                 Pct
                              Get          Get   Slps   Time       NoWait NoWait
Latch                       Requests      Miss  /Miss    (s)     Requests   Miss
------------------------ -------------- ------ ------ ------ ------------ ------
ASM db client latch               2,042    0.0             0            0
Consistent RBA                   98,283    0.0    0.0      0            0
FAL request queue                    64    0.0             0            0
FAL subheap alocation                64    0.0             0            0
FIB s.o chain latch                  96    0.0             0            0
FOB s.o list latch                  986    0.0             0            0
...
++++-分析-++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
SELECT b.latch_name latch_name,
       e.gets - b.gets gets,
       to_number(decode(e.gets,
                        b.gets,
                        NULL,
                        (e.misses - b.misses) * 100 / (e.gets - b.gets))) missed,
       to_number(decode(e.misses,
                        b.misses,
                        NULL,
                        (e.sleeps - b.sleeps) / (e.misses - b.misses))) sleeps,
       (e.wait_time - b.wait_time) / 1000000 wt,
       e.immediate_gets - b.immediate_gets nowai,
       to_number(decode(e.immediate_gets,
                        b.immediate_gets,
                        NULL,
                        (e.immediate_misses - b.immediate_misses) * 100 /
                        (e.immediate_gets - b.immediate_gets))) imiss
  FROM dba_hist_latch b, dba_hist_latch e
 WHERE b.snap_id = &bid
   AND e.snap_id = &eid
   AND b.dbid = &dbid
   AND e.dbid = &dbid
   AND b.dbid = e.dbid
   AND b.instance_number = &inst_num
   AND e.instance_number = &inst_num
   AND b.instance_number = e.instance_number
   AND b.latch_name = e.latch_name
   AND (e.gets - b.gets + e.immediate_gets - b.immediate_gets) > 0
 ORDER BY b.latch_name;
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++


Latch Sleep breakdown  DB/Inst: ORADB/oraDB1  Snaps: 66-67
-> ordered by misses desc
                                       Get                                 Spin
Latch Name                        Requests       Misses      Sleeps        Gets
-------------------------- --------------- ------------ ----------- -----------
row cache objects                2,851,791    2,001,054       2,985   1,998,090
cache buffers chains           246,877,920      526,867      34,254     494,907
undo global data                 2,445,483       24,812          53      24,762
simulator lru latch              7,212,042       14,717           4      14,713
KCL gc element parent latc      16,016,719        2,938          12       2,929
cache buffers lru chain            499,763          667           3         664
...
++++-分析-++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
select b.latch_name                                      name
     , e.gets        - b.gets                      gets
     , e.misses      - b.misses                    misses
     , e.sleeps      - b.sleeps                    sleeps
     , e.spin_gets   - b.spin_gets                 spin_gets
  from dba_hist_latch b
     , dba_hist_latch e
 where b.snap_id         = &bid
   and e.snap_id         = &eid
   and b.dbid            = &dbid
   and e.dbid            = &dbid
   and b.dbid            = e.dbid
   and b.instance_number = &inst_num
   and e.instance_number = &inst_num
   and b.instance_number = e.instance_number
   and b.latch_name      = e.latch_name
   and e.sleeps - b.sleeps > 0
 order by misses desc;
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++


Latch Miss Sources  DB/Inst: ORADB/oraDB1  Snaps: 66-67
-> only latches with sleeps are shown
-> ordered by name, sleeps desc
                                                     NoWait              Waiter
Latch Name               Where                       Misses     Sleeps   Sleeps
------------------------ -------------------------- ------- ---------- --------
KCL gc element parent la kclplz                           0          5        2
KCL gc element parent la kclbla                           0          4        0
KCL gc element parent la kclzcl                           0          4        0
KCL gc element parent la kclnfndnewm                      0          4        7
cache buffers chains     kcbzwb                           0     17,450   24,067
cache buffers chains     kcbchg: kslbegin: bufs not       0     16,883   14,443
cache buffers chains     kcbzib: finish free bufs         0      8,633      103
...
++++-分析-++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
select e.parent_name                              parent
     , e.where_in_code                            where_from
     , e.nwfail_count  - nvl(b.nwfail_count,0)    nwmisses
     , e.sleep_count   - nvl(b.sleep_count,0)     sleeps
     , e.wtr_slp_count - nvl(b.wtr_slp_count,0)   waiter_sleeps
  from DBA_HIST_LATCH_MISSES_SUMMARY b
     , DBA_HIST_LATCH_MISSES_SUMMARY e
 where b.snap_id(+)         = &bid
   and e.snap_id            = &eid
   and b.dbid(+)            = &dbid
   and e.dbid               = &dbid
   and b.dbid(+)            = e.dbid
   and b.instance_number(+) = &inst_num
   and e.instance_number    = &inst_num
   and b.instance_number(+) = e.instance_number
   and b.parent_name(+)     = e.parent_name
   and b.where_in_code(+)   = e.where_in_code
   and e.sleep_count        > nvl(b.sleep_count,0)
 order by e.parent_name, sleeps desc;

--Parent Latch Statistics
--only latches with sleeps are shown
--ordered by name
SELECT l.latch_name PARENT, lp.gets, lp.misses, lp.sleeps, lp.spin_gets
  FROM (SELECT e.instance_number,
               e.dbid,
               e.snap_id,
               e.latch_name,
               e.latch_hash,
               e.gets - b.gets gets,
               e.misses - b.misses misses,
               e.sleeps - b.sleeps sleeps,
               e.spin_gets - b.spin_gets spin_gets
          FROM dba_hist_latch_parent b, dba_hist_latch_parent e
         WHERE b.snap_id = &bid
           AND e.snap_id = &eid
           AND b.dbid = &dbid
           AND e.dbid = &dbid
           AND b.dbid = e.dbid
           AND b.instance_number = &inst_num
           AND e.instance_number = &inst_num
           AND b.instance_number = e.instance_number
           AND b.latch_name = e.latch_name
           AND b.latch_hash = e.latch_hash
           AND e.sleeps - b.sleeps > 0) lp,
       dba_hist_latch l
 WHERE l.snap_id = lp.snap_id
   AND l.dbid = lp.dbid
   AND l.instance_number = lp.instance_number
   AND l.latch_name = lp.latch_name
   AND l.latch_hash = lp.latch_hash
 ORDER BY l.latch_name;

--Latch Children
--only latches with sleeps/gets > 1/100000 are shown
--ordered by name, gets desc
SELECT l.latch_name, lc.child, lc.gets, lc.misses, lc.sleeps, lc.spin_gets
  FROM (SELECT /*+ ordered use_hash(b) */
         e.instance_number,
         e.dbid,
         e.snap_id,
         e.latch_name,
         e.latch_hash,
         e.child# child,
         e.gets - b.gets gets,
         e.misses - b.misses misses,
         e.sleeps - b.sleeps sleeps,
         e.spin_gets - b.spin_gets spin_gets
          FROM dba_hist_latch_children e, dba_hist_latch_children b
         WHERE b.snap_id = &bid
           AND e.snap_id = &eid
           AND b.dbid = &dbid
           AND e.dbid = &dbid
           AND b.dbid = e.dbid
           AND b.instance_number = &inst_num
           AND e.instance_number = &inst_num
           AND b.instance_number = e.instance_number
           AND b.latch_name = e.latch_name
           AND b.latch_hash = e.latch_hash
           AND b.child# = e.child#
           AND e.sleeps - b.sleeps > 0
           AND (e.sleeps - b.sleeps) / (e.gets - b.gets) > .00001) lc,
       dba_hist_latch l
 WHERE l.snap_id = lc.snap_id
   AND l.dbid = lc.dbid
   AND l.instance_number = lc.instance_number
   AND l.latch_name = lc.latch_name
   AND l.latch_hash = lc.latch_hash
 ORDER BY latch_name, gets DESC;
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Mutex Sleep  DB/Inst: ORADB/oraDB1  Snaps: 66-67
-> ordered by Wait Time desc
                                                                         Wait
Mutex Type         Location                                 Sleeps     Time (s)
------------------ -------------------------------- -------------- ------------
Cursor Pin         kksfbc [KKSCHLFSP2]                      34,825        340.3
Cursor Pin         kkslce [KKSCHLPIN2]                      10,112         98.8
Cursor Pin         kksfbc [KKSCHLPIN1]                       1,024         10.0
Cursor Parent      kksfbc [KKSPRTLOC1]                          38          0.0
Cursor Pin         kksLockDelete [KKSCHLPIN6]                    2          0.0
++++-分析-++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
***由于不存在v$mutex_sleep、v$mutex_sleep_history相关DBA_HIST_*表,在AWR中无法实现
select e.mutex_type                                mux
     , e.location                                  loc
     , e.sleeps    - nvl(b.sleeps, 0)              sleeps
     , (e.wait_time - nvl(b.wait_time, 0))/&ustos  wt
  from stats$mutex_sleep b
     , stats$mutex_sleep e
 where b.snap_id(+)         = :bid
   and e.snap_id            = :eid
   and e.dbid               = :dbid
   and b.dbid(+)            = e.dbid
   and e.instance_number    = :inst_num
   and b.instance_number(+) = e.instance_number
   and b.mutex_type(+)      = e.mutex_type
   and b.location(+)        = e.location
   and e.sleeps - nvl(b.sleeps, 0) > 0
 order by e.wait_time - nvl(b.wait_time, 0) desc;
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

 

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

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

注册时间:2011-05-26

  • 博文量
    211
  • 访问量
    809915