硬货 | AWR报告指标背后的SQL逻辑（三）

数据与人 2020-12-15

2029

一、摘要：

编写出发点：

网络上大量关于分析AWR报表的资料，却没有提供相关的分析资料。导致很多新人只知其然不知所以然。

此文章目的帮助更加深入理解AWR报表，为Oracle从业者与技术爱好者做性能分析与性能监控提供参考资料。

编写依据：

基于以下两个包源码分析得出。

oracle dbms_swrf_report_internal,
test_dbms_workload_repository

这二个包的源码是通过wrap加密保存的，需要用解密工具才能获得源码。

声明：

1、此文档内容纯属个人学习总结，不对文档内容完全准确负责。

2、由于分析awr生成存储过程工作量极大，很多代码没有经过优化与完善只初步对生成内容与awr报表对比正确。

3、此文档的sql语句98%以上是根据源码解析而成，非Oracle源码sql

4、建议有兴趣的同学去学习一下oracle plsql编写技巧，能在plsql缩写技能与编程思想上有所提升。

5、Awr二个包的代码极其复杂，对学习者的plsql编写能力有要求。

6、对应的二个包还包括ADDM，ASH的源码，有兴趣的同学可以深入了解。

很多东西都不知道原理甚至使用都不清楚，分析此报表为了更深入理解oracle性能分析原理，期望有更多的IT人士能把知识共享出来。

希望我的学习经历能帮助别人缩短学习时间。

后续：

1、将视情况会把每个awr报表对应说明，根据自己的理解做相应描述。

2、过一段时间会把数据字典的一些分析与个人理解共享出来。

二、分析过程

Oracle公司的plsql编写能力叹为观止，时间进度近一个月，分析时间达150个小时以上。

Awr工作原理

1、调用awr脚本，awr脚本调用awrrpti

 select output from table(dbms_workload_repository.&fn_name( :dbid,:inst_num,:bid,:eid,:rpt_options ));

2、&fn_name会根据用户输入选择调用

AWR_REPORT_HTML或AWR_REPORT_TEXT

3、AWR_REPORT_HTML调用：

DBMS_SWRF_REPORT_INTERNAL.AWR_REPORT_MAIN(L_DBID,L_INST_NUM, L_BID, L_EID, L_OPTIONS,DBMS_SWRF_REPORT_INTERNAL.TRUE_I);

4、AWR_REPORT_MAIN核心操作说明：

此存储过程会把报表需要的数据生成到

prt_stats,rpt_params,prt_time_vals三个数据定义表中

 REPORT_INIT(L_DBID, L_INST_NUM, L_BID, L_EID, RPT_STATS, RPT_PARAMS,RPT_TIME_VALS);

生成awr Main Report 之前的报表

 REPORT_SUMMARY(L_DBID, L_INST_NUM,L_BID, L_EID, L_OPTIONS, TO_HTML, FALSE_I);

生成剩下的报表

 DISPLAY_SUBTREES_OF(MAIN_REPT, TO_HTML, L_OPTIONS, L_DBID, L_INST_NUM, L_BID, L_EID);

三、AWR报告关键指标一览

三、分析出来对应的SQL语句（语句排列顺序跟AWR报表显示顺序一致）

----Enqueue Activity

select /*+ ordered */
 substr(e.eq_type || ' - ' || to_char(nvl(l.name, ' ')) ||
        decode(upper(e.req_reason),
               ' CONTENTION ',
               null,
               ' - ',
               null,
               '(' || e.req_reason || ') '),
        1,
        78) 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 b, dba_hist_enqueue_stat e, v$lock_type l
 where b.snap_id(+) = &beg_snap
   and e.snap_id = &end_snap
   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, e.eq_type;

----Undo Segment Summary

select undotsn,
       sum(undoblks)  1000 undob,
       sum(txncount) txcnt,
       max(maxquerylen) maxq,
       max(maxconcurrency) maxc,
       min(tuned_undoretention)  60 || '/' ||
       max(tuned_undoretention)  60 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 end_time > (SELECT END_INTERVAL_TIME
                     FROM DBA_HIST_SNAPSHOT
                    WHERE SNAP_ID = &beg_snap
                      AND DBID = &DBID
                      AND INSTANCE_NUMBER = &INST_NUM)
   and begin_time < (SELECT END_INTERVAL_TIME
                       FROM DBA_HIST_SNAPSHOT
                      WHERE SNAP_ID = &end_snap
                        AND DBID = &DBID
                        AND INSTANCE_NUMBER = &INST_NUM)
 group by undotsn;

----Undo Segment Stats

select endt, undob, txcnt, maxq, maxc, mintun, snolno, blkst, undotsn
  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 end_time > (SELECT END_INTERVAL_TIME
                             FROM DBA_HIST_SNAPSHOT
                            WHERE SNAP_ID = &beg_snap
                              AND DBID = &DBID
                              AND INSTANCE_NUMBER = &INST_NUM)
           and begin_time <
               (SELECT END_INTERVAL_TIME
                  FROM DBA_HIST_SNAPSHOT
                 WHERE SNAP_ID = &end_snap
                   AND DBID = &DBID
                   AND INSTANCE_NUMBER = &INST_NUM)
         order by begin_time desc)
 where rownum < 35;

----Latch Activity

select b.latch_name 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 = &beg_snap
   and e.snap_id = &end_snap
   and b.dbid = &dbid
   and e.dbid = &dbid
   and b.instance_number = &inst_num
   and e.instance_number = &inst_num
   and b.latch_hash = e.latch_hash
   and (e.gets - b.gets + e.immediate_gets - b.immediate_gets) > 0
 order by b.latch_name;

----Latch Sleep Breakdown

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,
       e.sleep1 - b.sleep1 sleep1,
       e.sleep2 - b.sleep2 sleep2,
       e.sleep3 - b.sleep3 sleep3
  from dba_hist_latch b, dba_hist_latch e
 where b.snap_id = &beg_snap
   and e.snap_id = &end_snap
   and b.dbid = &dbid
   and e.dbid = &dbid
   and b.instance_number = &inst_num
   and e.instance_number = &inst_num
   and b.latch_hash = e.latch_hash
   and e.sleeps - b.sleeps > 0
 order by misses desc, name;

----Latch Miss Sources

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(+) = &beg_snap
   and e.snap_id = &end_snap
   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, e.where_in_code;

----Parent Latch Statistics

select e.latch_name parent,
       e.gets - b.gets gets,
       e.misses - b.misses misses,
       e.sleeps - b.sleeps sleeps,
       to_char(e.spin_gets - b.spin_gets) || '  ' ||
       to_char(e.sleep1 - b.sleep1) || '  ' ||
       to_char(e.sleep2 - b.sleep2) || '  ' ||
       to_char(e.sleep3 - b.sleep3) sleephist
  from dba_hist_latch_parent b, dba_hist_latch_parent e
 where b.snap_id = &beg_snap
   and e.snap_id = &end_snap
   and b.dbid = &dbid
   and e.dbid = &dbid
   and b.instance_number = &inst_num
   and e.instance_number = &inst_num
   and b.latch_hash = e.latch_hash
   and e.sleeps - b.sleeps > 0
 order by parent;

----Child Latch Statistics

select /*+ ordered use_hash(b) */
 e.latch_name name,
 e.child# child,
 e.gets - b.gets gets,
 e.misses - b.misses misses,
 e.sleeps - b.sleeps sleeps,
 to_char(e.spin_gets - b.spin_gets) || '  ' ||
 to_char(e.sleep1 - b.sleep1) || '  ' || to_char(e.sleep2 - b.sleep2) ||
 '  ' || to_char(e.sleep3 - b.sleep3) sleephist
  from dba_hist_latch_children e, dba_hist_latch_children b
 where b.snap_id = &beg_snap
   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.latch_hash = e.latch_hash
   and b.child# = e.child#
   and e.sleeps - b.sleeps > 0
   and (e.sleeps - b.sleeps) 
       decode(e.gets - b.gets, 0, 1, e.gets - b.gets) > .00001
 order by name, gets desc;

----Segments by Logical Reads

select owner,
       tablespace_name,
       object_name,
       subobject_name,
       object_type,
       logical_reads,
       ratio
  from (select n.owner,
               n.tablespace_name,
               n.object_name,
               case
                 when length(n.subobject_name) < 11 then
                  n.subobject_name
                 else
                  substr(n.subobject_name, length(n.subobject_name) - 9)
               end subobject_name,
               n.object_type,
               r.logical_reads,
               decode((SELECT sum(e.VALUE) - sum(b.value)
                        FROM DBA_HIST_SYSSTAT b, DBA_HIST_SYSSTAT e
                       WHERE B.SNAP_ID = &beg_snap
                         AND E.SNAP_ID = &end_snap
                         AND B.DBID = &DBID
                         AND E.DBID = &DBID
                         AND B.INSTANCE_NUMBER = &INST_NUM
                         AND E.INSTANCE_NUMBER = &INST_NUM
                         and e.STAT_NAME = 'session logical reads'
                         and b.stat_name = 'session logical reads'),
                      0,
                      to_number(null),
                      100 * logical_reads 
                      (SELECT sum(e.VALUE) - sum(b.value)
                         FROM DBA_HIST_SYSSTAT b, DBA_HIST_SYSSTAT e
                        WHERE B.SNAP_ID = &beg_snap
                          AND E.SNAP_ID = &end_snap
                          AND B.DBID = &DBID
                          AND E.DBID = &DBID
                          AND B.INSTANCE_NUMBER = &INST_NUM
                          AND E.INSTANCE_NUMBER = &INST_NUM
                          and e.STAT_NAME = 'session logical reads'
                          and b.stat_name = 'session logical reads')) ratio
          from dba_hist_seg_stat_obj n,
               (select dataobj#,
                       obj#,
                       dbid,
                       sum(logical_reads_delta) logical_reads
                  from dba_hist_seg_stat
                 where &beg_snap < snap_id
                   and snap_id <= &end_snap
                   and dbid = &dbid
                   and instance_number = &inst_num
                 group by dataobj#, obj#, dbid) r
         where n.dataobj# = r.dataobj#
           and n.obj# = r.obj#
           and n.dbid = r.dbid
           and r.logical_reads > 0
         order by r.logical_reads desc, object_name, owner, subobject_name)
 where rownum <= 5;

----Segments by Physical Reads

select owner,       tablespace_name,       object_name,       subobject_name,       object_type,       physical_reads,       ratio  from (select n.owner,               n.tablespace_name,               n.object_name,               case                 when length(n.subobject_name) < 11 then                  n.subobject_name                 else                  substr(n.subobject_name, length(n.subobject_name) - 9)               end subobject_name,               n.object_type,               r.physical_reads,               decode((SELECT sum(e.VALUE) - sum(b.value)                        FROM DBA_HIST_SYSSTAT b, DBA_HIST_SYSSTAT e                       WHERE B.SNAP_ID = &beg_snap                         AND E.SNAP_ID = &end_snap                         AND B.DBID = &DBID                         AND E.DBID = &DBID                         AND B.INSTANCE_NUMBER = &INST_NUM                         AND E.INSTANCE_NUMBER = &INST_NUM                         and e.STAT_NAME = 'physical reads'                         and b.STAT_NAME = 'physical reads'),                      0,                      to_number(null),                      100 * r.physical_reads                       (SELECT sum(e.VALUE) - sum(b.value)                         FROM DBA_HIST_SYSSTAT b, DBA_HIST_SYSSTAT e                        WHERE B.SNAP_ID = &beg_snap                          AND E.SNAP_ID = &end_snap                          AND B.DBID = &DBID                          AND E.DBID = &DBID                          AND B.INSTANCE_NUMBER = &INST_NUM                          AND E.INSTANCE_NUMBER = &INST_NUM                          and e.STAT_NAME = 'physical reads'                          and b.STAT_NAME = 'physical reads')) ratio          from dba_hist_seg_stat_obj n,               (select dataobj#,                       obj#,                       dbid,                       sum(physical_reads_delta) physical_reads                  from dba_hist_seg_stat                 where &beg_snap < snap_id                   and snap_id <= &end_snap                   and dbid = &dbid                   and instance_number = &inst_num                 group by dataobj#, obj#, dbid) r         where n.dataobj# = r.dataobj#           and n.obj# = r.obj#           and n.dbid = r.dbid           and r.physical_reads > 0         order by r.physical_reads desc, object_name, owner, subobject_name) where rownum <= 5;

----Segments by Row Lock Waits

select owner,
       tablespace_name,
       object_name,
       subobject_name,
       object_type,
       row_lock_waits,
       ratio
  from (select n.owner,
               n.tablespace_name,
               n.object_name,
               case
                 when length(n.subobject_name) < 11 then
                  n.subobject_name
                 else
                  substr(n.subobject_name, length(n.subobject_name) - 9)
               end subobject_name,
               n.object_type,
               r.row_lock_waits,
               round(r.ratio * 100, 2) ratio
          from dba_hist_seg_stat_obj n,
               (select dataobj#,
                       obj#,
                       dbid,
                       sum(row_lock_waits_delta) row_lock_waits,
                       ratio_to_report(sum(row_lock_waits_delta)) over() ratio
                  from dba_hist_seg_stat
                 where &beg_snap < snap_id
                   and snap_id <= &end_snap
                   and dbid = &dbid
                   and instance_number = &inst_num
                 group by dataobj#, obj#, dbid) r
         where n.dataobj# = r.dataobj#
           and n.obj# = r.obj#
           and n.dbid = r.dbid
           and r.row_lock_waits > 0
         order by r.row_lock_waits desc, object_name, owner, subobject_name)
 where rownum <= 5;

----Segments by ITL Waits

select owner,
       tablespace_name,
       object_name,
       subobject_name,
       object_type,
       itl_waits,
       ratio
  from (select n.owner,
               n.tablespace_name,
               n.object_name,
               case
                 when length(n.subobject_name) < 11 then
                  n.subobject_name
                 else
                  substr(n.subobject_name, length(n.subobject_name) - 9)
               end subobject_name,
               n.object_type,
               r.itl_waits,
               round(r.ratio * 100, 2) ratio
          from dba_hist_seg_stat_obj n,
               (select dataobj#,
                       obj#,
                       dbid,
                       sum(itl_waits_delta) itl_waits,
                       ratio_to_report(sum(itl_waits_delta)) over() ratio
                  from dba_hist_seg_stat
                 where &beg_snap < snap_id
                   and snap_id <= &end_snap
                   and dbid = &dbid
                   and instance_number = &inst_num
                 group by dataobj#, obj#, dbid) r
         where n.dataobj# = r.dataobj#
           and n.obj# = r.obj#
           and n.dbid = r.dbid
           and r.itl_waits > 0
         order by r.itl_waits desc, object_name, owner, subobject_name)
 where rownum <= 5;

----Segments by Buffer Busy Waits

select owner,
       tablespace_name,
       object_name,
       subobject_name,
       object_type,
       buffer_busy_waits,
       ratio
  from (select n.owner,
               n.tablespace_name,
               n.object_name,
               case
                 when length(n.subobject_name) < 11 then
                  n.subobject_name
                 else
                  substr(n.subobject_name, length(n.subobject_name) - 9)
               end subobject_name,
               n.object_type,
               r.buffer_busy_waits,
               round(r.ratio * 100, 2) ratio
          from dba_hist_seg_stat_obj n,
               (select dataobj#,
                       obj#,
                       dbid,
                       sum(buffer_busy_waits_delta) buffer_busy_waits,
                       ratio_to_report(sum(buffer_busy_waits_delta)) over() ratio
                  from dba_hist_seg_stat
                 where &beg_snap < snap_id
                   and snap_id <= &end_snap
                   and dbid = &dbid
                   and instance_number = &inst_num
                 group by dataobj#, obj#, dbid) r
         where n.dataobj# = r.dataobj#
           and n.obj# = r.obj#
           and n.dbid = r.dbid
           and r.buffer_busy_waits > 0
         order by r.buffer_busy_waits desc,
                  object_name,
                  owner,
                  subobject_name)
 where rownum <= 5;

----Segments by Global Cache Buffer Busy

select owner,
       tablespace_name,
       object_name,
       subobject_name,
       object_type,
       gc_buffer_busy,
       ratio
  from (select n.owner,
               n.tablespace_name,
               n.object_name,
               case
                 when length(n.subobject_name) < 11 then
                  n.subobject_name
                 else
                  substr(n.subobject_name, length(n.subobject_name) - 9)
               end subobject_name,
               n.object_type,
               r.gc_buffer_busy,
               round(r.ratio * 100, 2) ratio
          from dba_hist_seg_stat_obj n,
               (select dataobj#,
                       obj#,
                       dbid,
                       sum(gc_buffer_busy_delta) gc_buffer_busy,
                       ratio_to_report(sum(gc_buffer_busy_delta)) over() ratio
                  from dba_hist_seg_stat
                 where &beg_snap < snap_id
                   and snap_id <= &end_snap
                   and dbid = &dbid
                   and instance_number = &inst_num
                 group by dataobj#, obj#, dbid) r
         where n.dataobj# = r.dataobj#
           and n.obj# = r.obj#
           and n.dbid = r.dbid
           and r.gc_buffer_busy > 0
         order by r.gc_buffer_busy desc, object_name, owner, subobject_name)
 where rownum <= 5;

----Segments by CR Blocks Received

select owner,
       tablespace_name,
       object_name,
       subobject_name,
       object_type,
       cr_blocks_received,
       ratio
  from (select n.owner,
               n.tablespace_name,
               n.object_name,
               case
                 when length(n.subobject_name) < 11 then
                  n.subobject_name
                 else
                  substr(n.subobject_name, length(n.subobject_name) - 9)
               end subobject_name,
               n.object_type,
               r.cr_blocks_received,
               decode((SELECT sum(e.VALUE) - sum(b.value)
                        FROM DBA_HIST_SYSSTAT b, DBA_HIST_SYSSTAT e
                       WHERE B.SNAP_ID = &beg_snap
                         AND E.SNAP_ID = &end_snap
                         AND B.DBID = &DBID
                         AND E.DBID = &DBID
                         AND B.INSTANCE_NUMBER = &INST_NUM
                         AND E.INSTANCE_NUMBER = &INST_NUM
                         and e.STAT_NAME = 'gc cr blocks received'
                         and b.stat_name = 'gc cr blocks received'),
                      0,
                      to_number(null),
                      100 * cr_blocks_received /
                      (SELECT sum(e.VALUE) - sum(b.value)
                         FROM DBA_HIST_SYSSTAT b, DBA_HIST_SYSSTAT e
                        WHERE B.SNAP_ID = &beg_snap
                          AND E.SNAP_ID = &end_snap
                          AND B.DBID = &DBID
                          AND E.DBID = &DBID
                          AND B.INSTANCE_NUMBER = &INST_NUM
                          AND E.INSTANCE_NUMBER = &INST_NUM
                          and e.STAT_NAME = 'gc cr blocks received'
                          and b.stat_name = 'gc cr blocks received')) ratio
          from dba_hist_seg_stat_obj n,
               (select dataobj#,
                       obj#,
                       dbid,
                       sum(gc_cr_blocks_received_delta) cr_blocks_received
                  from dba_hist_seg_stat
                 where &beg_snap < snap_id
                   and snap_id <= &end_snap
                   and dbid = &dbid
                   and instance_number = &inst_num
                 group by dataobj#, obj#, dbid) r
         where n.dataobj# = r.dataobj#
           and n.obj# = r.obj#
           and n.dbid = r.dbid
           and r.cr_blocks_received > 0
         order by r.cr_blocks_received desc,
                  object_name,
                  owner,
                  subobject_name)
 where rownum <= 5;

----Segments by Current Blocks Received

select owner,
       tablespace_name,
       object_name,
       subobject_name,
       object_type,
       cu_blocks_received,
       ratio
  from (select n.owner,
               n.tablespace_name,
               n.object_name,
               case
                 when length(n.subobject_name) < 11 then
                  n.subobject_name
                 else
                  substr(n.subobject_name, length(n.subobject_name) - 9)
               end subobject_name,
               n.object_type,
               r.cu_blocks_received,
               decode((SELECT sum(e.VALUE) - sum(b.value)
                        FROM DBA_HIST_SYSSTAT b, DBA_HIST_SYSSTAT e
                       WHERE B.SNAP_ID = &beg_snap
                         AND E.SNAP_ID = &end_snap
                         AND B.DBID = &DBID
                         AND E.DBID = &DBID
                         AND B.INSTANCE_NUMBER = &INST_NUM
                         AND E.INSTANCE_NUMBER = &INST_NUM
                         and e.STAT_NAME = 'gc current blocks received'
                         and b.stat_name = 'gc current blocks received'),
                      0,
                      to_number(null),
                      100 * cu_blocks_received /
                      (SELECT sum(e.VALUE) - sum(b.value)
                         FROM DBA_HIST_SYSSTAT b, DBA_HIST_SYSSTAT e
                        WHERE B.SNAP_ID = &beg_snap
                          AND E.SNAP_ID = &end_snap
                          AND B.DBID = &DBID
                          AND E.DBID = &DBID
                          AND B.INSTANCE_NUMBER = &INST_NUM
                          AND E.INSTANCE_NUMBER = &INST_NUM
                          and e.STAT_NAME = 'gc current blocks received'
                          and b.stat_name = 'gc current blocks received')) ratio
          from dba_hist_seg_stat_obj n,
               (select dataobj#,
                       obj#,
                       dbid,
                       sum(gc_cu_blocks_received_delta) cu_blocks_received
                  from dba_hist_seg_stat
                 where &beg_snap < snap_id
                   and snap_id <= &end_snap
                   and dbid = &dbid
                   and instance_number = &inst_num
                 group by dataobj#, obj#, dbid) r
         where n.dataobj# = r.dataobj#
           and n.obj# = r.obj#
           and n.dbid = r.dbid
           and r.cu_blocks_received > 0
         order by r.cu_blocks_received desc,
                  object_name,
                  owner,
                  subobject_name)
 where rownum <= 5;

----Dictionary Cache Stats

select lower(b.parameter) param,
       e.gets - b.gets gets,
       to_number(decode(e.gets,
                        b.gets,
                        null,
                        (e.getmisses - b.getmisses) * 100 /
                        (e.gets - b.gets))) getm,
       e.scans - b.scans scans,
       to_number(decode(e.scans,
                        b.scans,
                        null,
                        (e.scanmisses - b.scanmisses) * 100 /
                        (e.scans - b.scans))) scanm,
       e.modifications - b.modifications mods,
       e.usage usage
  from dba_hist_rowcache_summary b, dba_hist_rowcache_summary e
 where b.snap_id = &beg_snap
   and e.snap_id = &end_snap
   and b.dbid = &dbid
   and e.dbid = &dbid
   and b.instance_number = &inst_num
   and e.instance_number = &inst_num
   and b.parameter = e.parameter
   and e.gets - b.gets > 0
 order by param;

----Dictionary Cache Stats (RAC)

select lower(b.parameter) param,
       e.dlm_requests - b.dlm_requests dreq,
       e.dlm_conflicts - b.dlm_conflicts dcon,
       e.dlm_releases - b.dlm_releases drel
  from dba_hist_rowcache_summary b, dba_hist_rowcache_summary e
 where b.snap_id = &beg_snap
   and e.snap_id = &end_snap
   and b.dbid = &dbid
   and e.dbid = &dbid
   and b.instance_number = &inst_num
   and e.instance_number = &inst_num
   and b.parameter = e.parameter
   and e.dlm_requests - b.dlm_requests > 0
 order by param;

----Library Cache Activity

select b.namespace,
       e.gets - b.gets gets,
       to_number(decode(e.gets,
                        b.gets,
                        null,
                        100 -
                        (e.gethits - b.gethits) * 100 / (e.gets - b.gets))) getm,
       e.pins - b.pins pins,
       to_number(decode(e.pins,
                        b.pins,
                        null,
                        100 -
                        (e.pinhits - b.pinhits) * 100 / (e.pins - b.pins))) pinm,
       e.reloads - b.reloads reloads,
       e.invalidations - b.invalidations inv
  from dba_hist_librarycache b, dba_hist_librarycache e
 where b.snap_id = &beg_snap
   and e.snap_id = &end_snap
   and b.dbid = &dbid
   and e.dbid = &dbid
   and b.instance_number = &inst_num
   and e.instance_number = &inst_num
   and b.namespace = e.namespace
   and e.gets - b.gets > 0;

----Library Cache Activity (RAC)

select b.namespace,
       e.dlm_lock_requests - b.dlm_lock_requests dlreq,
       e.dlm_pin_requests - b.dlm_pin_requests dpreq,
       e.dlm_pin_releases - b.dlm_pin_releases dprel,
       e.dlm_invalidation_requests - b.dlm_invalidation_requests direq,
       e.dlm_invalidations - b.dlm_invalidations dinv
  from dba_hist_librarycache b, dba_hist_librarycache e
 where b.snap_id = &beg_snap
   and e.snap_id = &end_snap
   and b.dbid = &dbid
   and e.dbid = &dbid
   and b.instance_number = &inst_num
   and e.instance_number = &inst_num
   and b.namespace = e.namespace
   and e.dlm_lock_requests - b.dlm_lock_requests > 0;

----Process Memory Summary

select decode(snap_id, &beg_snap, 'B', &end_snap, 'E') b_or_e,
       category,
       allocated_total / 1024 / 1024,
       used_total / 1024 / 1024,
       allocated_avg / 1024 / 1024,
       allocated_stddev / 1024 / 1024,
       allocated_max / 1024 / 1024,
       max_allocated_max / 1024 / 1024,
       num_processes,
       non_zero_allocs
  from dba_hist_process_mem_summary
 where dbid = &dbid
   and instance_number = &inst_num
   and snap_id in (&beg_snap, &end_snap)
 order by snap_id, allocated_total desc;

----SGA Memory Summary

select s1.name,
       s1.value,
       decode(s2.value,
              s1.value,
              NULL,
              to_char(s2.value, '99,999,999,999,990'))
  from dba_hist_sga s1, dba_hist_sga s2
 where s1.snap_id = &beg_snap
   and s2.snap_id = &end_snap
   and s1.dbid = &dbid
   and s2.dbid = &dbid
   and s1.instance_number = &inst_num
   and s2.instance_number = &inst_num
   and s1.name = s2.name
 order by name;

----SGA breakdown difference

select replace(pool, 'pool', '') pool, name, snap1, snap2, diff
  from (select nvl(e.pool, b.pool) pool,
               nvl(e.name, b.name) name,
               b.bytes / 1024 / 1024 snap1,
               e.bytes / 1024 / 1024 snap2,
               decode(b.bytes,
                      NULL,
                      to_number(NULL),
                      100 * (nvl(e.bytes, 0) - b.bytes) / b.bytes) diff
          from (select *
                  from dba_hist_sgastat
                 where snap_id = &beg_snap
                   and dbid = &dbid
                   and instance_number = &inst_num) b
          full outer join (select *
                            from dba_hist_sgastat
                           where snap_id = &end_snap
                             and dbid = &dbid
                             and instance_number = &inst_num) e on b.name =
                                                                   e.name
                                                               and nvl(b.pool,
                                                                       'a') =
                                                                   nvl(e.pool,
                                                                       'a')
         order by nvl(e.bytes, b.bytes))
 order by pool, name;

----Rule Set

select e.owner || '.' || e.name rulesetname,
       e.evaluations - nvl(b.evaluations, 0) evaluations,
       e.sql_free_evaluations - nvl(b.sql_free_evaluations, 0),
       e.sql_executions - nvl(b.sql_executions, 0),
       e.cpu_time - nvl(b.cpu_time, 0),
       e.elapsed_time - nvl(b.elapsed_time, 0)
  from dba_hist_rule_set b, dba_hist_rule_set e
 where b.snap_id(+) = &beg_snap
   and e.snap_id = &end_snap
   and e.dbid = &dbid
   and b.dbid(+) = e.dbid
   and e.instance_number = &inst_num
   and b.instance_number(+) = e.instance_number
   and b.owner(+) = e.owner
   and b.name(+) = e.name
   and b.startup_time(+) = e.startup_time
 order by evaluations desc;

----Resource Limit Stats

select resource_name       rname,
       current_utilization curu,
       max_utilization     maxu,
       initial_allocation  inita,
       limit_value         lim
  from dba_hist_resource_limit
 where snap_id = &end_snap
   and dbid = &dbid
   and instance_number = &inst_num
   and (nvl(current_utilization, 0) / limit_value > .8 or
       nvl(max_utilization, 0) / limit_value > .8)
 order by rname;

----init.ora Parameters

select e.parameter_name name,
       b.value bval,
       decode(b.value, e.value, NULL, e.value) eval
  from dba_hist_parameter b, dba_hist_parameter e
 where b.snap_id(+) = &beg_snap
   and e.snap_id = &end_snap
   and b.dbid(+) = &dbid
   and e.dbid = &dbid
   and b.instance_number(+) = &inst_num
   and e.instance_number = &inst_num
   and b.parameter_hash(+) = e.parameter_hash
   and (nvl(b.isdefault, 'X') = 'FALSE' or
        nvl(b.ismodified, 'X') != 'FALSE' or e.ismodified != 'FALSE' or
        nvl(e.value, 0) != nvl(b.value, 0))
   and e.parameter_name not like '\_\_%' escape '\'
 order by e.parameter_name;

----Global Enqueue Statistics

select b.name st,
       e.value - b.value dif,
       round(e.value - b.value) /
       (SELECT EXTRACT(DAY FROM E.END_INTERVAL_TIME - B.END_INTERVAL_TIME) *
               86400 +
               EXTRACT(HOUR FROM E.END_INTERVAL_TIME - B.END_INTERVAL_TIME) * 3600 +
               EXTRACT(MINUTE FROM E.END_INTERVAL_TIME - B.END_INTERVAL_TIME) * 60 +
               EXTRACT(SECOND FROM E.END_INTERVAL_TIME - B.END_INTERVAL_TIME)
          FROM DBA_HIST_SNAPSHOT B, DBA_HIST_SNAPSHOT E
         WHERE B.SNAP_ID = &beg_snap
           AND E.SNAP_ID = &end_snap
           AND B.DBID = &DBID
           AND E.DBID = &DBID
           AND B.INSTANCE_NUMBER = &INST_NUM
           AND E.INSTANCE_NUMBER = &INST_NUM
           AND B.STARTUP_TIME = E.STARTUP_TIME
           AND B.END_INTERVAL_TIME < E.END_INTERVAL_TIME) ps,
       round(e.value - b.value) /
       (SELECT sum(e.value) - sum(b.value)
          FROM DBA_HIST_SYSSTAT B, DBA_HIST_SYSSTAT E
         WHERE B.SNAP_ID = &beg_snap
           AND E.SNAP_ID = &end_snap
           AND B.DBID = &DBID
           AND E.DBID = &DBID
           AND B.INSTANCE_NUMBER = &INST_NUM
           AND E.INSTANCE_NUMBER = &INST_NUM
           and e.STAT_NAME in ('user rollbacks', 'user commits')
           and b.STAT_NAME in ('user rollbacks', 'user commits')) pt
  from dba_hist_dlm_misc b, dba_hist_dlm_misc e
 where b.snap_id = &beg_snap
   and e.snap_id = &end_snap
   and b.instance_number = &inst_num
   and e.instance_number = &inst_num
   and b.dbid = &dbid
   and e.dbid = &dbid
   and e.statistic# = b.statistic#
 order by b.name;

----Global Cache Transfer Stats

select inst,
       class,
       crtotal,
       100 * decode(crtotal, 0, to_number(null), crblk / crtotal),
       100 * decode(crtotal, 0, to_number(null), crbusy / crtotal),
       100 * decode(crtotal, 0, to_number(null), crcong / crtotal),
       cutotal,
       100 * decode(cutotal, 0, to_number(null), cublk / cutotal),
       100 * decode(cutotal, 0, to_number(null), cubusy / cutotal),
       100 * decode(cutotal, 0, to_number(null), cucong / cutotal)
  from (select e.instance inst,
               e.class class,
               e.cr_block - nvl(b.cr_block, 0) crblk,
               e.cr_busy - nvl(b.cr_busy, 0) crbusy,
               e.cr_congested - nvl(b.cr_congested, 0) crcong,
               (e.cr_block - nvl(b.cr_block, 0) + e.cr_busy -
               nvl(b.cr_busy, 0) + e.cr_congested - nvl(b.cr_congested, 0)) crtotal,
               e.current_block - nvl(b.current_block, 0) cublk,
               e.current_busy - nvl(b.current_busy, 0) cubusy,
               e.current_congested - nvl(b.current_congested, 0) cucong,
               (e.current_block - nvl(b.current_block, 0) + e.current_busy -
               nvl(b.current_busy, 0) + e.current_congested -
               nvl(b.current_congested, 0)) cutotal
          from dba_hist_inst_cache_transfer b,
               dba_hist_inst_cache_transfer e
         where b.snap_id(+) = &beg_snap
           and e.snap_id = &end_snap
           and b.instance_number(+) = &inst_num
           and e.instance_number = &inst_num
           and b.dbid(+) = &dbid
           and e.dbid = &dbid
           and b.class(+) = e.class
           and b.instance(+) = e.instance
           and (e.cr_block + e.current_block + e.cr_busy + e.current_busy +
                e.cr_congested + e.current_congested) -
               (nvl(b.cr_block, 0) + nvl(b.current_block, 0) +
                nvl(b.cr_busy, 0) + nvl(b.cr_congested, 0) +
                nvl(b.current_busy, 0) + nvl(b.current_congested, 0)) > 0)
 order by crtotal + cutotal desc, class;

数据库

文章转载自数据与人，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

硬货 | AWR报告指标背后的SQL逻辑（三）

评论