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

数据与人 2020-12-15

827

一、摘要：

编写出发点：

网络上大量关于分析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报表显示顺序一致）

---- DB Name DB Id Instance Inst num Release RAC Host

SELECT DB_NAME,
       &dbid,
       INSTANCE_NAME,
       &inst_num,
       VERSION,
       PARALLEL,
       HOST_NAME
  FROM DBA_HIST_DATABASE_INSTANCE DI, DBA_HIST_SNAPSHOT S
 WHERE S.SNAP_ID = &beg_snap
   AND S.DBID = &DBID
   AND S.INSTANCE_NUMBER = &INST_NUM
   AND DI.DBID = S.DBID
   AND DI.INSTANCE_NUMBER = S.INSTANCE_NUMBER
   AND DI.STARTUP_TIME = S.STARTUP_TIME;

---- Begin Snap: Snap Time

  SELECT END_INTERVAL_TIME
    FROM DBA_HIST_SNAPSHOT B
   WHERE B.SNAP_ID = &beg_snap
     AND B.DBID = &DBID
     AND B.INSTANCE_NUMBER = &INST_NUM;

---- Begin Snap: Sessions

SELECT VALUE
       FROM DBA_HIST_SYSSTAT
      WHERE SNAP_ID = &beg_snap
        AND DBID = &DBID
        AND INSTANCE_NUMBER = &INST_NUM
        AND STAT_NAME = 'logons current';

---- Begin Snap: Cursors/Session

 select a.value  b.value
       from (SELECT VALUE
               FROM DBA_HIST_SYSSTAT
              WHERE SNAP_ID = &beg_snap
                AND DBID = &DBID
                AND INSTANCE_NUMBER = &INST_NUM
                AND STAT_NAME = 'opened cursors current') a,
            (SELECT VALUE
               FROM DBA_HIST_SYSSTAT
              WHERE SNAP_ID = &beg_snap
                AND DBID = &DBID
                AND INSTANCE_NUMBER = &INST_NUM
                AND STAT_NAME = 'logons current') b;

---- end Snap: Snap Time

SELECT END_INTERVAL_TIME
    FROM DBA_HIST_SNAPSHOT B
   WHERE B.SNAP_ID = &end_snap
     AND B.DBID = &DBID
     AND B.INSTANCE_NUMBER = &INST_NUM;

---- end Snap: Sessions

SELECT VALUE
       FROM DBA_HIST_SYSSTAT
      WHERE SNAP_ID = &end_snap
        AND DBID = &DBID
        AND INSTANCE_NUMBER = &INST_NUM
        AND STAT_NAME = 'logons current';

---- end Snap: Cursors/Session

 select a.value  b.value
       from (SELECT VALUE
               FROM DBA_HIST_SYSSTAT
              WHERE SNAP_ID = &beg_snap
                AND DBID = &DBID
                AND INSTANCE_NUMBER = &INST_NUM
                AND STAT_NAME = 'opened cursors current') a,
            (SELECT VALUE
               FROM DBA_HIST_SYSSTAT
              WHERE SNAP_ID = &end_snap
                AND DBID = &DBID
                AND INSTANCE_NUMBER = &INST_NUM
                AND STAT_NAME = 'logons current') b;

---- Elapsed:

SELECT EXTRACT(DAY FROM E.END_INTERVAL_TIME - B.END_INTERVAL_TIME) * 1440 +
          EXTRACT(HOUR FROM E.END_INTERVAL_TIME - B.END_INTERVAL_TIME) * 60 +
          EXTRACT(MINUTE FROM E.END_INTERVAL_TIME - B.END_INTERVAL_TIME) +
          EXTRACT(SECOND FROM E.END_INTERVAL_TIME - B.END_INTERVAL_TIME)  60
     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;

---- DB Time:

SELECT (sum(e.value) - sum(b.value))  1000000  60
             FROM DBA_HIST_SYS_TIME_MODEL e, DBA_HIST_SYS_TIME_MODEL b
            WHERE e.SNAP_ID = &end_snap
              AND e.DBID = &DBID
              AND e.INSTANCE_NUMBER = &INST_NUM
              AND e.STAT_NAME = 'DB time'
              and b.SNAP_ID = &beg_snap
              AND b.DBID = &DBID
              AND b.INSTANCE_NUMBER = &INST_NUM
              AND b.STAT_NAME = 'DB time';

---- buffer cache: begin end

SELECT e.value  1024  1024, b.value  1024  1024
  FROM DBA_HIST_PARAMETER e, DBA_HIST_PARAMETER b
 WHERE e.SNAP_ID = &end_snap
   AND e.DBID = &DBID
   AND e.INSTANCE_NUMBER = &INST_NUM
   AND e.PARAMETER_NAME = '__db_cache_size'
   and b.SNAP_ID = &beg_snap
   AND b.DBID = &DBID
   AND b.INSTANCE_NUMBER = &INST_NUM
   AND b.PARAMETER_NAME = '__db_cache_size';

---- shared pool size: begin end

SELECT e.value  1024  1024 ||'M', b.value  1024  1024 ||'M'
  FROM DBA_HIST_PARAMETER e, DBA_HIST_PARAMETER b
 WHERE e.SNAP_ID = &end_snap
   AND e.DBID = &DBID
   AND e.INSTANCE_NUMBER = &INST_NUM
   AND e.PARAMETER_NAME = '__shared_pool_size'
   and b.SNAP_ID = &beg_snap
   AND b.DBID = &DBID
   AND b.INSTANCE_NUMBER = &INST_NUM
   AND b.PARAMETER_NAME = '__shared_pool_size';

---- std block size:

SELECT b.value  1024 ||'k'
  FROM DBA_HIST_PARAMETER b
 WHERE b.SNAP_ID = &beg_snap
   AND b.DBID = &DBID
   AND b.INSTANCE_NUMBER = &INST_NUM
   AND b.PARAMETER_NAME = 'db_block_size';

---- log buffer:

SELECT b.value  1024 ||'k'
  FROM DBA_HIST_PARAMETER b
 WHERE b.SNAP_ID = &beg_snap
   AND b.DBID = &DBID
   AND b.INSTANCE_NUMBER = &INST_NUM
   AND b.PARAMETER_NAME = 'log_buffer';

----redo size per second :

select round((SELECT sum(e.value) - sum(b.value)
                 FROM DBA_HIST_SYSSTAT e, DBA_HIST_SYSSTAT b
                WHERE e.SNAP_ID = &end_snap
                  AND e.DBID = &DBID
                  AND e.INSTANCE_NUMBER = &INST_NUM
                  AND e.STAT_NAME = 'redo size'
                  and b.SNAP_ID = &beg_snap
                  AND b.DBID = &DBID
                  AND b.INSTANCE_NUMBER = &INST_NUM
                  AND b.STAT_NAME = 'redo size') 
             (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 e.snap_id = &end_snap
                 and b.snap_id = &beg_snap
                 AND b.DBID = &DBID
                 AND b.INSTANCE_NUMBER = &INST_NUM
                 AND e.DBID = &DBID
                 AND e.INSTANCE_NUMBER = &INST_NUM),
             2)
  from dual;

----redo size per transaction

select round(((SELECT sum(value)
                 FROM DBA_HIST_SYSSTAT e
                WHERE e.SNAP_ID = &end_snap
                  AND e.DBID = &DBID
                  AND e.INSTANCE_NUMBER = &INST_NUM
                  AND e.STAT_NAME in ('redo size')) -
             (SELECT sum(value)
                 FROM DBA_HIST_SYSSTAT b
                WHERE b.SNAP_ID = &beg_snap
                  AND b.DBID = &DBID
                  AND b.INSTANCE_NUMBER = &INST_NUM
                  AND b.STAT_NAME in ('redo size'))) 
             ((SELECT sum(value)
                 FROM DBA_HIST_SYSSTAT e
                WHERE e.SNAP_ID = &end_snap
                  AND e.DBID = &DBID
                  AND e.INSTANCE_NUMBER = &INST_NUM
                  AND e.STAT_NAME in ('user rollbacks', 'user commits')) -
             (SELECT sum(value)
                 FROM DBA_HIST_SYSSTAT b
                WHERE b.SNAP_ID = &beg_snap
                  AND b.DBID = &DBID
                  AND b.INSTANCE_NUMBER = &INST_NUM
                  AND b.STAT_NAME in ('user rollbacks', 'user commits'))),
             2)
  from dual;

---- Logical reads: per second

select round(((SELECT sum(value)
                 FROM DBA_HIST_SYSSTAT e
                WHERE e.SNAP_ID = &end_snap
                  AND e.DBID = &L_DBID
                  AND e.INSTANCE_NUMBER = &L_INST_NUM
                  AND e.STAT_NAME in ('session logical reads')) -
             (SELECT sum(value)
                 FROM DBA_HIST_SYSSTAT b
                WHERE b.SNAP_ID = &beg_snap
                  AND b.DBID = &L_DBID
                  AND b.INSTANCE_NUMBER = &L_INST_NUM
                  AND b.STAT_NAME in ('session logical reads'))) 
             (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 e.snap_id = &end_snap
                 and b.snap_id = &beg_snap
                 AND b.DBID = &L_DBID
                 AND b.INSTANCE_NUMBER = &L_INST_NUM
                 AND e.DBID = &L_DBID
                 AND e.INSTANCE_NUMBER = &L_INST_NUM),
             2)
  from dual;

---- Logical reads: per transaction

select round(((SELECT sum(value)
                 FROM DBA_HIST_SYSSTAT e
                WHERE e.SNAP_ID = &end_snap
                  AND e.DBID = &L_DBID
                  AND e.INSTANCE_NUMBER = &L_INST_NUM
                  AND e.STAT_NAME in ('redo size')) -
             (SELECT sum(value)
                 FROM DBA_HIST_SYSSTAT b
                WHERE b.SNAP_ID = &beg_snap
                  AND b.DBID = &L_DBID
                  AND b.INSTANCE_NUMBER = &L_INST_NUM
                  AND b.STAT_NAME in ('redo size'))) 
             ((SELECT sum(value)
                 FROM DBA_HIST_SYSSTAT e
                WHERE e.SNAP_ID = &end_snap
                  AND e.DBID = &L_DBID
                  AND e.INSTANCE_NUMBER = &L_INST_NUM
                  AND e.STAT_NAME in ('user rollbacks', 'user commits')) -
             (SELECT sum(value)
                 FROM DBA_HIST_SYSSTAT b
                WHERE b.SNAP_ID = &beg_snap
                  AND b.DBID = &L_DBID
                  AND b.INSTANCE_NUMBER = &L_INST_NUM
                  AND b.STAT_NAME in ('user rollbacks', 'user commits'))),
             2)
  from dual;

---- Block changes: per second

select round(((SELECT sum(value)
                 FROM DBA_HIST_SYSSTAT e
                WHERE e.SNAP_ID = &end_snap
                  AND e.DBID = &L_DBID
                  AND e.INSTANCE_NUMBER = &L_INST_NUM
                  AND e.STAT_NAME in ('db block changes')) -
             (SELECT sum(value)
                 FROM DBA_HIST_SYSSTAT b
                WHERE b.SNAP_ID = &beg_snap
                  AND b.DBID = &L_DBID
                  AND b.INSTANCE_NUMBER = &L_INST_NUM
                  AND b.STAT_NAME in ('db block changes'))) /
             (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 e.snap_id = &end_snap
                 and b.snap_id = &beg_snap
                 AND b.DBID = &L_DBID
                 AND b.INSTANCE_NUMBER = &L_INST_NUM
                 AND e.DBID = &L_DBID
                 AND e.INSTANCE_NUMBER = &L_INST_NUM),
             2)
  from dual;

---- Block changes: per transaction

select round(((SELECT sum(value)
                 FROM DBA_HIST_SYSSTAT e
                WHERE e.SNAP_ID = &end_snap
                  AND e.DBID = &L_DBID
                  AND e.INSTANCE_NUMBER = &L_INST_NUM
                  AND e.STAT_NAME in ('db block changes')) -
             (SELECT sum(value)
                 FROM DBA_HIST_SYSSTAT b
                WHERE b.SNAP_ID = &beg_snap
                  AND b.DBID = &L_DBID
                  AND b.INSTANCE_NUMBER = &L_INST_NUM
                  AND b.STAT_NAME in ('db block changes'))) /
             ((SELECT sum(value)
                 FROM DBA_HIST_SYSSTAT e
                WHERE e.SNAP_ID = &end_snap
                  AND e.DBID = &L_DBID
                  AND e.INSTANCE_NUMBER = &L_INST_NUM
                  AND e.STAT_NAME in ('user rollbacks', 'user commits')) -
             (SELECT sum(value)
                 FROM DBA_HIST_SYSSTAT b
                WHERE b.SNAP_ID = &beg_snap
                  AND b.DBID = &L_DBID
                  AND b.INSTANCE_NUMBER = &L_INST_NUM
                  AND b.STAT_NAME in ('user rollbacks', 'user commits'))),
             2)
  from dual;

---- Physical reads: per second

select round(((SELECT sum(value)
                 FROM DBA_HIST_SYSSTAT e
                WHERE e.SNAP_ID = &end_snap
                  AND e.DBID = &L_DBID
                  AND e.INSTANCE_NUMBER = &L_INST_NUM
                  AND e.STAT_NAME in ('physical reads')) -
             (SELECT sum(value)
                 FROM DBA_HIST_SYSSTAT b
                WHERE b.SNAP_ID = &beg_snap
                  AND b.DBID = &L_DBID
                  AND b.INSTANCE_NUMBER = &L_INST_NUM
                  AND b.STAT_NAME in ('physical reads'))) /
             (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 e.snap_id = &end_snap
                 and b.snap_id = &beg_snap
                 AND b.DBID = &L_DBID
                 AND b.INSTANCE_NUMBER = &L_INST_NUM
                 AND e.DBID = &L_DBID
                 AND e.INSTANCE_NUMBER = &L_INST_NUM),
             2)
  from dual;

---- Physical reads: per transaction

select round(((SELECT sum(value)
                 FROM DBA_HIST_SYSSTAT e
                WHERE e.SNAP_ID = &end_snap
                  AND e.DBID = &L_DBID
                  AND e.INSTANCE_NUMBER = &L_INST_NUM
                  AND e.STAT_NAME in ('physical reads')) -
             (SELECT sum(value)
                 FROM DBA_HIST_SYSSTAT b
                WHERE b.SNAP_ID = &beg_snap
                  AND b.DBID = &L_DBID
                  AND b.INSTANCE_NUMBER = &L_INST_NUM
                  AND b.STAT_NAME in ('physical reads'))) /
             ((SELECT sum(value)
                 FROM DBA_HIST_SYSSTAT e
                WHERE e.SNAP_ID = &end_snap
                  AND e.DBID = &L_DBID
                  AND e.INSTANCE_NUMBER = &L_INST_NUM
                  AND e.STAT_NAME in ('user rollbacks', 'user commits')) -
             (SELECT sum(value)
                 FROM DBA_HIST_SYSSTAT b
                WHERE b.SNAP_ID = &beg_snap
                  AND b.DBID = &L_DBID
                  AND b.INSTANCE_NUMBER = &L_INST_NUM
                  AND b.STAT_NAME in ('user rollbacks', 'user commits'))),
             2)
  from dual;

---- Physical writes: per second

select round(((SELECT sum(value)
                 FROM DBA_HIST_SYSSTAT e
                WHERE e.SNAP_ID = &end_snap
                  AND e.DBID = &L_DBID
                  AND e.INSTANCE_NUMBER = &L_INST_NUM
                  AND e.STAT_NAME in ('physical writes')) -
             (SELECT sum(value)
                 FROM DBA_HIST_SYSSTAT b
                WHERE b.SNAP_ID = &beg_snap
                  AND b.DBID = &L_DBID
                  AND b.INSTANCE_NUMBER = &L_INST_NUM
                  AND b.STAT_NAME in ('physical writes'))) /
             (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 e.snap_id = &end_snap
                 and b.snap_id = &beg_snap
                 AND b.DBID = &L_DBID
                 AND b.INSTANCE_NUMBER = &L_INST_NUM
                 AND e.DBID = &L_DBID
                 AND e.INSTANCE_NUMBER = &L_INST_NUM),
             2)
  from dual;

---- Physical writes: per transaction

select round(((SELECT sum(value)
                 FROM DBA_HIST_SYSSTAT e
                WHERE e.SNAP_ID = &end_snap
                  AND e.DBID = &L_DBID
                  AND e.INSTANCE_NUMBER = &L_INST_NUM
                  AND e.STAT_NAME in ('physical writes')) -
             (SELECT sum(value)
                 FROM DBA_HIST_SYSSTAT b
                WHERE b.SNAP_ID = &beg_snap
                  AND b.DBID = &L_DBID
                  AND b.INSTANCE_NUMBER = &L_INST_NUM
                  AND b.STAT_NAME in ('physical writes'))) /
             ((SELECT sum(value)
                 FROM DBA_HIST_SYSSTAT e
                WHERE e.SNAP_ID = &end_snap
                  AND e.DBID = &L_DBID
                  AND e.INSTANCE_NUMBER = &L_INST_NUM
                  AND e.STAT_NAME in ('user rollbacks', 'user commits')) -
             (SELECT sum(value)
                 FROM DBA_HIST_SYSSTAT b
                WHERE b.SNAP_ID = &beg_snap
                  AND b.DBID = &L_DBID
                  AND b.INSTANCE_NUMBER = &L_INST_NUM
                  AND b.STAT_NAME in ('user rollbacks', 'user commits'))),
             2)
  from dual;

---- User calls: per second

select round(((SELECT sum(value)
                 FROM DBA_HIST_SYSSTAT e
                WHERE e.SNAP_ID = &end_snap
                  AND e.DBID = &L_DBID
                  AND e.INSTANCE_NUMBER = &L_INST_NUM
                  AND e.STAT_NAME in ('user calls')) -
             (SELECT sum(value)
                 FROM DBA_HIST_SYSSTAT b
                WHERE b.SNAP_ID = &beg_snap
                  AND b.DBID = &L_DBID
                  AND b.INSTANCE_NUMBER = &L_INST_NUM
                  AND b.STAT_NAME in ('user calls'))) /
             (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 e.snap_id = &end_snap
                 and b.snap_id = &beg_snap
                 AND b.DBID = &L_DBID
                 AND b.INSTANCE_NUMBER = &L_INST_NUM
                 AND e.DBID = &L_DBID
                 AND e.INSTANCE_NUMBER = &L_INST_NUM),
             2)
  from dual;

---- User calls: per transaction

select round(((SELECT sum(value)
                 FROM DBA_HIST_SYSSTAT e
                WHERE e.SNAP_ID = &end_snap
                  AND e.DBID = &L_DBID
                  AND e.INSTANCE_NUMBER = &L_INST_NUM
                  AND e.STAT_NAME in ('user calls')) -
             (SELECT sum(value)
                 FROM DBA_HIST_SYSSTAT b
                WHERE b.SNAP_ID = &beg_snap
                  AND b.DBID = &L_DBID
                  AND b.INSTANCE_NUMBER = &L_INST_NUM
                  AND b.STAT_NAME in ('user calls'))) /
             ((SELECT sum(value)
                 FROM DBA_HIST_SYSSTAT e
                WHERE e.SNAP_ID = &end_snap
                  AND e.DBID = &L_DBID
                  AND e.INSTANCE_NUMBER = &L_INST_NUM
                  AND e.STAT_NAME in ('user rollbacks', 'user commits')) -
             (SELECT sum(value)
                 FROM DBA_HIST_SYSSTAT b
                WHERE b.SNAP_ID = &beg_snap
                  AND b.DBID = &L_DBID
                  AND b.INSTANCE_NUMBER = &L_INST_NUM
                  AND b.STAT_NAME in ('user rollbacks', 'user commits'))),
             2)
  from dual;

---- Parses: per second

select round(((SELECT sum(value)
                 FROM DBA_HIST_SYSSTAT e
                WHERE e.SNAP_ID = &end_snap
                  AND e.DBID = &L_DBID
                  AND e.INSTANCE_NUMBER = &L_INST_NUM
                  AND e.STAT_NAME in ('parse count (total)')) -
             (SELECT sum(value)
                 FROM DBA_HIST_SYSSTAT b
                WHERE b.SNAP_ID = &beg_snap
                  AND b.DBID = &L_DBID
                  AND b.INSTANCE_NUMBER = &L_INST_NUM
                  AND b.STAT_NAME in ('parse count (total)'))) /
             (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 e.snap_id = &end_snap
                 and b.snap_id = &beg_snap
                 AND b.DBID = &L_DBID
                 AND b.INSTANCE_NUMBER = &L_INST_NUM
                 AND e.DBID = &L_DBID
                 AND e.INSTANCE_NUMBER = &L_INST_NUM),
             2)
  from dual;

---- Parses: per transaction

select round(((SELECT sum(value)
                 FROM DBA_HIST_SYSSTAT e
                WHERE e.SNAP_ID = &end_snap
                  AND e.DBID = &L_DBID
                  AND e.INSTANCE_NUMBER = &L_INST_NUM
                  AND e.STAT_NAME in ('parse count (total)')) -
             (SELECT sum(value)
                 FROM DBA_HIST_SYSSTAT b
                WHERE b.SNAP_ID = &beg_snap
                  AND b.DBID = &L_DBID
                  AND b.INSTANCE_NUMBER = &L_INST_NUM
                  AND b.STAT_NAME in ('parse count (total)'))) /
             ((SELECT sum(value)
                 FROM DBA_HIST_SYSSTAT e
                WHERE e.SNAP_ID = &end_snap
                  AND e.DBID = &L_DBID
                  AND e.INSTANCE_NUMBER = &L_INST_NUM
                  AND e.STAT_NAME in ('user rollbacks', 'user commits')) -
             (SELECT sum(value)
                 FROM DBA_HIST_SYSSTAT b
                WHERE b.SNAP_ID = &beg_snap
                  AND b.DBID = &L_DBID
                  AND b.INSTANCE_NUMBER = &L_INST_NUM
                  AND b.STAT_NAME in ('user rollbacks', 'user commits'))),
             2)
  from dual;

---- Hard parses: per second

select round(((SELECT sum(value)
                 FROM DBA_HIST_SYSSTAT e
                WHERE e.SNAP_ID = &end_snap
                  AND e.DBID = &L_DBID
                  AND e.INSTANCE_NUMBER = &L_INST_NUM
                  AND e.STAT_NAME in ('parse count (hard)')) -
             (SELECT sum(value)
                 FROM DBA_HIST_SYSSTAT b
                WHERE b.SNAP_ID = &beg_snap
                  AND b.DBID = &L_DBID
                  AND b.INSTANCE_NUMBER = &L_INST_NUM
                  AND b.STAT_NAME in ('parse count (hard)'))) /
             (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 e.snap_id = &end_snap
                 and b.snap_id = &beg_snap
                 AND b.DBID = &L_DBID
                 AND b.INSTANCE_NUMBER = &L_INST_NUM
                 AND e.DBID = &L_DBID
                 AND e.INSTANCE_NUMBER = &L_INST_NUM),
             2)
  from dual;

---- Hard Parses: per transaction

select round(((SELECT sum(value)
                 FROM DBA_HIST_SYSSTAT e
                WHERE e.SNAP_ID = &end_snap
                  AND e.DBID = &L_DBID
                  AND e.INSTANCE_NUMBER = &L_INST_NUM
                  AND e.STAT_NAME in ('parse count (hard)')) -
             (SELECT sum(value)
                 FROM DBA_HIST_SYSSTAT b
                WHERE b.SNAP_ID = &beg_snap
                  AND b.DBID = &L_DBID
                  AND b.INSTANCE_NUMBER = &L_INST_NUM
                  AND b.STAT_NAME in ('parse count (hard)'))) /
             ((SELECT sum(value)
                 FROM DBA_HIST_SYSSTAT e
                WHERE e.SNAP_ID = &end_snap
                  AND e.DBID = &L_DBID
                  AND e.INSTANCE_NUMBER = &L_INST_NUM
                  AND e.STAT_NAME in ('user rollbacks', 'user commits')) -
             (SELECT sum(value)
                 FROM DBA_HIST_SYSSTAT b
                WHERE b.SNAP_ID = &beg_snap
                  AND b.DBID = &L_DBID
                  AND b.INSTANCE_NUMBER = &L_INST_NUM
                  AND b.STAT_NAME in ('user rollbacks', 'user commits'))),
             2)
  from dual;

---- Sorts: per second

select round(((SELECT sum(value)
                 FROM DBA_HIST_SYSSTAT e
                WHERE e.SNAP_ID = &end_snap
                  AND e.DBID = &L_DBID
                  AND e.INSTANCE_NUMBER = &L_INST_NUM
                  AND e.STAT_NAME in ('sorts (disk)')) -
             (SELECT sum(value)
                 FROM DBA_HIST_SYSSTAT b
                WHERE b.SNAP_ID = &beg_snap
                  AND b.DBID = &L_DBID
                  AND b.INSTANCE_NUMBER = &L_INST_NUM
                  AND b.STAT_NAME in ('sorts (disk)'))) /
             (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 e.snap_id = &end_snap
                 and b.snap_id = &beg_snap
                 AND b.DBID = &L_DBID
                 AND b.INSTANCE_NUMBER = &L_INST_NUM
                 AND e.DBID = &L_DBID
                 AND e.INSTANCE_NUMBER = &L_INST_NUM),
             2)
  from dual;

---- Sorts: per transaction

select round(((SELECT sum(value)
                 FROM DBA_HIST_SYSSTAT e
                WHERE e.SNAP_ID = &end_snap
                  AND e.DBID = &L_DBID
                  AND e.INSTANCE_NUMBER = &L_INST_NUM
                  AND e.STAT_NAME in ('sorts (disk)')) -
             (SELECT sum(value)
                 FROM DBA_HIST_SYSSTAT b
                WHERE b.SNAP_ID = &beg_snap
                  AND b.DBID = &L_DBID
                  AND b.INSTANCE_NUMBER = &L_INST_NUM
                  AND b.STAT_NAME in ('sorts (disk)'))) /
             ((SELECT sum(value)
                 FROM DBA_HIST_SYSSTAT e
                WHERE e.SNAP_ID = &end_snap
                  AND e.DBID = &L_DBID
                  AND e.INSTANCE_NUMBER = &L_INST_NUM
                  AND e.STAT_NAME in ('user rollbacks', 'user commits')) -
             (SELECT sum(value)
                 FROM DBA_HIST_SYSSTAT b
                WHERE b.SNAP_ID = &beg_snap
                  AND b.DBID = &L_DBID
                  AND b.INSTANCE_NUMBER = &L_INST_NUM
                  AND b.STAT_NAME in ('user rollbacks', 'user commits'))),
             2)
  from dual;

---- Logons: per second

select round(((SELECT sum(value)
                 FROM DBA_HIST_SYSSTAT e
                WHERE e.SNAP_ID = &end_snap
                  AND e.DBID = &L_DBID
                  AND e.INSTANCE_NUMBER = &L_INST_NUM
                  AND e.STAT_NAME in ('logons cumulative')) -
             (SELECT sum(value)
                 FROM DBA_HIST_SYSSTAT b
                WHERE b.SNAP_ID = &beg_snap
                  AND b.DBID = &L_DBID
                  AND b.INSTANCE_NUMBER = &L_INST_NUM
                  AND b.STAT_NAME in ('logons cumulative'))) /
             (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 e.snap_id = &end_snap
                 and b.snap_id = &beg_snap
                 AND b.DBID = &L_DBID
                 AND b.INSTANCE_NUMBER = &L_INST_NUM
                 AND e.DBID = &L_DBID
                 AND e.INSTANCE_NUMBER = &L_INST_NUM),
             2)
  from dual;

---- Logons: per transaction

select round(((SELECT sum(value)
                 FROM DBA_HIST_SYSSTAT e
                WHERE e.SNAP_ID = &end_snap
                  AND e.DBID = &L_DBID
                  AND e.INSTANCE_NUMBER = &L_INST_NUM
                  AND e.STAT_NAME in ('logons cumulative')) -
             (SELECT sum(value)
                 FROM DBA_HIST_SYSSTAT b
                WHERE b.SNAP_ID = &beg_snap
                  AND b.DBID = &L_DBID
                  AND b.INSTANCE_NUMBER = &L_INST_NUM
                  AND b.STAT_NAME in ('logons cumulative'))) /
             ((SELECT sum(value)
                 FROM DBA_HIST_SYSSTAT e
                WHERE e.SNAP_ID = &end_snap
                  AND e.DBID = &L_DBID
                  AND e.INSTANCE_NUMBER = &L_INST_NUM
                  AND e.STAT_NAME in ('user rollbacks', 'user commits')) -
             (SELECT sum(value)
                 FROM DBA_HIST_SYSSTAT b
                WHERE b.SNAP_ID = &beg_snap
                  AND b.DBID = &L_DBID
                  AND b.INSTANCE_NUMBER = &L_INST_NUM
                  AND b.STAT_NAME in ('user rollbacks', 'user commits'))),
             2)
  from dual;

---- Executes: per second

select round(((SELECT sum(value)
                 FROM DBA_HIST_SYSSTAT e
                WHERE e.SNAP_ID = &end_snap
                  AND e.DBID = &L_DBID
                  AND e.INSTANCE_NUMBER = &L_INST_NUM
                  AND e.STAT_NAME in ('execute count')) -
             (SELECT sum(value)
                 FROM DBA_HIST_SYSSTAT b
                WHERE b.SNAP_ID = &beg_snap
                  AND b.DBID = &L_DBID
                  AND b.INSTANCE_NUMBER = &L_INST_NUM
                  AND b.STAT_NAME in ('execute count'))) /
             (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 e.snap_id = &end_snap
                 and b.snap_id = &beg_snap
                 AND b.DBID = &L_DBID
                 AND b.INSTANCE_NUMBER = &L_INST_NUM
                 AND e.DBID = &L_DBID
                 AND e.INSTANCE_NUMBER = &L_INST_NUM),
             2)
  from dual;

---- Executes: per transaction

select round(((SELECT sum(value)
                 FROM DBA_HIST_SYSSTAT e
                WHERE e.SNAP_ID = &end_snap
                  AND e.DBID = &L_DBID
                  AND e.INSTANCE_NUMBER = &L_INST_NUM
                  AND e.STAT_NAME in ('execute count')) -
             (SELECT sum(value)
                 FROM DBA_HIST_SYSSTAT b
                WHERE b.SNAP_ID = &beg_snap
                  AND b.DBID = &L_DBID
                  AND b.INSTANCE_NUMBER = &L_INST_NUM
                  AND b.STAT_NAME in ('execute count'))) /
             ((SELECT sum(value)
                 FROM DBA_HIST_SYSSTAT e
                WHERE e.SNAP_ID = &end_snap
                  AND e.DBID = &L_DBID
                  AND e.INSTANCE_NUMBER = &L_INST_NUM
                  AND e.STAT_NAME in ('user rollbacks', 'user commits')) -
             (SELECT sum(value)
                 FROM DBA_HIST_SYSSTAT b
                WHERE b.SNAP_ID = &beg_snap
                  AND b.DBID = &L_DBID
                  AND b.INSTANCE_NUMBER = &L_INST_NUM
                  AND b.STAT_NAME in ('user rollbacks', 'user commits'))),
             2)
  from dual;

----Transactions: per second

select round(((SELECT sum(value)
                 FROM DBA_HIST_SYSSTAT e
                WHERE e.SNAP_ID = &end_snap
                  AND e.DBID = &L_DBID
                  AND e.INSTANCE_NUMBER = &L_INST_NUM
                  AND e.STAT_NAME in ('user rollbacks', 'user commits')) -
             (SELECT sum(value)
                 FROM DBA_HIST_SYSSTAT b
                WHERE b.SNAP_ID = &beg_snap
                  AND b.DBID = &L_DBID
                  AND b.INSTANCE_NUMBER = &L_INST_NUM
                  AND b.STAT_NAME in ('user rollbacks', 'user commits'))) /
             (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 e.snap_id = &end_snap
                 and b.snap_id = &beg_snap
                 AND b.DBID = &L_DBID
                 AND b.INSTANCE_NUMBER = &L_INST_NUM
                 AND e.DBID = &L_DBID
                 AND e.INSTANCE_NUMBER = &L_INST_NUM),
             2)
  from dual;

数据库

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

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

评论