一、摘要：
编写出发点：
网络上大量关于分析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报告关键指标一览
select to_char(s.startup_time,'dd Mon "at" HH24:mi:ss') instart_fmt
, di.dbid dbid
, di.instance_name inst_name
, di.db_name db_name
, s.snap_id snap_id
, to_char(s.end_interval_time,'dd Mon YYYY HH24:mi') snapdat
, s.snap_level lvl
from dba_hist_snapshot s
, dba_hist_database_instance di
where
di.dbid = s.dbid
and di.instance_number = s.instance_number
and di.startup_time = s.startup_time
order by db_name, instance_name, snap_id;
133 10 1月 at 11:52:29 697726329 mycim MYCIM 109716 10 8月 2022 10:00 1
132 10 1月 at 11:52:29 697726329 mycim MYCIM 109715 10 8月 2022 09:00 1
---- 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 cursors , b.value sessions,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 ,
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;
---- 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 (memory)')) - ---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 (memory)'))) /
(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 (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 ('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;
----% 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;
---shared pool
---- 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);
----Enqueue Activity
select /*+ ordered */
substr(e.eq_type || ' - ' || to_char(nvl(l.name, ' ')) ||
decode(upper(e.req_reason),
' CONTENTION ',
null,
' - ',
null,
'(' || e.req_reason || ') '),
1,
78) ety,
e.total_req# - nvl(b.total_req#, 0) reqs,
e.succ_req# - nvl(b.succ_req#, 0) sreq,
e.failed_req# - nvl(b.failed_req#, 0) freq,
e.total_wait# - nvl(b.total_wait#, 0) waits,
(e.cum_wait_time - nvl(b.cum_wait_time, 0)) 1000 wttm,
decode((e.total_wait# - nvl(b.total_wait#, 0)),
0,
to_number(NULL),
((e.cum_wait_time - nvl(b.cum_wait_time, 0))
(e.total_wait# - nvl(b.total_wait#, 0)))) awttm
from dba_hist_enqueue_stat b, dba_hist_enqueue_stat e, v$lock_type l
where b.snap_id(+) = &beg_snap
and e.snap_id = &end_snap
and b.dbid(+) = &dbid
and e.dbid = &dbid
and b.dbid(+) = e.dbid
and b.instance_number(+) = &inst_num
and e.instance_number = &inst_num
and b.instance_number(+) = e.instance_number
and b.eq_type(+) = e.eq_type
and b.req_reason(+) = e.req_reason
and e.total_wait# - nvl(b.total_wait#, 0) > 0
and l.type(+) = e.eq_type
order by wttm desc, waits desc, e.eq_type;
----Undo Segment Summary
select undotsn,
sum(undoblks) 1000 undob,
sum(txncount) txcnt,
max(maxquerylen) maxq,
max(maxconcurrency) maxc,
min(tuned_undoretention) 60 || '/' ||
max(tuned_undoretention) 60 mintun,
sum(ssolderrcnt) || '/' || sum(nospaceerrcnt) snolno,
sum(unxpstealcnt) || '/' || sum(unxpblkrelcnt) || '/' ||
sum(unxpblkreucnt) || '/' || sum(expstealcnt) || '/' ||
sum(expblkrelcnt) || '/' || sum(expblkreucnt) blkst
from dba_hist_undostat
where dbid = &dbid
and instance_number = &inst_num
and end_time > (SELECT END_INTERVAL_TIME
FROM DBA_HIST_SNAPSHOT
WHERE SNAP_ID = &beg_snap
AND DBID = &DBID
AND INSTANCE_NUMBER = &INST_NUM)
and begin_time < (SELECT END_INTERVAL_TIME
FROM DBA_HIST_SNAPSHOT
WHERE SNAP_ID = &end_snap
AND DBID = &DBID
AND INSTANCE_NUMBER = &INST_NUM)
group by undotsn;
----Undo Segment Stats
select endt, undob, txcnt, maxq, maxc, mintun, snolno, blkst, undotsn
from (select undotsn,
to_char(end_time, 'DD-Mon HH24:MI') endt,
undoblks undob,
txncount txcnt,
maxquerylen maxq,
maxconcurrency maxc,
tuned_undoretention 60 mintun,
ssolderrcnt || '' '' || nospaceerrcnt snolno,
unxpstealcnt || '/' || unxpblkrelcnt || '/' || unxpblkreucnt || '/' ||
expstealcnt || '/' || expblkrelcnt || '/' || expblkreucnt blkst
from dba_hist_undostat
where dbid = &dbid
and instance_number = &inst_num
and end_time > (SELECT END_INTERVAL_TIME
FROM DBA_HIST_SNAPSHOT
WHERE SNAP_ID = &beg_snap
AND DBID = &DBID
AND INSTANCE_NUMBER = &INST_NUM)
and begin_time <
(SELECT END_INTERVAL_TIME
FROM DBA_HIST_SNAPSHOT
WHERE SNAP_ID = &end_snap
AND DBID = &DBID
AND INSTANCE_NUMBER = &INST_NUM)
order by begin_time desc)
where rownum < 35;
----Latch Activity
select b.latch_name name,
e.gets - b.gets gets,
to_number(decode(e.gets,
b.gets,
null,
(e.misses - b.misses) * 100 (e.gets - b.gets))) missed,
to_number(decode(e.misses,
b.misses,
null,
(e.sleeps - b.sleeps) (e.misses - b.misses))) sleeps,
(e.wait_time - b.wait_time) 1000000 wt,
e.immediate_gets - b.immediate_gets nowai,
to_number(decode(e.immediate_gets,
b.immediate_gets,
null,
(e.immediate_misses - b.immediate_misses) * 100
(e.immediate_gets - b.immediate_gets))) imiss
from dba_hist_latch b, dba_hist_latch e
where b.snap_id = &beg_snap
and e.snap_id = &end_snap
and b.dbid = &dbid
and e.dbid = &dbid
and b.instance_number = &inst_num
and e.instance_number = &inst_num
and b.latch_hash = e.latch_hash
and (e.gets - b.gets + e.immediate_gets - b.immediate_gets) > 0
order by b.latch_name;
----Latch Sleep Breakdown
select b.latch_name name,
e.gets - b.gets gets,
e.misses - b.misses misses,
e.sleeps - b.sleeps sleeps,
e.spin_gets - b.spin_gets spin_gets,
e.sleep1 - b.sleep1 sleep1,
e.sleep2 - b.sleep2 sleep2,
e.sleep3 - b.sleep3 sleep3
from dba_hist_latch b, dba_hist_latch e
where b.snap_id = &beg_snap
and e.snap_id = &end_snap
and b.dbid = &dbid
and e.dbid = &dbid
and b.instance_number = &inst_num
and e.instance_number = &inst_num
and b.latch_hash = e.latch_hash
and e.sleeps - b.sleeps > 0
order by misses desc, name;
----Latch Miss Sources
select e.parent_name parent,
e.where_in_code where_from,
e.nwfail_count - nvl(b.nwfail_count, 0) nwmisses,
e.sleep_count - nvl(b.sleep_count, 0) sleeps,
e.wtr_slp_count - nvl(b.wtr_slp_count, 0) waiter_sleeps
from dba_hist_latch_misses_summary b, dba_hist_latch_misses_summary e
where b.snap_id(+) = &beg_snap
and e.snap_id = &end_snap
and b.dbid(+) = &dbid
and e.dbid = &dbid
and b.dbid(+) = e.dbid
and b.instance_number(+) = &inst_num
and e.instance_number = &inst_num
and b.instance_number(+) = e.instance_number
and b.parent_name(+) = e.parent_name
and b.where_in_code(+) = e.where_in_code
and e.sleep_count > nvl(b.sleep_count, 0)
order by e.parent_name, sleeps desc, e.where_in_code;
----Parent Latch Statistics
select e.latch_name parent,
e.gets - b.gets gets,
e.misses - b.misses misses,
e.sleeps - b.sleeps sleeps,
to_char(e.spin_gets - b.spin_gets) || ' ' ||
to_char(e.sleep1 - b.sleep1) || ' ' ||
to_char(e.sleep2 - b.sleep2) || ' ' ||
to_char(e.sleep3 - b.sleep3) sleephist
from dba_hist_latch_parent b, dba_hist_latch_parent e
where b.snap_id = &beg_snap
and e.snap_id = &end_snap
and b.dbid = &dbid
and e.dbid = &dbid
and b.instance_number = &inst_num
and e.instance_number = &inst_num
and b.latch_hash = e.latch_hash
and e.sleeps - b.sleeps > 0
order by parent;
----Child Latch Statistics
select /*+ ordered use_hash(b) */
e.latch_name name,
e.child# child,
e.gets - b.gets gets,
e.misses - b.misses misses,
e.sleeps - b.sleeps sleeps,
to_char(e.spin_gets - b.spin_gets) || ' ' ||
to_char(e.sleep1 - b.sleep1) || ' ' || to_char(e.sleep2 - b.sleep2) ||
' ' || to_char(e.sleep3 - b.sleep3) sleephist
from dba_hist_latch_children e, dba_hist_latch_children b
where b.snap_id = &beg_snap
and e.snap_id = &eid
and b.dbid = &dbid
and e.dbid = &dbid
and b.instance_number = &inst_num
and e.instance_number = &inst_num
and b.latch_hash = e.latch_hash
and b.child# = e.child#
and e.sleeps - b.sleeps > 0
and (e.sleeps - b.sleeps)
decode(e.gets - b.gets, 0, 1, e.gets - b.gets) > .00001
order by name, gets desc;
----Segments by Logical Reads
select owner,
tablespace_name,
object_name,
subobject_name,
object_type,
logical_reads,
ratio
from (select n.owner,
n.tablespace_name,
n.object_name,
case
when length(n.subobject_name) < 11 then
n.subobject_name
else
substr(n.subobject_name, length(n.subobject_name) - 9)
end subobject_name,
n.object_type,
r.logical_reads,
decode((SELECT sum(e.VALUE) - sum(b.value)
FROM DBA_HIST_SYSSTAT b, DBA_HIST_SYSSTAT e
WHERE B.SNAP_ID = &beg_snap
AND E.SNAP_ID = &end_snap
AND B.DBID = &DBID
AND E.DBID = &DBID
AND B.INSTANCE_NUMBER = &INST_NUM
AND E.INSTANCE_NUMBER = &INST_NUM
and e.STAT_NAME = 'session logical reads'
and b.stat_name = 'session logical reads'),
0,
to_number(null),
100 * logical_reads
(SELECT sum(e.VALUE) - sum(b.value)
FROM DBA_HIST_SYSSTAT b, DBA_HIST_SYSSTAT e
WHERE B.SNAP_ID = &beg_snap
AND E.SNAP_ID = &end_snap
AND B.DBID = &DBID
AND E.DBID = &DBID
AND B.INSTANCE_NUMBER = &INST_NUM
AND E.INSTANCE_NUMBER = &INST_NUM
and e.STAT_NAME = 'session logical reads'
and b.stat_name = 'session logical reads')) ratio
from dba_hist_seg_stat_obj n,
(select dataobj#,
obj#,
dbid,
sum(logical_reads_delta) logical_reads
from dba_hist_seg_stat
where &beg_snap < snap_id
and snap_id <= &end_snap
and dbid = &dbid
and instance_number = &inst_num
group by dataobj#, obj#, dbid) r
where n.dataobj# = r.dataobj#
and n.obj# = r.obj#
and n.dbid = r.dbid
and r.logical_reads > 0
order by r.logical_reads desc, object_name, owner, subobject_name)
where rownum <= 5;
----Segments by Physical Reads
select owner, tablespace_name, object_name, subobject_name, object_type, physical_reads, ratio from (select n.owner, n.tablespace_name, n.object_name, case when length(n.subobject_name) < 11 then n.subobject_name else substr(n.subobject_name, length(n.subobject_name) - 9) end subobject_name, n.object_type, r.physical_reads, decode((SELECT sum(e.VALUE) - sum(b.value) FROM DBA_HIST_SYSSTAT b, DBA_HIST_SYSSTAT e WHERE B.SNAP_ID = &beg_snap AND E.SNAP_ID = &end_snap AND B.DBID = &DBID AND E.DBID = &DBID AND B.INSTANCE_NUMBER = &INST_NUM AND E.INSTANCE_NUMBER = &INST_NUM and e.STAT_NAME = 'physical reads' and b.STAT_NAME = 'physical reads'), 0, to_number(null), 100 * r.physical_reads (SELECT sum(e.VALUE) - sum(b.value) FROM DBA_HIST_SYSSTAT b, DBA_HIST_SYSSTAT e WHERE B.SNAP_ID = &beg_snap AND E.SNAP_ID = &end_snap AND B.DBID = &DBID AND E.DBID = &DBID AND B.INSTANCE_NUMBER = &INST_NUM AND E.INSTANCE_NUMBER = &INST_NUM and e.STAT_NAME = 'physical reads' and b.STAT_NAME = 'physical reads')) ratio from dba_hist_seg_stat_obj n, (select dataobj#, obj#, dbid, sum(physical_reads_delta) physical_reads from dba_hist_seg_stat where &beg_snap < snap_id and snap_id <= &end_snap and dbid = &dbid and instance_number = &inst_num group by dataobj#, obj#, dbid) r where n.dataobj# = r.dataobj# and n.obj# = r.obj# and n.dbid = r.dbid and r.physical_reads > 0 order by r.physical_reads desc, object_name, owner, subobject_name) where rownum <= 5;
----Segments by Row Lock Waits
select owner,
tablespace_name,
object_name,
subobject_name,
object_type,
row_lock_waits,
ratio
from (select n.owner,
n.tablespace_name,
n.object_name,
case
when length(n.subobject_name) < 11 then
n.subobject_name
else
substr(n.subobject_name, length(n.subobject_name) - 9)
end subobject_name,
n.object_type,
r.row_lock_waits,
round(r.ratio * 100, 2) ratio
from dba_hist_seg_stat_obj n,
(select dataobj#,
obj#,
dbid,
sum(row_lock_waits_delta) row_lock_waits,
ratio_to_report(sum(row_lock_waits_delta)) over() ratio
from dba_hist_seg_stat
where &beg_snap < snap_id
and snap_id <= &end_snap
and dbid = &dbid
and instance_number = &inst_num
group by dataobj#, obj#, dbid) r
where n.dataobj# = r.dataobj#
and n.obj# = r.obj#
and n.dbid = r.dbid
and r.row_lock_waits > 0
order by r.row_lock_waits desc, object_name, owner, subobject_name)
where rownum <= 5;
----Segments by ITL Waits
select owner,
tablespace_name,
object_name,
subobject_name,
object_type,
itl_waits,
ratio
from (select n.owner,
n.tablespace_name,
n.object_name,
case
when length(n.subobject_name) < 11 then
n.subobject_name
else
substr(n.subobject_name, length(n.subobject_name) - 9)
end subobject_name,
n.object_type,
r.itl_waits,
round(r.ratio * 100, 2) ratio
from dba_hist_seg_stat_obj n,
(select dataobj#,
obj#,
dbid,
sum(itl_waits_delta) itl_waits,
ratio_to_report(sum(itl_waits_delta)) over() ratio
from dba_hist_seg_stat
where &beg_snap < snap_id
and snap_id <= &end_snap
and dbid = &dbid
and instance_number = &inst_num
group by dataobj#, obj#, dbid) r
where n.dataobj# = r.dataobj#
and n.obj# = r.obj#
and n.dbid = r.dbid
and r.itl_waits > 0
order by r.itl_waits desc, object_name, owner, subobject_name)
where rownum <= 5;
----Segments by Buffer Busy Waits
select owner,
tablespace_name,
object_name,
subobject_name,
object_type,
buffer_busy_waits,
ratio
from (select n.owner,
n.tablespace_name,
n.object_name,
case
when length(n.subobject_name) < 11 then
n.subobject_name
else
substr(n.subobject_name, length(n.subobject_name) - 9)
end subobject_name,
n.object_type,
r.buffer_busy_waits,
round(r.ratio * 100, 2) ratio
from dba_hist_seg_stat_obj n,
(select dataobj#,
obj#,
dbid,
sum(buffer_busy_waits_delta) buffer_busy_waits,
ratio_to_report(sum(buffer_busy_waits_delta)) over() ratio
from dba_hist_seg_stat
where &beg_snap < snap_id
and snap_id <= &end_snap
and dbid = &dbid
and instance_number = &inst_num
group by dataobj#, obj#, dbid) r
where n.dataobj# = r.dataobj#
and n.obj# = r.obj#
and n.dbid = r.dbid
and r.buffer_busy_waits > 0
order by r.buffer_busy_waits desc,
object_name,
owner,
subobject_name)
where rownum <= 5;
----Segments by Global Cache Buffer Busy
select owner,
tablespace_name,
object_name,
subobject_name,
object_type,
gc_buffer_busy,
ratio
from (select n.owner,
n.tablespace_name,
n.object_name,
case
when length(n.subobject_name) < 11 then
n.subobject_name
else
substr(n.subobject_name, length(n.subobject_name) - 9)
end subobject_name,
n.object_type,
r.gc_buffer_busy,
round(r.ratio * 100, 2) ratio
from dba_hist_seg_stat_obj n,
(select dataobj#,
obj#,
dbid,
sum(gc_buffer_busy_delta) gc_buffer_busy,
ratio_to_report(sum(gc_buffer_busy_delta)) over() ratio
from dba_hist_seg_stat
where &beg_snap < snap_id
and snap_id <= &end_snap
and dbid = &dbid
and instance_number = &inst_num
group by dataobj#, obj#, dbid) r
where n.dataobj# = r.dataobj#
and n.obj# = r.obj#
and n.dbid = r.dbid
and r.gc_buffer_busy > 0
order by r.gc_buffer_busy desc, object_name, owner, subobject_name)
where rownum <= 5;
----Segments by CR Blocks Received
select owner,
tablespace_name,
object_name,
subobject_name,
object_type,
cr_blocks_received,
ratio
from (select n.owner,
n.tablespace_name,
n.object_name,
case
when length(n.subobject_name) < 11 then
n.subobject_name
else
substr(n.subobject_name, length(n.subobject_name) - 9)
end subobject_name,
n.object_type,
r.cr_blocks_received,
decode((SELECT sum(e.VALUE) - sum(b.value)
FROM DBA_HIST_SYSSTAT b, DBA_HIST_SYSSTAT e
WHERE B.SNAP_ID = &beg_snap
AND E.SNAP_ID = &end_snap
AND B.DBID = &DBID
AND E.DBID = &DBID
AND B.INSTANCE_NUMBER = &INST_NUM
AND E.INSTANCE_NUMBER = &INST_NUM
and e.STAT_NAME = 'gc cr blocks received'
and b.stat_name = 'gc cr blocks received'),
0,
to_number(null),
100 * cr_blocks_received /
(SELECT sum(e.VALUE) - sum(b.value)
FROM DBA_HIST_SYSSTAT b, DBA_HIST_SYSSTAT e
WHERE B.SNAP_ID = &beg_snap
AND E.SNAP_ID = &end_snap
AND B.DBID = &DBID
AND E.DBID = &DBID
AND B.INSTANCE_NUMBER = &INST_NUM
AND E.INSTANCE_NUMBER = &INST_NUM
and e.STAT_NAME = 'gc cr blocks received'
and b.stat_name = 'gc cr blocks received')) ratio
from dba_hist_seg_stat_obj n,
(select dataobj#,
obj#,
dbid,
sum(gc_cr_blocks_received_delta) cr_blocks_received
from dba_hist_seg_stat
where &beg_snap < snap_id
and snap_id <= &end_snap
and dbid = &dbid
and instance_number = &inst_num
group by dataobj#, obj#, dbid) r
where n.dataobj# = r.dataobj#
and n.obj# = r.obj#
and n.dbid = r.dbid
and r.cr_blocks_received > 0
order by r.cr_blocks_received desc,
object_name,
owner,
subobject_name)
where rownum <= 5;
----Segments by Current Blocks Received
select owner,
tablespace_name,
object_name,
subobject_name,
object_type,
cu_blocks_received,
ratio
from (select n.owner,
n.tablespace_name,
n.object_name,
case
when length(n.subobject_name) < 11 then
n.subobject_name
else
substr(n.subobject_name, length(n.subobject_name) - 9)
end subobject_name,
n.object_type,
r.cu_blocks_received,
decode((SELECT sum(e.VALUE) - sum(b.value)
FROM DBA_HIST_SYSSTAT b, DBA_HIST_SYSSTAT e
WHERE B.SNAP_ID = &beg_snap
AND E.SNAP_ID = &end_snap
AND B.DBID = &DBID
AND E.DBID = &DBID
AND B.INSTANCE_NUMBER = &INST_NUM
AND E.INSTANCE_NUMBER = &INST_NUM
and e.STAT_NAME = 'gc current blocks received'
and b.stat_name = 'gc current blocks received'),
0,
to_number(null),
100 * cu_blocks_received /
(SELECT sum(e.VALUE) - sum(b.value)
FROM DBA_HIST_SYSSTAT b, DBA_HIST_SYSSTAT e
WHERE B.SNAP_ID = &beg_snap
AND E.SNAP_ID = &end_snap
AND B.DBID = &DBID
AND E.DBID = &DBID
AND B.INSTANCE_NUMBER = &INST_NUM
AND E.INSTANCE_NUMBER = &INST_NUM
and e.STAT_NAME = 'gc current blocks received'
and b.stat_name = 'gc current blocks received')) ratio
from dba_hist_seg_stat_obj n,
(select dataobj#,
obj#,
dbid,
sum(gc_cu_blocks_received_delta) cu_blocks_received
from dba_hist_seg_stat
where &beg_snap < snap_id
and snap_id <= &end_snap
and dbid = &dbid
and instance_number = &inst_num
group by dataobj#, obj#, dbid) r
where n.dataobj# = r.dataobj#
and n.obj# = r.obj#
and n.dbid = r.dbid
and r.cu_blocks_received > 0
order by r.cu_blocks_received desc,
object_name,
owner,
subobject_name)
where rownum <= 5;
----Dictionary Cache Stats
select lower(b.parameter) param,
e.gets - b.gets gets,
to_number(decode(e.gets,
b.gets,
null,
(e.getmisses - b.getmisses) * 100 /
(e.gets - b.gets))) getm,
e.scans - b.scans scans,
to_number(decode(e.scans,
b.scans,
null,
(e.scanmisses - b.scanmisses) * 100 /
(e.scans - b.scans))) scanm,
e.modifications - b.modifications mods,
e.usage usage
from dba_hist_rowcache_summary b, dba_hist_rowcache_summary e
where b.snap_id = &beg_snap
and e.snap_id = &end_snap
and b.dbid = &dbid
and e.dbid = &dbid
and b.instance_number = &inst_num
and e.instance_number = &inst_num
and b.parameter = e.parameter
and e.gets - b.gets > 0
order by param;
----Dictionary Cache Stats (RAC)
select lower(b.parameter) param,
e.dlm_requests - b.dlm_requests dreq,
e.dlm_conflicts - b.dlm_conflicts dcon,
e.dlm_releases - b.dlm_releases drel
from dba_hist_rowcache_summary b, dba_hist_rowcache_summary e
where b.snap_id = &beg_snap
and e.snap_id = &end_snap
and b.dbid = &dbid
and e.dbid = &dbid
and b.instance_number = &inst_num
and e.instance_number = &inst_num
and b.parameter = e.parameter
and e.dlm_requests - b.dlm_requests > 0
order by param;
----Library Cache Activity
select b.namespace,
e.gets - b.gets gets,
to_number(decode(e.gets,
b.gets,
null,
100 -
(e.gethits - b.gethits) * 100 / (e.gets - b.gets))) getm,
e.pins - b.pins pins,
to_number(decode(e.pins,
b.pins,
null,
100 -
(e.pinhits - b.pinhits) * 100 / (e.pins - b.pins))) pinm,
e.reloads - b.reloads reloads,
e.invalidations - b.invalidations inv
from dba_hist_librarycache b, dba_hist_librarycache e
where b.snap_id = &beg_snap
and e.snap_id = &end_snap
and b.dbid = &dbid
and e.dbid = &dbid
and b.instance_number = &inst_num
and e.instance_number = &inst_num
and b.namespace = e.namespace
and e.gets - b.gets > 0;
----Library Cache Activity (RAC)
select b.namespace,
e.dlm_lock_requests - b.dlm_lock_requests dlreq,
e.dlm_pin_requests - b.dlm_pin_requests dpreq,
e.dlm_pin_releases - b.dlm_pin_releases dprel,
e.dlm_invalidation_requests - b.dlm_invalidation_requests direq,
e.dlm_invalidations - b.dlm_invalidations dinv
from dba_hist_librarycache b, dba_hist_librarycache e
where b.snap_id = &beg_snap
and e.snap_id = &end_snap
and b.dbid = &dbid
and e.dbid = &dbid
and b.instance_number = &inst_num
and e.instance_number = &inst_num
and b.namespace = e.namespace
and e.dlm_lock_requests - b.dlm_lock_requests > 0;
----Process Memory Summary
select decode(snap_id, &beg_snap, 'B', &end_snap, 'E') b_or_e,
category,
allocated_total / 1024 / 1024,
used_total / 1024 / 1024,
allocated_avg / 1024 / 1024,
allocated_stddev / 1024 / 1024,
allocated_max / 1024 / 1024,
max_allocated_max / 1024 / 1024,
num_processes,
non_zero_allocs
from dba_hist_process_mem_summary
where dbid = &dbid
and instance_number = &inst_num
and snap_id in (&beg_snap, &end_snap)
order by snap_id, allocated_total desc;
----SGA Memory Summary
select s1.name,
s1.value,
decode(s2.value,
s1.value,
NULL,
to_char(s2.value, '99,999,999,999,990'))
from dba_hist_sga s1, dba_hist_sga s2
where s1.snap_id = &beg_snap
and s2.snap_id = &end_snap
and s1.dbid = &dbid
and s2.dbid = &dbid
and s1.instance_number = &inst_num
and s2.instance_number = &inst_num
and s1.name = s2.name
order by name;
----SGA breakdown difference
select replace(pool, 'pool', '') pool, name, snap1, snap2, diff
from (select nvl(e.pool, b.pool) pool,
nvl(e.name, b.name) name,
b.bytes / 1024 / 1024 snap1,
e.bytes / 1024 / 1024 snap2,
decode(b.bytes,
NULL,
to_number(NULL),
100 * (nvl(e.bytes, 0) - b.bytes) / b.bytes) diff
from (select *
from dba_hist_sgastat
where snap_id = &beg_snap
and dbid = &dbid
and instance_number = &inst_num) b
full outer join (select *
from dba_hist_sgastat
where snap_id = &end_snap
and dbid = &dbid
and instance_number = &inst_num) e on b.name =
e.name
and nvl(b.pool,
'a') =
nvl(e.pool,
'a')
order by nvl(e.bytes, b.bytes))
order by pool, name;
----Rule Set
select e.owner || '.' || e.name rulesetname,
e.evaluations - nvl(b.evaluations, 0) evaluations,
e.sql_free_evaluations - nvl(b.sql_free_evaluations, 0),
e.sql_executions - nvl(b.sql_executions, 0),
e.cpu_time - nvl(b.cpu_time, 0),
e.elapsed_time - nvl(b.elapsed_time, 0)
from dba_hist_rule_set b, dba_hist_rule_set e
where b.snap_id(+) = &beg_snap
and e.snap_id = &end_snap
and e.dbid = &dbid
and b.dbid(+) = e.dbid
and e.instance_number = &inst_num
and b.instance_number(+) = e.instance_number
and b.owner(+) = e.owner
and b.name(+) = e.name
and b.startup_time(+) = e.startup_time
order by evaluations desc;
----Resource Limit Stats
select resource_name rname,
current_utilization curu,
max_utilization maxu,
initial_allocation inita,
limit_value lim
from dba_hist_resource_limit
where snap_id = &end_snap
and dbid = &dbid
and instance_number = &inst_num
and (nvl(current_utilization, 0) / limit_value > .8 or
nvl(max_utilization, 0) / limit_value > .8)
order by rname;
----init.ora Parameters
select e.parameter_name name,
b.value bval,
decode(b.value, e.value, NULL, e.value) eval
from dba_hist_parameter b, dba_hist_parameter e
where b.snap_id(+) = &beg_snap
and e.snap_id = &end_snap
and b.dbid(+) = &dbid
and e.dbid = &dbid
and b.instance_number(+) = &inst_num
and e.instance_number = &inst_num
and b.parameter_hash(+) = e.parameter_hash
and (nvl(b.isdefault, 'X') = 'FALSE' or
nvl(b.ismodified, 'X') != 'FALSE' or e.ismodified != 'FALSE' or
nvl(e.value, 0) != nvl(b.value, 0))
and e.parameter_name not like '\_\_%' escape '\'
order by e.parameter_name;
----Global Enqueue Statistics
select b.name st,
e.value - b.value dif,
round(e.value - b.value) /
(SELECT EXTRACT(DAY FROM E.END_INTERVAL_TIME - B.END_INTERVAL_TIME) *
86400 +
EXTRACT(HOUR FROM E.END_INTERVAL_TIME - B.END_INTERVAL_TIME) * 3600 +
EXTRACT(MINUTE FROM E.END_INTERVAL_TIME - B.END_INTERVAL_TIME) * 60 +
EXTRACT(SECOND FROM E.END_INTERVAL_TIME - B.END_INTERVAL_TIME)
FROM DBA_HIST_SNAPSHOT B, DBA_HIST_SNAPSHOT E
WHERE B.SNAP_ID = &beg_snap
AND E.SNAP_ID = &end_snap
AND B.DBID = &DBID
AND E.DBID = &DBID
AND B.INSTANCE_NUMBER = &INST_NUM
AND E.INSTANCE_NUMBER = &INST_NUM
AND B.STARTUP_TIME = E.STARTUP_TIME
AND B.END_INTERVAL_TIME < E.END_INTERVAL_TIME) ps,
round(e.value - b.value) /
(SELECT sum(e.value) - sum(b.value)
FROM DBA_HIST_SYSSTAT B, DBA_HIST_SYSSTAT E
WHERE B.SNAP_ID = &beg_snap
AND E.SNAP_ID = &end_snap
AND B.DBID = &DBID
AND E.DBID = &DBID
AND B.INSTANCE_NUMBER = &INST_NUM
AND E.INSTANCE_NUMBER = &INST_NUM
and e.STAT_NAME in ('user rollbacks', 'user commits')
and b.STAT_NAME in ('user rollbacks', 'user commits')) pt
from dba_hist_dlm_misc b, dba_hist_dlm_misc e
where b.snap_id = &beg_snap
and e.snap_id = &end_snap
and b.instance_number = &inst_num
and e.instance_number = &inst_num
and b.dbid = &dbid
and e.dbid = &dbid
and e.statistic# = b.statistic#
order by b.name;
----Global Cache Transfer Stats
select inst,
class,
crtotal,
100 * decode(crtotal, 0, to_number(null), crblk / crtotal),
100 * decode(crtotal, 0, to_number(null), crbusy / crtotal),
100 * decode(crtotal, 0, to_number(null), crcong / crtotal),
cutotal,
100 * decode(cutotal, 0, to_number(null), cublk / cutotal),
100 * decode(cutotal, 0, to_number(null), cubusy / cutotal),
100 * decode(cutotal, 0, to_number(null), cucong / cutotal)
from (select e.instance inst,
e.class class,
e.cr_block - nvl(b.cr_block, 0) crblk,
e.cr_busy - nvl(b.cr_busy, 0) crbusy,
e.cr_congested - nvl(b.cr_congested, 0) crcong,
(e.cr_block - nvl(b.cr_block, 0) + e.cr_busy -
nvl(b.cr_busy, 0) + e.cr_congested - nvl(b.cr_congested, 0)) crtotal,
e.current_block - nvl(b.current_block, 0) cublk,
e.current_busy - nvl(b.current_busy, 0) cubusy,
e.current_congested - nvl(b.current_congested, 0) cucong,
(e.current_block - nvl(b.current_block, 0) + e.current_busy -
nvl(b.current_busy, 0) + e.current_congested -
nvl(b.current_congested, 0)) cutotal
from dba_hist_inst_cache_transfer b,
dba_hist_inst_cache_transfer e
where b.snap_id(+) = &beg_snap
and e.snap_id = &end_snap
and b.instance_number(+) = &inst_num
and e.instance_number = &inst_num
and b.dbid(+) = &dbid
and e.dbid = &dbid
and b.class(+) = e.class
and b.instance(+) = e.instance
and (e.cr_block + e.current_block + e.cr_busy + e.current_busy +
e.cr_congested + e.current_congested) -
(nvl(b.cr_block, 0) + nvl(b.current_block, 0) +
nvl(b.cr_busy, 0) + nvl(b.cr_congested, 0) +
nvl(b.current_busy, 0) + nvl(b.current_congested, 0)) > 0)
order by crtotal + cutotal desc, class;
2844
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
