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

数据与人 2020-12-15

1269

一、摘要：

编写出发点：

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

----% Blocks changed per Read:

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 ('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'))),
             2)
  from dual;

----% Blocks changed per Read:

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 ('recursive 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 ('recursive 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 ('recursive 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 ('recursive 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 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'))),
             2)
  from dual;

---- Rollback 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 rollbacks')) -
             (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'))) 
             ((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;

----Rows per Sort:

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 (rows)')) -
             (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 (rows)'))) 
             ((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 (memory)')) -
             (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 (memory)')) +
             (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)'))),
             2)
  from dual;

---- Buffer Nowait %:

select round(100 *
             (1 - ((SELECT SUM(WAIT_COUNT)
                      FROM DBA_HIST_WAITSTAT
                     WHERE SNAP_ID = &end_snap
                       AND DBID = &L_DBID
                       AND INSTANCE_NUMBER = &L_INST_NUM) -
             (SELECT SUM(WAIT_COUNT)
                      FROM DBA_HIST_WAITSTAT
                     WHERE SNAP_ID = &beg_snap
                       AND DBID = &L_DBID
                       AND INSTANCE_NUMBER = &L_INST_NUM)) 
             ((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')))),
             2)
  from dual;

---- Redo NoWait %:

select round(100 *
             (1 - ((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 log space requests')) -
             (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 log space requests'))) 
             ((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 entries')) -
             (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 entries')))),
             2)
  from dual;

---- Buffer Hit %:

select round(100 *
             (1 -
             ((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 ('physical reads direct')) -
             (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 direct'))) -
             nvl(((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 direct (lob)')) -
                   (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 direct (lob)'))),
                   0)) 
             ((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')))),
             2)
  from dual;

---- In-memory Sort %:

select round(100 * ((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 (memory)')) -
             (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 (memory)'))) 
             (((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 (memory)')) -
             (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 (memory)'))) +
             ((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)')))),
             2)
  from dual;

---- Library Hit %

SELECT round(100 * (SUM(e.PINHITS) - sum(b.pinhits)) 
             (SUM(e.PINS) - sum(b.pins)),
             2)
  FROM DBA_HIST_LIBRARYCACHE b, DBA_HIST_LIBRARYCACHE e
 WHERE e.SNAP_ID = &end_SNAP
   AND e.DBID = &L_DBID
   AND e.INSTANCE_NUMBER = &L_INST_NUM
   and b.SNAP_ID = &beg_SNAP
   AND b.DBID = &L_DBID
   AND b.INSTANCE_NUMBER = &L_INST_NUM

---- Soft Parse %:

select round(100 * (1 -
               ((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 = '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 = '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 = '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 = 'parse count (total)'))),
               2)
          from dual;

----Execute to Parse %:

select round(100 * (1 - ((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 = '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 = '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 = '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 = 'execute count'))),
             2)
  from dual;

----Latch Hit %:

SELECT round(100 * (1 - (SUM(e.MISSES) - sum(b.MISSES)) 
             (SUM(e.GETS) - sum(b.GETS))),
             2)
  FROM DBA_HIST_LATCH b, DBA_HIST_LATCH e
 WHERE e.SNAP_ID = &end_SNAP
   AND e.DBID = &L_DBID
   AND e.INSTANCE_NUMBER = &L_INST_NUM
   and b.SNAP_ID = &beg_SNAP
   AND b.DBID = &L_DBID
   AND b.INSTANCE_NUMBER = &L_INST_NUM;

----Parse CPU to Parse Elapsd %:

select round(100 * ((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 = 'parse time cpu') -
             (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 = 'parse time cpu')) 
             ((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 = 'parse time elapsed') -
             (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 = 'parse time elapsed')),
             2)
  from dual;

----% Non-Parse CPU:

select round(100 *
             (1 - ((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 = 'parse time cpu') -
             (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 = 'parse time cpu')) 
             (((SELECT sum(value)
                       FROM DBA_HIST_SYS_TIME_MODEL e
                      WHERE e.SNAP_ID = &end_snap
                        AND e.DBID = &L_DBID
                        AND e.INSTANCE_NUMBER = &L_INST_NUM
                        AND e.STAT_NAME = 'DB CPU') -
             (SELECT sum(value)
                       FROM DBA_HIST_SYS_TIME_MODEL b
                      WHERE b.SNAP_ID = &beg_snap
                        AND b.DBID = &L_DBID
                        AND b.INSTANCE_NUMBER = &L_INST_NUM
                        AND b.STAT_NAME = 'DB CPU'))  10000)),
             2)
  from dual;

---- Memory Usage %:begin

select round(100 *
             (1 - (SELECT bytes
                     FROM DBA_HIST_SGASTAT
                    WHERE SNAP_ID = &beg_snap
                      AND DBID = &L_DBID
                      AND INSTANCE_NUMBER = &L_INST_NUM
                      AND NAME = 'free memory'
                      and pool IN ('shared pool', 'all pools')) 
             (SELECT sum(value)
                     FROM DBA_HIST_PARAMETER
                    WHERE SNAP_ID = &beg_snap
                      AND DBID = &L_DBID
                      AND INSTANCE_NUMBER = &L_INST_NUM
                      AND PARAMETER_NAME = '__shared_pool_size')),
             2)
  from dual;

---- Memory Usage %:end

select round(100 *
             (1 - (SELECT bytes
                     FROM DBA_HIST_SGASTAT
                    WHERE SNAP_ID = &end_snap
                      AND DBID = &L_DBID
                      AND INSTANCE_NUMBER = &L_INST_NUM
                      AND NAME = 'free memory'
                      and pool IN ('shared pool', 'all pools')) 
             (SELECT sum(value)
                     FROM DBA_HIST_PARAMETER
                    WHERE SNAP_ID = &end_snap
                      AND DBID = &L_DBID
                      AND INSTANCE_NUMBER = &L_INST_NUM
                      AND PARAMETER_NAME = '__shared_pool_size')),
             2)
  from dual;

---- % SQL with executions>1:begin

SELECT DECODE(B.TOTAL_SQL, 0, 0, 100 * (1 - B.SINGLE_USE_SQL  B.TOTAL_SQL))
  FROM DBA_HIST_SQL_SUMMARY B
 Where SNAP_ID = &begin_snap
   AND DBID = &L_DBID
   AND INSTANCE_NUMBER = &L_INST_NUM;

---- % SQL with executions>1:end

SELECT DECODE(e.TOTAL_SQL, 0, 0, 100 * (1 - e.SINGLE_USE_SQL  e.TOTAL_SQL))
  FROM DBA_HIST_SQL_SUMMARY e
 Where SNAP_ID = &end_snap
   AND DBID = &L_DBID
   AND INSTANCE_NUMBER = &L_INST_NUM;

---- % Memory for SQL w/exec>1:begin

SELECT DECODE(B.TOTAL_SQL_MEM,
              0,
              0,
              100 * (1 - B.SINGLE_USE_SQL_MEM  B.TOTAL_SQL_MEM))
  FROM DBA_HIST_SQL_SUMMARY B
 Where SNAP_ID = &begin_snap
   AND DBID = &L_DBID
   AND INSTANCE_NUMBER = &L_INST_NUM;

---- % Memory for SQL w/exec>1:end

SELECT DECODE(e.TOTAL_SQL_MEM,
              0,
              0,
              100 * (1 - e.SINGLE_USE_SQL_MEM  e.TOTAL_SQL_MEM))
  FROM DBA_HIST_SQL_SUMMARY e
 Where SNAP_ID = &end_snap
   AND DBID = &L_DBID
   AND INSTANCE_NUMBER = &L_INST_NUM;

---- Top 5 Timed Events:

SELECT EVENT,
       WAITS,
       TIME,
       DECODE(WAITS,
              NULL,
              TO_NUMBER(NULL),
              0,
              TO_NUMBER(NULL),
              TIME  WAITS * 1000) AVGWT,
       PCTWTT,
       WAIT_CLASS
  FROM (SELECT EVENT, WAITS, TIME, PCTWTT, WAIT_CLASS
          FROM (SELECT E.EVENT_NAME EVENT,
                       E.TOTAL_WAITS - NVL(B.TOTAL_WAITS, 0) WAITS,
                       (E.TIME_WAITED_MICRO - NVL(B.TIME_WAITED_MICRO, 0)) 
                       1000000 TIME,
                       100 *
                       (E.TIME_WAITED_MICRO - NVL(B.TIME_WAITED_MICRO, 0)) 
                       ((SELECT sum(value)
                           FROM DBA_HIST_SYS_TIME_MODEL e
                          WHERE e.SNAP_ID = &end_snap
                            AND e.DBID = &DBID
                            AND e.INSTANCE_NUMBER = &INST_NUM
                            AND e.STAT_NAME = 'DB time') -
                       (SELECT sum(value)
                           FROM DBA_HIST_SYS_TIME_MODEL b
                          WHERE b.SNAP_ID = &beg_snap
                            AND b.DBID = &DBID
                            AND b.INSTANCE_NUMBER = &INST_NUM
                            AND b.STAT_NAME = 'DB time')) PCTWTT,
                       E.WAIT_CLASS WAIT_CLASS
                  FROM DBA_HIST_SYSTEM_EVENT B, DBA_HIST_SYSTEM_EVENT 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.EVENT_ID(+) = E.EVENT_ID
                   AND E.TOTAL_WAITS > NVL(B.TOTAL_WAITS, 0)
                   AND E.WAIT_CLASS != 'Idle'
                UNION ALL
                SELECT 'CPU time' EVENT,
                       TO_NUMBER(NULL) WAITS,
                       ((SELECT sum(value)
                           FROM DBA_HIST_SYS_TIME_MODEL e
                          WHERE e.SNAP_ID = &end_snap
                            AND e.DBID = &DBID
                            AND e.INSTANCE_NUMBER = &INST_NUM
                            AND e.STAT_NAME = 'DB CPU') -
                       (SELECT sum(value)
                           FROM DBA_HIST_SYS_TIME_MODEL b
                          WHERE b.SNAP_ID = &beg_snap
                            AND b.DBID = &DBID
                            AND b.INSTANCE_NUMBER = &INST_NUM
                            AND b.STAT_NAME = 'DB CPU'))  1000000 TIME,
                       100 * ((SELECT sum(value)
                                 FROM DBA_HIST_SYS_TIME_MODEL e
                                WHERE e.SNAP_ID = &end_snap
                                  AND e.DBID = &DBID
                                  AND e.INSTANCE_NUMBER = &INST_NUM
                                  AND e.STAT_NAME = 'DB CPU') -
                       (SELECT sum(value)
                                 FROM DBA_HIST_SYS_TIME_MODEL b
                                WHERE b.SNAP_ID = &beg_snap
                                  AND b.DBID = &DBID
                                  AND b.INSTANCE_NUMBER = &INST_NUM
                                  AND b.STAT_NAME = 'DB CPU')) 
                       ((SELECT sum(value)
                           FROM DBA_HIST_SYS_TIME_MODEL e
                          WHERE e.SNAP_ID = &end_snap
                            AND e.DBID = &L_DBID
                            AND e.INSTANCE_NUMBER = &INST_NUM
                            AND e.STAT_NAME = 'DB time') -
                       (SELECT sum(value)
                           FROM DBA_HIST_SYS_TIME_MODEL b
                          WHERE b.SNAP_ID = &beg_snap
                            AND b.DBID = &DBID
                            AND b.INSTANCE_NUMBER = &INST_NUM
                            AND b.STAT_NAME = 'DB time')) PCTWTT,
                       NULL WAIT_CLASS
                  from dual
                 WHERE ((SELECT sum(value)
                           FROM DBA_HIST_SYS_TIME_MODEL e
                          WHERE e.SNAP_ID = &end_snap
                            AND e.DBID = &DBID
                            AND e.INSTANCE_NUMBER = &INST_NUM
                            AND e.STAT_NAME = 'DB CPU') -
                       (SELECT sum(value)
                           FROM DBA_HIST_SYS_TIME_MODEL b
                          WHERE b.SNAP_ID = &beg_snap
                            AND b.DBID = &DBID
                            AND b.INSTANCE_NUMBER = &INST_NUM
                            AND b.STAT_NAME = 'DB CPU')) > 0)
         ORDER BY TIME DESC, WAITS DESC)
 WHERE ROWNUM <= 5;

---- Global Cache blocks received:per second

  select round(((SELECT e.VALUE - 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') +
               
               (SELECT e.VALUE - 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')) 
               (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),
               2)
    from dual;

---- Global Cache blocks received:per transaction

  select round(((SELECT e.VALUE - 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') +
               (SELECT e.VALUE - 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')) 
               (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')),
               2)
    from dual;

---- Global Cache blocks served:per second

select round(((SELECT e.VALUE - 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 served'
                  and b.stat_name = 'gc cr blocks served') +
             
             (SELECT e.VALUE - 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 served'
                  and b.STAT_NAME = 'gc current blocks served')) 
             (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),
             2)
  from dual;

---- Global Cache blocks served:per transaction

select round(((SELECT e.VALUE - 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 served'
                  and b.stat_name = 'gc cr blocks served') +
             (SELECT e.VALUE - 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 served'
                  and b.STAT_NAME = 'gc current blocks served')) /
             (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')),
             2)
  from dual;

----GCS/GES messages received::per second

select round(((SELECT e.VALUE - 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 = 'gcs msgs received'
                  and b.stat_name = 'gcs msgs received') +
             (SELECT e.VALUE - 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 = 'ges msgs received'
                  and b.STAT_NAME = 'ges msgs received')) /
             (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),
             2)
  from dual;

----GCS/GES messages received::per transaction

  select round(((SELECT e.VALUE - 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 = 'gcs msgs received'
                    and b.stat_name = 'gcs msgs received') +
               (SELECT e.VALUE - 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 = 'ges msgs received'
                    and b.STAT_NAME = 'ges msgs 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 in ('user rollbacks', 'user commits')
                   and b.STAT_NAME in ('user rollbacks', 'user commits')),
               2)
    from dual;

----GCS/GES messages sent:per second

select round(((SELECT e.VALUE - 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 = 'gcs messages sent'
                    and b.stat_name = 'gcs messages sent') +
               (SELECT e.VALUE - 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 = 'ges messages sent'
                    and b.stat_name = 'ges messages sent ')) /
               (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),
               2)
    from dual;

----GCS/GES messages sent:per transaction

select round(((SELECT e.VALUE - 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 = 'gcs messages sent'
                    and b.stat_name = 'gcs messages sent') +
               (SELECT e.VALUE - 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 = 'ges messages sent'
                    and b.stat_name = 'ges messages sent ')) /
               (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')),
               2)
    from dual;

----DBWR Fusion writes:per second

select round((SELECT e.VALUE - 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 = 'DBWR fusion writes'
                 and b.stat_name = 'DBWR fusion 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 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),
             2)
  from dual;

----DBWR Fusion writes:per transaction

select round((SELECT e.VALUE - 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 = 'DBWR fusion writes'
                 and b.stat_name = 'DBWR fusion writes') /
             (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')),
             2)
  from dual;

---- Estd Interconnect traffic (KB)

select round(((SELECT VALUE
                 FROM DBA_HIST_PARAMETER
                WHERE SNAP_ID = &beg_snap
                  AND DBID = &DBID
                  AND INSTANCE_NUMBER = &INST_NUM
                  AND PARAMETER_NAME = 'db_block_size') *
             (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
                      ('gc cr blocks received', 'gc current blocks received',
                       'gc current blocks received',
                       'gc current blocks served')
                  and b.stat_name in
                      ('gc cr blocks received', 'gc current blocks received',
                       'gc current blocks received',
                       'gc current blocks served')) +
             200 *
             (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
                      ('gcs msgs received', 'ges msgs received',
                       'gcs messages sent', 'ges messages sent')
                  and b.stat_name in
                      ('gcs msgs received', 'ges msgs received',
                       'gcs messages sent', 'ges messages sent'))) / 1024 /
             (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),
             2)
  from dual;

---- Buffer access - local cache %:

select round(100 *
             (1 - ((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
                           ('physical reads', 'gc cr blocks received',
                            'gc current blocks received')
                       and b.STAT_NAME in
                           ('physical reads', 'gc cr blocks received',
                            'gc current 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 in ('physical reads direct',
                            'physical reads direct (lob)')
                       and b.stat_name in ('physical reads direct',
                            'physical reads direct (lob)'))) /
             (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')),
             2)
  from dual;

---- Buffer access - remote cache %:

select round(100 * ((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 ('gc cr blocks received',
                             'gc current blocks received')
                        and b.stat_name in ('gc cr blocks received',
                             'gc current 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 = 'session logical reads'
                        and b.stat_name = 'session logical reads')),
             2)
  from dual;

----Buffer access - disk %:

select round(100 * (((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') -
             (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 ('physical reads direct',
                              'physical reads direct (lob)')
                         and b.stat_name in ('physical reads direct',
                              'physical reads direct (lob)'))) /
             (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')),
             2)
  from dual;

---- Avg global enqueue get time (ms):

select decode(v_2, 0, '', v_1 / v_2)
  from (select 10 * (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 = 'global enqueue get time'
                        and e.STAT_NAME = 'global enqueue get time') v_1,
               (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 ('global enqueue gets async',
                        'global enqueue gets sync')
                   and b.stat_name in ('global enqueue gets async',
                        'global enqueue gets sync')) v_2
          from dual);

---- Avg global cache cr block receive time (ms):

select decode(v_2, 0, '', v_1 / v_2)
  from (select 10 * (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 block receive time'
                        and e.STAT_NAME = 'gc cr block receive time') v_1,
               (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') v_2
          from dual);

---- Avg global cache current block receive time (ms):

select decode(v_2, 0, '', v_1 / v_2)
  from (select 10 *
               (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 block receive time'
                   and e.STAT_NAME = 'gc current block receive time') v_1,
               (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') v_2
          from dual);

---- Avg global cache cr block build time (ms):

select decode(v_2, 0, '', v_1 / v_2)
  from (select 10 * (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 block build time'
                        and e.STAT_NAME = 'gc cr block build time') v_1,
               (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 served'
                   and b.stat_name = 'gc cr blocks served') v_2
          from dual);

---- Avg global cache cr block send time (ms):

select decode(v_2, 0, '', v_1 / v_2)
  from (select 10 * (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 block send time'
                        and e.STAT_NAME = 'gc cr block send time') v_1,
               (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 served'
                   and b.stat_name = 'gc cr blocks served') v_2
          from dual);

---- Global cache log flushes for cr blocks served %:

select decode(v_2, 0, '', v_1 / v_2)
  from (select 10 * (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 = 'cr_flushes'
                        and e.STAT_NAME = 'cr_flushes') v_1,
               (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 served'
                   and b.stat_name = 'gc cr blocks served') v_2
          from dual);

---- Avg global cache cr block flush time (ms):

select decode(v_2, 0, '', v_1 / v_2)
  from (select 10 * (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 block flush time'
                        and e.STAT_NAME = 'gc cr block flush time') v_1,
               (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 = 'cr_flushes'
                   and b.stat_name = 'cr_flushes') v_2
          from dual);

---- Avg global cache current block pin time (ms):

select decode(v_2, 0, '', v_1 / v_2)
  from (select 10 * (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 block pin time'
                        and e.STAT_NAME = 'gc current block pin time') v_1,
               (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 served'
                   and b.stat_name = 'gc current blocks served') v_2
          from dual);

---- Avg global cache current block send time (ms):

select decode(v_2, 0, '', v_1 / v_2)
  from (select 10 * (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 block send time'
                        and e.STAT_NAME = 'gc current block send time') v_1,
               (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 served'
                   and b.stat_name = 'gc current blocks served') v_2
          from dual);

---- Global cache log flushes for current blocks served %:

select decode(v_2, 0, '', v_1 / v_2)
  from (select 10 *
               (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 block flush time'
                   and e.STAT_NAME = 'gc current block flush time') v_1,
               (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 = 'current_flushes'
                   and b.stat_name = 'current_flushes') v_2
          from dual);

---- Avg global cache current block flush time (ms):

select decode(v_2, 0, '', v_1 / v_2)
  from (select 10 * (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 = 'current_flushes'
                        and e.STAT_NAME = 'current_flushes') v_1,
               (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 served'
                   and b.stat_name = 'gc current blocks served') v_2
          from dual);

---- Avg message sent queue time (ms):

select decode(v_2, 0, '', v_1 / v_2)
  from (select (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 = 'msgs sent queue time (ms)'
                   and e.STAT_NAME = 'msgs sent queue time (ms)') v_1,
               (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 = 'msgs sent queued'
                   and b.stat_name = 'msgs sent queued') v_2
          from dual);

---- Avg message sent queue time on ksxp (ms):

select decode(v_2, 0, '', v_1 / v_2)
  from (select (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 = 'msgs sent queue time on ksxp (ms)'
                   and e.STAT_NAME = 'msgs sent queue time on ksxp (ms)') v_1,
               (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 = 'msgs sent queued on ksxp'
                   and b.stat_name = 'msgs sent queued on ksxp') v_2
          from dual);

---- Avg message received queue time (ms):

select decode(v_2, 0, '', v_1 / v_2)
  from (select (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 = 'msgs received queue time (ms)'
                   and e.STAT_NAME = 'msgs received queue time (ms)') v_1,
               (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 = 'msgs received queued'
                   and b.stat_name = 'msgs received queued') v_2
          from dual);

---- Avg GCS message process time (ms):

select decode(v_2, 0, '', v_1 / v_2)
  from (select (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 = 'gcs msgs process time(ms)'
                   and e.STAT_NAME = 'gcs msgs process time(ms)') v_1,
               (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 = 'gcs msgs received'
                   and b.stat_name = 'gcs msgs received') v_2
          from dual);

---- Avg GES message process time (ms):

select decode(v_2, 0, '', v_1 / v_2)
  from (select (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 = 'ges msgs process time(ms)'
                   and e.STAT_NAME = 'ges msgs process time(ms)') v_1,
               (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 = 'ges msgs received'
                   and b.stat_name = 'ges msgs received') v_2
          from dual);

数据库

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

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

评论