Oracle AWR 的内部 SQL 汇总，通过这些 SQL ，可以大致的了解 AWR 报告中各个指标的计算过程。
#### DB Name DB Id Instance Inst num Release RAC Host
SELECT DB_NAME,
&dbid,
INSTANCE_NAME,
&inst_num,
VERSION,
PARALLEL,
HOST_NAME
FROM DBA_HIST_DATABASE_INSTANCE DI, DBA_HIST_SNAPSHOT S
WHERE S.SNAP_ID = &beg_snap
AND S.DBID = &DBID
AND S.INSTANCE_NUMBER = &INST_NUM
AND DI.DBID = S.DBID
AND DI.INSTANCE_NUMBER = S.INSTANCE_NUMBER AND DI.STARTUP_TIME = S.STARTUP_TIME;
#### Begin Snap: Snap Time
SELECT END_INTERVAL_TIME
FROM DBA_HIST_SNAPSHOT B
WHERE B.SNAP_ID = &beg_snap
AND B.DBID = &DBID
AND B.INSTANCE_NUMBER = &INST_NUM;
#### Begin Snap: Sessions
SELECT VALUE
FROM DBA_HIST_SYSSTAT
WHERE SNAP_ID = &beg_snap
AND DBID = &DBID
AND INSTANCE_NUMBER = &INST_NUM
AND STAT_NAME = 'logons current';
#### Begin Snap: Cursors/Session
select a.value / b.value
from (SELECT VALUE
FROM DBA_HIST_SYSSTAT
WHERE SNAP_ID = &beg_snap
AND DBID = &DBID
AND INSTANCE_NUMBER = &INST_NUM
AND STAT_NAME = 'opened cursors current') a,
(SELECT VALUE
FROM DBA_HIST_SYSSTAT
WHERE SNAP_ID = &beg_snap
AND DBID = &DBID
AND INSTANCE_NUMBER = &INST_NUM
AND STAT_NAME = 'logons current') b;
#### end Snap: Snap Time
SELECT END_INTERVAL_TIME
FROM DBA_HIST_SNAPSHOT B
WHERE B.SNAP_ID = &end_snap
AND B.DBID = &DBID
AND B.INSTANCE_NUMBER = &INST_NUM;
#### end Snap: Sessions
SELECT VALUE
FROM DBA_HIST_SYSSTAT
WHERE SNAP_ID = &end_snap
AND DBID = &DBID
AND INSTANCE_NUMBER = &INST_NUM
AND STAT_NAME = 'logons current';
#### end Snap: Cursors/Session
select a.value / b.value
from (SELECT VALUE
FROM DBA_HIST_SYSSTAT
WHERE SNAP_ID = &beg_snap
AND DBID = &DBID
AND INSTANCE_NUMBER = &INST_NUM
AND STAT_NAME = 'opened cursors current') a,
(SELECT VALUE
FROM DBA_HIST_SYSSTAT
WHERE SNAP_ID = &end_snap
AND DBID = &DBID
AND INSTANCE_NUMBER = &INST_NUM
AND STAT_NAME = 'logons current') b;
#### Elapsed:
SELECT EXTRACT(DAY FROM E.END_INTERVAL_TIME - B.END_INTERVAL_TIME) * 1440 +
EXTRACT(HOUR FROM E.END_INTERVAL_TIME - B.END_INTERVAL_TIME) * 60 +
EXTRACT(MINUTE FROM E.END_INTERVAL_TIME - B.END_INTERVAL_TIME) +
EXTRACT(SECOND FROM E.END_INTERVAL_TIME - B.END_INTERVAL_TIME) / 60
FROM DBA_HIST_SNAPSHOT B, DBA_HIST_SNAPSHOT E
WHERE B.SNAP_ID = &beg_snap
AND E.SNAP_ID = &end_snap
AND B.DBID = &DBID
AND E.DBID = &DBID
AND B.INSTANCE_NUMBER = &INST_NUM
AND E.INSTANCE_NUMBER = &INST_NUM
AND B.STARTUP_TIME = E.STARTUP_TIME
AND B.END_INTERVAL_TIME < E.END_INTERVAL_TIME;
#### DB Time:
SELECT (sum(e.value) - sum(b.value)) / 1000000 / 60
FROM DBA_HIST_SYS_TIME_MODEL e, DBA_HIST_SYS_TIME_MODEL b
WHERE e.SNAP_ID = &end_snap
AND e.DBID = &DBID
AND e.INSTANCE_NUMBER = &INST_NUM AND e.STAT_NAME = 'DB time'
and b.SNAP_ID = &beg_snap
AND b.DBID = &DBID
AND b.INSTANCE_NUMBER = &INST_NUM AND b.STAT_NAME = 'DB time';
#### buffer cache: begin end
SELECT e.value / 1024 / 1024, b.value / 1024 / 1024
FROM DBA_HIST_PARAMETER e, DBA_HIST_PARAMETER b
WHERE e.SNAP_ID = &end_snap
AND e.DBID = &DBID
AND e.INSTANCE_NUMBER = &INST_NUM
AND e.PARAMETER_NAME = '__db_cache_size' and b.SNAP_ID = &beg_snap
AND b.DBID = &DBID
AND b.INSTANCE_NUMBER = &INST_NUM
AND b.PARAMETER_NAME = '__db_cache_size';
#### shared pool size: begin end
SELECT e.value / 1024 / 1024 ||'M', b.value / 1024 / 1024 ||'M'
FROM DBA_HIST_PARAMETER e, DBA_HIST_PARAMETER b
WHERE e.SNAP_ID = &end_snap
AND e.DBID = &DBID
AND e.INSTANCE_NUMBER = &INST_NUM
AND e.PARAMETER_NAME = '__shared_pool_size' and b.SNAP_ID = &beg_snap
AND b.DBID = &DBID
AND b.INSTANCE_NUMBER = &INST_NUM
AND b.PARAMETER_NAME = '__shared_pool_size';
#### std block size:
SELECT b.value / 1024 ||'k'
FROM DBA_HIST_PARAMETER b
WHERE b.SNAP_ID = &beg_snap
AND b.DBID = &DBID
AND b.INSTANCE_NUMBER = &INST_NUM
AND b.PARAMETER_NAME = 'db_block_size';
#### log buffer:
SELECT b.value / 1024 ||'k'
FROM DBA_HIST_PARAMETER b
WHERE b.SNAP_ID = &beg_snap
AND b.DBID = &DBID
AND b.INSTANCE_NUMBER = &INST_NUM
AND b.PARAMETER_NAME = 'log_buffer';
####redo size per second :
select round((SELECT sum(e.value) - sum(b.value)
FROM DBA_HIST_SYSSTAT e, DBA_HIST_SYSSTAT b
WHERE e.SNAP_ID = &end_snap
AND e.DBID = &DBID
AND e.INSTANCE_NUMBER = &INST_NUM AND e.STAT_NAME = 'redo size'
and b.SNAP_ID = &beg_snap
AND b.DBID = &DBID
AND b.INSTANCE_NUMBER = &INST_NUM
AND b.STAT_NAME = 'redo size') /
(SELECT EXTRACT(DAY FROM
E.END_INTERVAL_TIME - B.END_INTERVAL_TIME) *
86400 + EXTRACT(HOUR FROM E.END_INTERVAL_TIME -
B.END_INTERVAL_TIME) * 3600 +
EXTRACT(MINUTE FROM
E.END_INTERVAL_TIME - B.END_INTERVAL_TIME) * 60 +
EXTRACT(SECOND FROM
E.END_INTERVAL_TIME - B.END_INTERVAL_TIME) FROM DBA_HIST_SNAPSHOT B, DBA_HIST_SNAPSHOT E
where e.snap_id = &end_snap
and b.snap_id = &beg_snap AND b.DBID = &DBID
AND b.INSTANCE_NUMBER = &INST_NUM AND e.DBID = &DBID
AND e.INSTANCE_NUMBER = &INST_NUM),
2)
from dual;
####redo size per transaction
select round(((SELECT sum(value)
FROM DBA_HIST_SYSSTAT e
WHERE e.SNAP_ID = &end_snap
AND e.DBID = &DBID
AND e.INSTANCE_NUMBER = &INST_NUM
AND e.STAT_NAME in ('redo size')) -
(SELECT sum(value)
FROM DBA_HIST_SYSSTAT b
WHERE b.SNAP_ID = &beg_snap
AND b.DBID = &DBID
AND b.INSTANCE_NUMBER = &INST_NUM
AND b.STAT_NAME in ('redo size'))) / ((SELECT sum(value)
FROM DBA_HIST_SYSSTAT e
WHERE e.SNAP_ID = &end_snap
AND e.DBID = &DBID
AND e.INSTANCE_NUMBER = &INST_NUM
AND e.STAT_NAME in ('user rollbacks', 'user commits')) -
(SELECT sum(value)
FROM DBA_HIST_SYSSTAT b
WHERE b.SNAP_ID = &beg_snap
AND b.DBID = &DBID
AND b.INSTANCE_NUMBER = &INST_NUM
AND b.STAT_NAME in ('user rollbacks', 'user commits'))),
2)
from dual;
#### Logical reads: per second
select round(((SELECT sum(value)
FROM DBA_HIST_SYSSTAT e
WHERE e.SNAP_ID = &end_snap
AND e.DBID = &L_DBID
AND e.INSTANCE_NUMBER = &L_INST_NUM
AND e.STAT_NAME in ('session logical reads')) -
(SELECT sum(value)
FROM DBA_HIST_SYSSTAT b
WHERE b.SNAP_ID = &beg_snap
AND b.DBID = &L_DBID
AND b.INSTANCE_NUMBER = &L_INST_NUM
AND b.STAT_NAME in ('session logical reads'))) / (SELECT EXTRACT(DAY FROM
E.END_INTERVAL_TIME - B.END_INTERVAL_TIME) * 86400 + EXTRACT(HOUR FROM E.END_INTERVAL_TIME -
B.END_INTERVAL_TIME) * 3600 +
EXTRACT(MINUTE FROM
E.END_INTERVAL_TIME - B.END_INTERVAL_TIME) * 60 +
EXTRACT(SECOND FROM
E.END_INTERVAL_TIME - B.END_INTERVAL_TIME) FROM DBA_HIST_SNAPSHOT B, DBA_HIST_SNAPSHOT E
where e.snap_id = &end_snap
and b.snap_id = &beg_snap AND b.DBID = &L_DBID
AND b.INSTANCE_NUMBER = &L_INST_NUM AND e.DBID = &L_DBID
AND e.INSTANCE_NUMBER = &L_INST_NUM),
2)
from dual;
#### Logical reads: per transaction
select round(((SELECT sum(value)
FROM DBA_HIST_SYSSTAT e
WHERE e.SNAP_ID = &end_snap
AND e.DBID = &L_DBID
AND e.INSTANCE_NUMBER = &L_INST_NUM
AND e.STAT_NAME in ('redo size')) -
(SELECT sum(value)
FROM DBA_HIST_SYSSTAT b
WHERE b.SNAP_ID = &beg_snap
AND b.DBID = &L_DBID
AND b.INSTANCE_NUMBER = &L_INST_NUM
AND b.STAT_NAME in ('redo size'))) /
((SELECT sum(value)
FROM DBA_HIST_SYSSTAT e
WHERE e.SNAP_ID = &end_snap
AND e.DBID = &L_DBID
AND e.INSTANCE_NUMBER = &L_INST_NUM
AND e.STAT_NAME in ('user rollbacks', 'user commits')) -
(SELECT sum(value)
FROM DBA_HIST_SYSSTAT b
WHERE b.SNAP_ID = &beg_snap
AND b.DBID = &L_DBID
AND b.INSTANCE_NUMBER = &L_INST_NUM
AND b.STAT_NAME in ('user rollbacks', 'user commits'))),
2)
from dual;
#### Block changes: per second
select round(((SELECT sum(value)
FROM DBA_HIST_SYSSTAT e
WHERE e.SNAP_ID = &end_snap
AND e.DBID = &L_DBID
AND e.INSTANCE_NUMBER = &L_INST_NUM
AND e.STAT_NAME in ('db block changes')) -
(SELECT sum(value)
FROM DBA_HIST_SYSSTAT b
WHERE b.SNAP_ID = &beg_snap
AND b.DBID = &L_DBID
AND b.INSTANCE_NUMBER = &L_INST_NUM
AND b.STAT_NAME in ('db block changes'))) / (SELECT EXTRACT(DAY FROM
E.END_INTERVAL_TIME - B.END_INTERVAL_TIME) *
86400 + EXTRACT(HOUR FROM E.END_INTERVAL_TIME -
B.END_INTERVAL_TIME) * 3600 +
EXTRACT(MINUTE FROM
E.END_INTERVAL_TIME - B.END_INTERVAL_TIME) * 60 +
EXTRACT(SECOND FROM
E.END_INTERVAL_TIME - B.END_INTERVAL_TIME) FROM DBA_HIST_SNAPSHOT B, DBA_HIST_SNAPSHOT E
where e.snap_id = &end_snap
and b.snap_id = &beg_snap AND b.DBID = &L_DBID
AND b.INSTANCE_NUMBER = &L_INST_NUM AND e.DBID = &L_DBID
AND e.INSTANCE_NUMBER = &L_INST_NUM),
2)
from dual;
#### Block changes: per transaction
select round(((SELECT sum(value)
FROM DBA_HIST_SYSSTAT e
WHERE e.SNAP_ID = &end_snap
AND e.DBID = &L_DBID
AND e.INSTANCE_NUMBER = &L_INST_NUM
AND e.STAT_NAME in ('db block changes')) -
(SELECT sum(value)
FROM DBA_HIST_SYSSTAT b
WHERE b.SNAP_ID = &beg_snap
AND b.DBID = &L_DBID
AND b.INSTANCE_NUMBER = &L_INST_NUM
AND b.STAT_NAME in ('db block changes'))) /
((SELECT sum(value)
FROM DBA_HIST_SYSSTAT e
WHERE e.SNAP_ID = &end_snap
AND e.DBID = &L_DBID
AND e.INSTANCE_NUMBER = &L_INST_NUM
AND e.STAT_NAME in ('user rollbacks', 'user commits')) -
(SELECT sum(value)
FROM DBA_HIST_SYSSTAT b
WHERE b.SNAP_ID = &beg_snap
AND b.DBID = &L_DBID
AND b.INSTANCE_NUMBER = &L_INST_NUM
AND b.STAT_NAME in ('user rollbacks', 'user commits'))),
2)
from dual;
#### Physical reads: per second
select round(((SELECT sum(value)
FROM DBA_HIST_SYSSTAT e
WHERE e.SNAP_ID = &end_snap
AND e.DBID = &L_DBID
AND e.INSTANCE_NUMBER = &L_INST_NUM
AND e.STAT_NAME in ('physical reads')) -
(SELECT sum(value)
FROM DBA_HIST_SYSSTAT b
WHERE b.SNAP_ID = &beg_snap
AND b.DBID = &L_DBID
AND b.INSTANCE_NUMBER = &L_INST_NUM
AND b.STAT_NAME in ('physical reads'))) / (SELECT EXTRACT(DAY FROM
E.END_INTERVAL_TIME - B.END_INTERVAL_TIME) *
86400 + EXTRACT(HOUR FROM E.END_INTERVAL_TIME -
B.END_INTERVAL_TIME) * 3600 +
EXTRACT(MINUTE FROM
E.END_INTERVAL_TIME - B.END_INTERVAL_TIME) * 60 +
EXTRACT(SECOND FROM
E.END_INTERVAL_TIME - B.END_INTERVAL_TIME) FROM DBA_HIST_SNAPSHOT B, DBA_HIST_SNAPSHOT E
where e.snap_id = &end_snap
and b.snap_id = &beg_snap AND b.DBID = &L_DBID
AND b.INSTANCE_NUMBER = &L_INST_NUM AND e.DBID = &L_DBID
AND e.INSTANCE_NUMBER = &L_INST_NUM),
2)
from dual;
#### Physical reads: per transaction
select round(((SELECT sum(value)
FROM DBA_HIST_SYSSTAT e
WHERE e.SNAP_ID = &end_snap
AND e.DBID = &L_DBID
AND e.INSTANCE_NUMBER = &L_INST_NUM
AND e.STAT_NAME in ('physical reads')) -
(SELECT sum(value)
FROM DBA_HIST_SYSSTAT b
WHERE b.SNAP_ID = &beg_snap
AND b.DBID = &L_DBID
AND b.INSTANCE_NUMBER = &L_INST_NUM
AND b.STAT_NAME in ('physical reads'))) / ((SELECT sum(value)
FROM DBA_HIST_SYSSTAT e
WHERE e.SNAP_ID = &end_snap
AND e.DBID = &L_DBID
AND e.INSTANCE_NUMBER = &L_INST_NUM
AND e.STAT_NAME in ('user rollbacks', 'user commits')) -
(SELECT sum(value)
FROM DBA_HIST_SYSSTAT b
WHERE b.SNAP_ID = &beg_snap
AND b.DBID = &L_DBID
AND b.INSTANCE_NUMBER = &L_INST_NUM
AND b.STAT_NAME in ('user rollbacks', 'user commits'))),
2)
from dual;
#### Physical 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 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 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 writes')) -
(SELECT sum(value)
FROM DBA_HIST_SYSSTAT b
WHERE b.SNAP_ID = &beg_snap
AND b.DBID = &L_DBID
AND b.INSTANCE_NUMBER = &L_INST_NUM
AND b.STAT_NAME in ('physical writes'))) / ((SELECT sum(value)
FROM DBA_HIST_SYSSTAT e
WHERE e.SNAP_ID = &end_snap
AND e.DBID = &L_DBID
AND e.INSTANCE_NUMBER = &L_INST_NUM
AND e.STAT_NAME in ('user rollbacks', 'user commits')) -
(SELECT sum(value)
FROM DBA_HIST_SYSSTAT b
WHERE b.SNAP_ID = &beg_snap
AND b.DBID = &L_DBID
AND b.INSTANCE_NUMBER = &L_INST_NUM
AND b.STAT_NAME in ('user rollbacks', 'user commits'))),
2)
from dual;
#### User calls: per second
select round(((SELECT sum(value)
FROM DBA_HIST_SYSSTAT e
WHERE e.SNAP_ID = &end_snap
AND e.DBID = &L_DBID
AND e.INSTANCE_NUMBER = &L_INST_NUM
AND e.STAT_NAME in ('user calls')) -
(SELECT sum(value)
FROM DBA_HIST_SYSSTAT b
WHERE b.SNAP_ID = &beg_snap
AND b.DBID = &L_DBID
AND b.INSTANCE_NUMBER = &L_INST_NUM
AND b.STAT_NAME in ('user calls'))) / (SELECT EXTRACT(DAY FROM
E.END_INTERVAL_TIME - B.END_INTERVAL_TIME) *
86400 + EXTRACT(HOUR FROM E.END_INTERVAL_TIME -
B.END_INTERVAL_TIME) * 3600 +
EXTRACT(MINUTE FROM
E.END_INTERVAL_TIME - B.END_INTERVAL_TIME) * 60 +
EXTRACT(SECOND FROM
E.END_INTERVAL_TIME - B.END_INTERVAL_TIME) FROM DBA_HIST_SNAPSHOT B, DBA_HIST_SNAPSHOT E
where e.snap_id = &end_snap
and b.snap_id = &beg_snap AND b.DBID = &L_DBID
AND b.INSTANCE_NUMBER = &L_INST_NUM AND e.DBID = &L_DBID
AND e.INSTANCE_NUMBER = &L_INST_NUM),
2)
from dual;
#### User calls: per transaction
select round(((SELECT sum(value)
FROM DBA_HIST_SYSSTAT e
WHERE e.SNAP_ID = &end_snap
AND e.DBID = &L_DBID
AND e.INSTANCE_NUMBER = &L_INST_NUM
AND e.STAT_NAME in ('user calls')) -
(SELECT sum(value)
FROM DBA_HIST_SYSSTAT b
WHERE b.SNAP_ID = &beg_snap
AND b.DBID = &L_DBID
AND b.INSTANCE_NUMBER = &L_INST_NUM
AND b.STAT_NAME in ('user calls'))) / ((SELECT sum(value)
FROM DBA_HIST_SYSSTAT e
WHERE e.SNAP_ID = &end_snap
AND e.DBID = &L_DBID
AND e.INSTANCE_NUMBER = &L_INST_NUM
AND e.STAT_NAME in ('user rollbacks', 'user commits')) -
(SELECT sum(value)
FROM DBA_HIST_SYSSTAT b
WHERE b.SNAP_ID = &beg_snap
AND b.DBID = &L_DBID
AND b.INSTANCE_NUMBER = &L_INST_NUM
AND b.STAT_NAME in ('user rollbacks', 'user commits'))),
2)
from dual;
#### Parses: per second
select round(((SELECT sum(value)
FROM DBA_HIST_SYSSTAT e
WHERE e.SNAP_ID = &end_snap
AND e.DBID = &L_DBID
AND e.INSTANCE_NUMBER = &L_INST_NUM
AND e.STAT_NAME in ('parse count (total)')) -
(SELECT sum(value)
FROM DBA_HIST_SYSSTAT b
WHERE b.SNAP_ID = &beg_snap
AND b.DBID = &L_DBID
AND b.INSTANCE_NUMBER = &L_INST_NUM
AND b.STAT_NAME in ('parse count (total)'))) / (SELECT EXTRACT(DAY FROM
E.END_INTERVAL_TIME - B.END_INTERVAL_TIME) *
86400 + EXTRACT(HOUR FROM E.END_INTERVAL_TIME -
B.END_INTERVAL_TIME) * 3600 +
EXTRACT(MINUTE FROM
E.END_INTERVAL_TIME - B.END_INTERVAL_TIME) * 60 +
EXTRACT(SECOND FROM
E.END_INTERVAL_TIME - B.END_INTERVAL_TIME) FROM DBA_HIST_SNAPSHOT B, DBA_HIST_SNAPSHOT E
where e.snap_id = &end_snap
and b.snap_id = &beg_snap AND b.DBID = &L_DBID
AND b.INSTANCE_NUMBER = &L_INST_NUM AND e.DBID = &L_DBID
AND e.INSTANCE_NUMBER = &L_INST_NUM),
2)
from dual;
#### Parses: per transaction
select round(((SELECT sum(value)
FROM DBA_HIST_SYSSTAT e
WHERE e.SNAP_ID = &end_snap
AND e.DBID = &L_DBID
AND e.INSTANCE_NUMBER = &L_INST_NUM
AND e.STAT_NAME in ('parse count (total)')) -
(SELECT sum(value)
FROM DBA_HIST_SYSSTAT b
WHERE b.SNAP_ID = &beg_snap
AND b.DBID = &L_DBID
AND b.INSTANCE_NUMBER = &L_INST_NUM
AND b.STAT_NAME in ('parse count (total)'))) / ((SELECT sum(value)
FROM DBA_HIST_SYSSTAT e
WHERE e.SNAP_ID = &end_snap
AND e.DBID = &L_DBID
AND e.INSTANCE_NUMBER = &L_INST_NUM
AND e.STAT_NAME in ('user rollbacks', 'user commits')) -
(SELECT sum(value)
FROM DBA_HIST_SYSSTAT b
WHERE b.SNAP_ID = &beg_snap
AND b.DBID = &L_DBID
AND b.INSTANCE_NUMBER = &L_INST_NUM
AND b.STAT_NAME in ('user rollbacks', 'user commits'))),
2)
from dual;
#### Hard parses: per second
select round(((SELECT sum(value)
FROM DBA_HIST_SYSSTAT e
WHERE e.SNAP_ID = &end_snap
AND e.DBID = &L_DBID
AND e.INSTANCE_NUMBER = &L_INST_NUM
AND e.STAT_NAME in ('parse count (hard)')) -
(SELECT sum(value)
FROM DBA_HIST_SYSSTAT b
WHERE b.SNAP_ID = &beg_snap
AND b.DBID = &L_DBID
AND b.INSTANCE_NUMBER = &L_INST_NUM
AND b.STAT_NAME in ('parse count (hard)'))) /
(SELECT EXTRACT(DAY FROM
E.END_INTERVAL_TIME - B.END_INTERVAL_TIME) *
86400 + EXTRACT(HOUR FROM E.END_INTERVAL_TIME -
B.END_INTERVAL_TIME) * 3600 +
EXTRACT(MINUTE FROM
E.END_INTERVAL_TIME - B.END_INTERVAL_TIME) * 60 +
EXTRACT(SECOND FROM
E.END_INTERVAL_TIME - B.END_INTERVAL_TIME) FROM DBA_HIST_SNAPSHOT B, DBA_HIST_SNAPSHOT E
where e.snap_id = &end_snap
and b.snap_id = &beg_snap AND b.DBID = &L_DBID
AND b.INSTANCE_NUMBER = &L_INST_NUM AND e.DBID = &L_DBID
AND e.INSTANCE_NUMBER = &L_INST_NUM),
2)
from dual;
#### Hard Parses: per transaction
select round(((SELECT sum(value)
FROM DBA_HIST_SYSSTAT e
WHERE e.SNAP_ID = &end_snap
AND e.DBID = &L_DBID
AND e.INSTANCE_NUMBER = &L_INST_NUM
AND e.STAT_NAME in ('parse count (hard)')) -
(SELECT sum(value)
FROM DBA_HIST_SYSSTAT b
WHERE b.SNAP_ID = &beg_snap
AND b.DBID = &L_DBID
AND b.INSTANCE_NUMBER = &L_INST_NUM
AND b.STAT_NAME in ('parse count (hard)'))) / ((SELECT sum(value)
FROM DBA_HIST_SYSSTAT e
WHERE e.SNAP_ID = &end_snap
AND e.DBID = &L_DBID
AND e.INSTANCE_NUMBER = &L_INST_NUM
AND e.STAT_NAME in ('user rollbacks', 'user commits')) -
(SELECT sum(value)
FROM DBA_HIST_SYSSTAT b
WHERE b.SNAP_ID = &beg_snap
AND b.DBID = &L_DBID
AND b.INSTANCE_NUMBER = &L_INST_NUM
AND b.STAT_NAME in ('user rollbacks', 'user commits'))),
2)
from dual;
#### Sorts: per second
select round(((SELECT sum(value)
FROM DBA_HIST_SYSSTAT e
WHERE e.SNAP_ID = &end_snap
AND e.DBID = &L_DBID
AND e.INSTANCE_NUMBER = &L_INST_NUM
AND e.STAT_NAME in ('sorts (disk)')) -
(SELECT sum(value)
FROM DBA_HIST_SYSSTAT b
WHERE b.SNAP_ID = &beg_snap
AND b.DBID = &L_DBID
AND b.INSTANCE_NUMBER = &L_INST_NUM
AND b.STAT_NAME in ('sorts (disk)'))) / (SELECT EXTRACT(DAY FROM
E.END_INTERVAL_TIME - B.END_INTERVAL_TIME) *
86400 + EXTRACT(HOUR FROM E.END_INTERVAL_TIME -
B.END_INTERVAL_TIME) * 3600 +
EXTRACT(MINUTE FROM
E.END_INTERVAL_TIME - B.END_INTERVAL_TIME) * 60 +
EXTRACT(SECOND FROM
E.END_INTERVAL_TIME - B.END_INTERVAL_TIME) FROM DBA_HIST_SNAPSHOT B, DBA_HIST_SNAPSHOT E
where e.snap_id = &end_snap
and b.snap_id = &beg_snap AND b.DBID = &L_DBID
AND b.INSTANCE_NUMBER = &L_INST_NUM AND e.DBID = &L_DBID
AND e.INSTANCE_NUMBER = &L_INST_NUM),
2)
from dual;
#### Sorts: per transaction
select round(((SELECT sum(value)
FROM DBA_HIST_SYSSTAT e
WHERE e.SNAP_ID = &end_snap
AND e.DBID = &L_DBID
AND e.INSTANCE_NUMBER = &L_INST_NUM
AND e.STAT_NAME in ('sorts (disk)')) -
(SELECT sum(value)
FROM DBA_HIST_SYSSTAT b
WHERE b.SNAP_ID = &beg_snap
AND b.DBID = &L_DBID
AND b.INSTANCE_NUMBER = &L_INST_NUM
AND b.STAT_NAME in ('sorts (disk)'))) / ((SELECT sum(value)
FROM DBA_HIST_SYSSTAT e
WHERE e.SNAP_ID = &end_snap
AND e.DBID = &L_DBID
AND e.INSTANCE_NUMBER = &L_INST_NUM
AND e.STAT_NAME in ('user rollbacks', 'user commits')) -
(SELECT sum(value)
FROM DBA_HIST_SYSSTAT b
WHERE b.SNAP_ID = &beg_snap
AND b.DBID = &L_DBID
AND b.INSTANCE_NUMBER = &L_INST_NUM
AND b.STAT_NAME in ('user rollbacks', 'user commits'))),
2)
from dual;
#### Logons: per second
select round(((SELECT sum(value)
FROM DBA_HIST_SYSSTAT e
WHERE e.SNAP_ID = &end_snap
AND e.DBID = &L_DBID
AND e.INSTANCE_NUMBER = &L_INST_NUM
AND e.STAT_NAME in ('logons cumulative')) -
(SELECT sum(value)
FROM DBA_HIST_SYSSTAT b
WHERE b.SNAP_ID = &beg_snap
AND b.DBID = &L_DBID
AND b.INSTANCE_NUMBER = &L_INST_NUM
AND b.STAT_NAME in ('logons cumulative'))) / (SELECT EXTRACT(DAY FROM
E.END_INTERVAL_TIME - B.END_INTERVAL_TIME) *
86400 + EXTRACT(HOUR FROM E.END_INTERVAL_TIME -
B.END_INTERVAL_TIME) * 3600 +
EXTRACT(MINUTE FROM
E.END_INTERVAL_TIME - B.END_INTERVAL_TIME) * 60 +
EXTRACT(SECOND FROM
E.END_INTERVAL_TIME - B.END_INTERVAL_TIME) FROM DBA_HIST_SNAPSHOT B, DBA_HIST_SNAPSHOT E
where e.snap_id = &end_snap
and b.snap_id = &beg_snap AND b.DBID = &L_DBID
AND b.INSTANCE_NUMBER = &L_INST_NUM AND e.DBID = &L_DBID
AND e.INSTANCE_NUMBER = &L_INST_NUM),
2)
from dual;
#### Logons: per transaction
select round(((SELECT sum(value)
FROM DBA_HIST_SYSSTAT e
WHERE e.SNAP_ID = &end_snap
AND e.DBID = &L_DBID
AND e.INSTANCE_NUMBER = &L_INST_NUM
AND e.STAT_NAME in ('logons cumulative')) -
(SELECT sum(value)
FROM DBA_HIST_SYSSTAT b
WHERE b.SNAP_ID = &beg_snap
AND b.DBID = &L_DBID
AND b.INSTANCE_NUMBER = &L_INST_NUM
AND b.STAT_NAME in ('logons cumulative'))) / ((SELECT sum(value)
FROM DBA_HIST_SYSSTAT e
WHERE e.SNAP_ID = &end_snap
AND e.DBID = &L_DBID
AND e.INSTANCE_NUMBER = &L_INST_NUM
AND e.STAT_NAME in ('user rollbacks', 'user commits')) -
(SELECT sum(value)
FROM DBA_HIST_SYSSTAT b
WHERE b.SNAP_ID = &beg_snap
AND b.DBID = &L_DBID
AND b.INSTANCE_NUMBER = &L_INST_NUM
AND b.STAT_NAME in ('user rollbacks', 'user commits'))),
2)
from dual;
#### Executes: per second
select round(((SELECT sum(value)
FROM DBA_HIST_SYSSTAT e
WHERE e.SNAP_ID = &end_snap
AND e.DBID = &L_DBID
AND e.INSTANCE_NUMBER = &L_INST_NUM
AND e.STAT_NAME in ('execute count')) -
(SELECT sum(value)
FROM DBA_HIST_SYSSTAT b
WHERE b.SNAP_ID = &beg_snap
AND b.DBID = &L_DBID
AND b.INSTANCE_NUMBER = &L_INST_NUM
AND b.STAT_NAME in ('execute count'))) / (SELECT EXTRACT(DAY FROM
E.END_INTERVAL_TIME - B.END_INTERVAL_TIME) *
86400 + EXTRACT(HOUR FROM E.END_INTERVAL_TIME -
B.END_INTERVAL_TIME) * 3600 +
EXTRACT(MINUTE FROM
E.END_INTERVAL_TIME - B.END_INTERVAL_TIME) * 60 +
EXTRACT(SECOND FROM
E.END_INTERVAL_TIME - B.END_INTERVAL_TIME) FROM DBA_HIST_SNAPSHOT B, DBA_HIST_SNAPSHOT E
where e.snap_id = &end_snap
and b.snap_id = &beg_snap AND b.DBID = &L_DBID
AND b.INSTANCE_NUMBER = &L_INST_NUM AND e.DBID = &L_DBID
AND e.INSTANCE_NUMBER = &L_INST_NUM),
2)
from dual;
#### Executes: per transaction
select round(((SELECT sum(value)
FROM DBA_HIST_SYSSTAT e
WHERE e.SNAP_ID = &end_snap
AND e.DBID = &L_DBID
AND e.INSTANCE_NUMBER = &L_INST_NUM
AND e.STAT_NAME in ('execute count')) -
(SELECT sum(value)
FROM DBA_HIST_SYSSTAT b
WHERE b.SNAP_ID = &beg_snap
AND b.DBID = &L_DBID
AND b.INSTANCE_NUMBER = &L_INST_NUM
AND b.STAT_NAME in ('execute count'))) / ((SELECT sum(value)
FROM DBA_HIST_SYSSTAT e
WHERE e.SNAP_ID = &end_snap
AND e.DBID = &L_DBID
AND e.INSTANCE_NUMBER = &L_INST_NUM
AND e.STAT_NAME in ('user rollbacks', 'user commits')) -
(SELECT sum(value)
FROM DBA_HIST_SYSSTAT b
WHERE b.SNAP_ID = &beg_snap
AND b.DBID = &L_DBID
AND b.INSTANCE_NUMBER = &L_INST_NUM
AND b.STAT_NAME in ('user rollbacks', 'user commits'))),
2)
from dual;
####Transactions: per second
select round(((SELECT sum(value)
FROM DBA_HIST_SYSSTAT e
WHERE e.SNAP_ID = &end_snap
AND e.DBID = &L_DBID
AND e.INSTANCE_NUMBER = &L_INST_NUM
AND e.STAT_NAME in ('user rollbacks', 'user commits')) -
(SELECT sum(value)
FROM DBA_HIST_SYSSTAT b
WHERE b.SNAP_ID = &beg_snap
AND b.DBID = &L_DBID
AND b.INSTANCE_NUMBER = &L_INST_NUM
AND b.STAT_NAME in ('user rollbacks', 'user commits'))) /
(SELECT EXTRACT(DAY FROM
E.END_INTERVAL_TIME - B.END_INTERVAL_TIME) *
86400 + EXTRACT(HOUR FROM E.END_INTERVAL_TIME -
B.END_INTERVAL_TIME) * 3600 +
EXTRACT(MINUTE FROM
E.END_INTERVAL_TIME - B.END_INTERVAL_TIME) * 60 +
EXTRACT(SECOND FROM
E.END_INTERVAL_TIME - B.END_INTERVAL_TIME) FROM DBA_HIST_SNAPSHOT B, DBA_HIST_SNAPSHOT E
where e.snap_id = &end_snap
and b.snap_id = &beg_snap AND b.DBID = &L_DBID
AND b.INSTANCE_NUMBER = &L_INST_NUM AND e.DBID = &L_DBID
AND e.INSTANCE_NUMBER = &L_INST_NUM),
2)
from dual;
####% 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
AND
AND
(SELECT sum(value)
FROM
e.DBID = &L_DBID
e.INSTANCE_NUMBER = &L_INST_NUM
e.STAT_NAME = 'parse count (total)') -
DBA_HIST_SYSSTAT b
WHERE b.SNAP_ID = &beg_snap
AND
AND
AND
((SELECT sum(value)
FROM
b.DBID = &L_DBID
b.INSTANCE_NUMBER = &L_INST_NUM
b.STAT_NAME = 'parse count (total)')) /
DBA_HIST_SYSSTAT e
WHERE e.SNAP_ID = &end_snap
AND
AND
AND
(SELECT sum(value)
FROM
e.DBID = &L_DBID
e.INSTANCE_NUMBER = &L_INST_NUM
e.STAT_NAME = 'execute count') -
DBA_HIST_SYSSTAT b
WHERE b.SNAP_ID = &beg_snap
AND b.DBID = &L_DBID
AND b.INSTANCE_NUMBER = &L_INST_NUM
AND b.STAT_NAME = 'execute count'))),
2)
from dual;
####Latch Hit %:
SELECT round(100 * (1 - (SUM(e.MISSES) - sum(b.MISSES)) /
(SUM(e.GETS) - sum(b.GETS))),
2)
FROM DBA_HIST_LATCH b, DBA_HIST_LATCH e WHERE e.SNAP_ID = &end_SNAP
AND e.DBID = &L_DBID
AND e.INSTANCE_NUMBER = &L_INST_NUM
and b.SNAP_ID = &beg_SNAP
AND b.DBID = &L_DBID
AND b.INSTANCE_NUMBER = &L_INST_NUM;
####Parse CPU to Parse Elapsd %:
select round(100 * ((SELECT sum(value)
FROM DBA_HIST_SYSSTAT e
WHERE e.SNAP_ID = &end_snap
AND e.DBID = &L_DBID
AND e.INSTANCE_NUMBER = &L_INST_NUM
AND e.STAT_NAME = 'parse time cpu') -
(SELECT sum(value)
FROM DBA_HIST_SYSSTAT b
WHERE b.SNAP_ID = &beg_snap
AND b.DBID = &L_DBID
AND b.INSTANCE_NUMBER = &L_INST_NUM
AND b.STAT_NAME = 'parse time cpu')) /
((SELECT sum(value)
FROM DBA_HIST_SYSSTAT e
WHERE e.SNAP_ID = &end_snap
AND e.DBID = &L_DBID
AND e.INSTANCE_NUMBER = &L_INST_NUM
AND e.STAT_NAME = 'parse time elapsed') -
(SELECT sum(value)
FROM DBA_HIST_SYSSTAT b
WHERE b.SNAP_ID = &beg_snap
AND b.DBID = &L_DBID
AND b.INSTANCE_NUMBER = &L_INST_NUM
AND b.STAT_NAME = 'parse time elapsed')),
2)
from dual;
####% Non-Parse CPU:
select round(100 *
(1 - ((SELECT sum(value)
FROM DBA_HIST_SYSSTAT e
WHERE e.SNAP_ID = &end_snap
AND e.DBID = &L_DBID
AND e.INSTANCE_NUMBER = &L_INST_NUM
AND e.STAT_NAME = 'parse time cpu') -
(SELECT sum(value)
FROM DBA_HIST_SYSSTAT b
WHERE b.SNAP_ID = &beg_snap
AND b.DBID = &L_DBID
AND b.INSTANCE_NUMBER = &L_INST_NUM
AND b.STAT_NAME = 'parse time cpu')) / (((SELECT sum(value)
FROM DBA_HIST_SYS_TIME_MODEL e WHERE e.SNAP_ID = &end_snap
AND e.DBID = &L_DBID
AND e.INSTANCE_NUMBER = &L_INST_NUM
AND e.STAT_NAME = 'DB CPU') -
(SELECT sum(value)
FROM DBA_HIST_SYS_TIME_MODEL b WHERE b.SNAP_ID = &beg_snap
AND b.DBID = &L_DBID
AND b.INSTANCE_NUMBER = &L_INST_NUM
AND b.STAT_NAME = 'DB CPU')) / 10000)),
2)
from dual;
#### Memory Usage %:begin
select round(100 *
(1 - (SELECT bytes
FROM DBA_HIST_SGASTAT
WHERE SNAP_ID = &beg_snap
AND DBID = &L_DBID
AND INSTANCE_NUMBER = &L_INST_NUM AND NAME = 'free memory'
and pool IN ('shared pool', 'all pools')) /
(SELECT sum(value)
FROM DBA_HIST_PARAMETER WHERE SNAP_ID = &beg_snap
AND DBID = &L_DBID
AND INSTANCE_NUMBER = &L_INST_NUM
AND PARAMETER_NAME = '__shared_pool_size')),
2)
from dual;
#### Memory Usage %:end
select round(100 *
(1 - (SELECT bytes
FROM DBA_HIST_SGASTAT
WHERE SNAP_ID = &end_snap
AND DBID = &L_DBID
AND INSTANCE_NUMBER = &L_INST_NUM AND NAME = 'free memory'
and pool IN ('shared pool', 'all pools')) / (SELECT sum(value)
FROM DBA_HIST_PARAMETER WHERE SNAP_ID = &end_snap
AND DBID = &L_DBID
AND INSTANCE_NUMBER = &L_INST_NUM
AND PARAMETER_NAME = '__shared_pool_size')),
2)
from dual;
#### % SQL with executions>1:begin
SELECT DECODE(B.TOTAL_SQL, 0, 0, 100 * (1 - B.SINGLE_USE_SQL / B.TOTAL_SQL))
FROM DBA_HIST_SQL_SUMMARY B
Where SNAP_ID = &begin_snap
AND DBID = &L_DBID
AND INSTANCE_NUMBER = &L_INST_NUM;
#### % SQL with executions>1:end
SELECT DECODE(e.TOTAL_SQL, 0, 0, 100 * (1 - e.SINGLE_USE_SQL / e.TOTAL_SQL))
FROM DBA_HIST_SQL_SUMMARY e
Where SNAP_ID = &end_snap
AND DBID = &L_DBID
AND INSTANCE_NUMBER = &L_INST_NUM;
#### % Memory for SQL w/exec>1:begin
SELECT DECODE(B.TOTAL_SQL_MEM,
0,
0,
100 * (1 - B.SINGLE_USE_SQL_MEM / B.TOTAL_SQL_MEM))
FROM DBA_HIST_SQL_SUMMARY B
Where SNAP_ID = &begin_snap
AND DBID = &L_DBID
AND INSTANCE_NUMBER = &L_INST_NUM;
#### % Memory for SQL w/exec>1:end
SELECT DECODE(e.TOTAL_SQL_MEM,
0,
0,
100 * (1 - e.SINGLE_USE_SQL_MEM / e.TOTAL_SQL_MEM))
FROM DBA_HIST_SQL_SUMMARY e
Where SNAP_ID = &end_snap
AND DBID = &L_DBID
AND INSTANCE_NUMBER = &L_INST_NUM;
#### Top 5 Timed Events:
SELECT EVENT,
WAITS,
TIME,
DECODE(WAITS,
NULL,
TO_NUMBER(NULL),
0,
TO_NUMBER(NULL),
TIME / WAITS * 1000) AVGWT,
PCTWTT,
WAIT_CLASS
FROM (SELECT EVENT, WAITS, TIME, PCTWTT, WAIT_CLASS
FROM (SELECT E.EVENT_NAME EVENT,
E.TOTAL_WAITS - NVL(B.TOTAL_WAITS, 0) WAITS,
(E.TIME_WAITED_MICRO - NVL(B.TIME_WAITED_MICRO, 0)) /
1000000 TIME,
100 *
(E.TIME_WAITED_MICRO - NVL(B.TIME_WAITED_MICRO, 0)) /
((SELECT sum(value)
FROM DBA_HIST_SYS_TIME_MODEL e WHERE e.SNAP_ID = &end_snap
AND e.DBID = &DBID
AND e.INSTANCE_NUMBER = &INST_NUM
AND e.STAT_NAME = 'DB time') -
(SELECT sum(value)
FROM DBA_HIST_SYS_TIME_MODEL b WHERE b.SNAP_ID = &beg_snap
AND b.DBID = &DBID
AND b.INSTANCE_NUMBER = &INST_NUM
AND b.STAT_NAME = 'DB time')) PCTWTT,
E.WAIT_CLASS WAIT_CLASS
FROM DBA_HIST_SYSTEM_EVENT B, DBA_HIST_SYSTEM_EVENT E
WHERE B.SNAP_ID(+) = &beg_snap
AND E.SNAP_ID = &end_snap
AND B.DBID(+) = &DBID
AND E.DBID = &DBID
AND B.INSTANCE_NUMBER(+) = &INST_NUM
AND E.INSTANCE_NUMBER = &INST_NUM
AND B.EVENT_ID(+) = E.EVENT_ID
AND E.TOTAL_WAITS > NVL(B.TOTAL_WAITS, 0) AND E.WAIT_CLASS != 'Idle'
UNION ALL
SELECT 'CPU time' EVENT,
TO_NUMBER(NULL) WAITS,
((SELECT sum(value)
FROM DBA_HIST_SYS_TIME_MODEL e WHERE e.SNAP_ID = &end_snap
AND e.DBID = &DBID
AND e.INSTANCE_NUMBER = &INST_NUM
AND e.STAT_NAME = 'DB CPU') -
(SELECT sum(value)
FROM DBA_HIST_SYS_TIME_MODEL b WHERE b.SNAP_ID = &beg_snap
AND b.DBID = &DBID
AND b.INSTANCE_NUMBER = &INST_NUM
AND b.STAT_NAME = 'DB CPU')) / 1000000 TIME,
100 * ((SELECT sum(value)
FROM DBA_HIST_SYS_TIME_MODEL e WHERE e.SNAP_ID = &end_snap
AND e.DBID = &DBID
AND e.INSTANCE_NUMBER = &INST_NUM
AND e.STAT_NAME = 'DB CPU') -
(SELECT sum(value)
FROM DBA_HIST_SYS_TIME_MODEL b WHERE b.SNAP_ID = &beg_snap
AND b.DBID = &DBID
AND b.INSTANCE_NUMBER = &INST_NUM
AND b.STAT_NAME = 'DB CPU')) /
((SELECT sum(value)
FROM DBA_HIST_SYS_TIME_MODEL e WHERE e.SNAP_ID = &end_snap
AND e.DBID = &L_DBID
AND e.INSTANCE_NUMBER = &INST_NUM
AND e.STAT_NAME = 'DB time') -
(SELECT sum(value)
FROM DBA_HIST_SYS_TIME_MODEL b WHERE b.SNAP_ID = &beg_snap
AND b.DBID = &DBID
AND b.INSTANCE_NUMBER = &INST_NUM
AND b.STAT_NAME = 'DB time')) PCTWTT, NULL WAIT_CLASS
from dual
WHERE ((SELECT sum(value)
FROM DBA_HIST_SYS_TIME_MODEL e
WHERE e.SNAP_ID = &end_snap
AND e.DBID = &DBID
AND e.INSTANCE_NUMBER = &INST_NUM
AND e.STAT_NAME = 'DB CPU') -
(SELECT sum(value)
FROM DBA_HIST_SYS_TIME_MODEL b WHERE b.SNAP_ID = &beg_snap
AND b.DBID = &DBID
AND b.INSTANCE_NUMBER = &INST_NUM
AND b.STAT_NAME = 'DB CPU')) > 0)
ORDER BY TIME DESC, WAITS DESC)
WHERE ROWNUM <= 5;
#### Global Cache blocks received:per second
select round(((SELECT e.VALUE - b.value
FROM DBA_HIST_SYSSTAT b, DBA_HIST_SYSSTAT e
WHERE B.SNAP_ID = &beg_snap
AND E.SNAP_ID = &end_snap AND B.DBID = &DBID
AND E.DBID = &DBID
AND B.INSTANCE_NUMBER = &INST_NUM
AND E.INSTANCE_NUMBER = &INST_NUM
and e.STAT_NAME = 'gc cr blocks received'
and b.stat_name = 'gc cr blocks received') +
(SELECT e.VALUE - b.value
FROM DBA_HIST_SYSSTAT b, DBA_HIST_SYSSTAT e WHERE B.SNAP_ID = &beg_snap
AND E.SNAP_ID = &end_snap AND B.DBID = &DBID
AND E.DBID = &DBID
AND B.INSTANCE_NUMBER = &INST_NUM
AND E.INSTANCE_NUMBER = &INST_NUM
and e.STAT_NAME = 'gc current blocks received'
and b.STAT_NAME = 'gc current blocks received')) /
(SELECT EXTRACT(DAY FROM
E.END_INTERVAL_TIME - B.END_INTERVAL_TIME) * 86400 + EXTRACT(HOUR FROM E.END_INTERVAL_TIME -
B.END_INTERVAL_TIME) * 3600 +
EXTRACT(MINUTE FROM
E.END_INTERVAL_TIME - B.END_INTERVAL_TIME) * 60 +
EXTRACT(SECOND FROM
E.END_INTERVAL_TIME - B.END_INTERVAL_TIME) FROM DBA_HIST_SNAPSHOT B, DBA_HIST_SNAPSHOT E
WHERE B.SNAP_ID = &beg_snap
AND E.SNAP_ID = &end_snap
AND B.DBID = &DBID
AND E.DBID = &DBID
AND B.INSTANCE_NUMBER = &INST_NUM
AND E.INSTANCE_NUMBER = &INST_NUM
AND B.STARTUP_TIME = E.STARTUP_TIME
AND B.END_INTERVAL_TIME < E.END_INTERVAL_TIME),
2)
from dual;
#### Global Cache blocks received:per transaction
select round(((SELECT e.VALUE - b.value
FROM DBA_HIST_SYSSTAT b, DBA_HIST_SYSSTAT e WHERE B.SNAP_ID = &beg_snap
AND E.SNAP_ID = &end_snap AND B.DBID = &DBID
AND E.DBID = &DBID
AND B.INSTANCE_NUMBER = &INST_NUM
AND E.INSTANCE_NUMBER = &INST_NUM
and e.STAT_NAME = 'gc cr blocks received'
and b.stat_name = 'gc cr blocks received') +
(SELECT e.VALUE - b.value
FROM DBA_HIST_SYSSTAT b, DBA_HIST_SYSSTAT e WHERE B.SNAP_ID = &beg_snap
AND E.SNAP_ID = &end_snap AND B.DBID = &DBID
AND E.DBID = &DBID
AND B.INSTANCE_NUMBER = &INST_NUM
AND E.INSTANCE_NUMBER = &INST_NUM
and e.STAT_NAME = 'gc current blocks received'
and b.STAT_NAME = 'gc current blocks received')) /
(SELECT sum(e.value) - sum(b.value)
FROM DBA_HIST_SYSSTAT B, DBA_HIST_SYSSTAT E WHERE B.SNAP_ID = &beg_snap
AND E.SNAP_ID = &end_snap AND B.DBID = &DBID
AND E.DBID = &DBID
AND B.INSTANCE_NUMBER = &INST_NUM
AND E.INSTANCE_NUMBER = &INST_NUM
and e.STAT_NAME in ('user rollbacks', 'user commits')
and b.STAT_NAME in ('user rollbacks', 'user commits')),
2)
from dual;
#### Global Cache blocks served:per second
select round(((SELECT e.VALUE - b.value
FROM DBA_HIST_SYSSTAT b, DBA_HIST_SYSSTAT e
WHERE B.SNAP_ID = &beg_snap
AND E.SNAP_ID = &end_snap AND B.DBID = &DBID
AND E.DBID = &DBID
AND B.INSTANCE_NUMBER = &INST_NUM
AND E.INSTANCE_NUMBER = &INST_NUM
and e.STAT_NAME = 'gc cr blocks served'
and b.stat_name = 'gc cr blocks served') +
(SELECT e.VALUE - b.value
FROM DBA_HIST_SYSSTAT b, DBA_HIST_SYSSTAT e
WHERE B.SNAP_ID = &beg_snap
AND E.SNAP_ID = &end_snap AND B.DBID = &DBID
AND E.DBID = &DBID
AND B.INSTANCE_NUMBER = &INST_NUM
AND E.INSTANCE_NUMBER = &INST_NUM
and e.STAT_NAME = 'gc current blocks served'
and b.STAT_NAME = 'gc current blocks served')) /
(SELECT EXTRACT(DAY FROM
E.END_INTERVAL_TIME - B.END_INTERVAL_TIME) *
86400 + EXTRACT(HOUR FROM E.END_INTERVAL_TIME -
B.END_INTERVAL_TIME) * 3600 +
EXTRACT(MINUTE FROM
E.END_INTERVAL_TIME - B.END_INTERVAL_TIME) * 60 +
EXTRACT(SECOND FROM
E.END_INTERVAL_TIME - B.END_INTERVAL_TIME) FROM DBA_HIST_SNAPSHOT B, DBA_HIST_SNAPSHOT E
WHERE B.SNAP_ID = &beg_snap
AND E.SNAP_ID = &end_snap AND B.DBID = &DBID
AND E.DBID = &DBID
AND B.INSTANCE_NUMBER = &INST_NUM
AND E.INSTANCE_NUMBER = &INST_NUM
AND B.STARTUP_TIME = E.STARTUP_TIME
AND B.END_INTERVAL_TIME < E.END_INTERVAL_TIME),
2)
from dual;
#### Global Cache blocks served:per transaction
select round(((SELECT e.VALUE - b.value
FROM DBA_HIST_SYSSTAT b, DBA_HIST_SYSSTAT e WHERE B.SNAP_ID = &beg_snap
AND E.SNAP_ID = &end_snap AND B.DBID = &DBID
AND E.DBID = &DBID
AND B.INSTANCE_NUMBER = &INST_NUM
AND E.INSTANCE_NUMBER = &INST_NUM
and e.STAT_NAME = 'gc cr blocks served'
and b.stat_name = 'gc cr blocks served') + (SELECT e.VALUE - b.value
FROM DBA_HIST_SYSSTAT b, DBA_HIST_SYSSTAT e WHERE B.SNAP_ID = &beg_snap
AND E.SNAP_ID = &end_snap AND B.DBID = &DBID
AND E.DBID = &DBID
AND B.INSTANCE_NUMBER = &INST_NUM
AND E.INSTANCE_NUMBER = &INST_NUM
and e.STAT_NAME = 'gc current blocks served'
and b.STAT_NAME = 'gc current blocks served')) /
(SELECT sum(e.value) - sum(b.value)
FROM DBA_HIST_SYSSTAT B, DBA_HIST_SYSSTAT E WHERE B.SNAP_ID = &beg_snap
AND E.SNAP_ID = &end_snap AND B.DBID = &DBID
AND E.DBID = &DBID
AND B.INSTANCE_NUMBER = &INST_NUM
AND E.INSTANCE_NUMBER = &INST_NUM
and e.STAT_NAME in ('user rollbacks', 'user commits')
and b.STAT_NAME in ('user rollbacks', 'user commits')),
2)
from dual;
####GCS/GES messages received::per second
select round(((SELECT e.VALUE - b.value
FROM DBA_HIST_SYSSTAT b, DBA_HIST_SYSSTAT e WHERE B.SNAP_ID = &beg_snap
AND E.SNAP_ID = &end_snap AND B.DBID = &DBID
AND E.DBID = &DBID
AND B.INSTANCE_NUMBER = &INST_NUM
AND E.INSTANCE_NUMBER = &INST_NUM
and e.STAT_NAME = 'gcs msgs received'
and b.stat_name = 'gcs msgs received') +
(SELECT e.VALUE - b.value
FROM DBA_HIST_SYSSTAT b, DBA_HIST_SYSSTAT e WHERE B.SNAP_ID = &beg_snap
AND E.SNAP_ID = &end_snap AND B.DBID = &DBID
AND E.DBID = &DBID
AND B.INSTANCE_NUMBER = &INST_NUM
AND E.INSTANCE_NUMBER = &INST_NUM
and e.STAT_NAME = 'ges msgs received'
and b.STAT_NAME = 'ges msgs received')) / (SELECT EXTRACT(DAY FROM
E.END_INTERVAL_TIME - B.END_INTERVAL_TIME) *
86400 + EXTRACT(HOUR FROM E.END_INTERVAL_TIME -
B.END_INTERVAL_TIME) * 3600 +
EXTRACT(MINUTE FROM
E.END_INTERVAL_TIME - B.END_INTERVAL_TIME) * 60 +
EXTRACT(SECOND FROM
E.END_INTERVAL_TIME - B.END_INTERVAL_TIME) FROM DBA_HIST_SNAPSHOT B, DBA_HIST_SNAPSHOT E
WHERE B.SNAP_ID = &beg_snap
AND E.SNAP_ID = &end_snap AND B.DBID = &DBID
AND E.DBID = &DBID
AND B.INSTANCE_NUMBER = &INST_NUM
AND E.INSTANCE_NUMBER = &INST_NUM
AND B.STARTUP_TIME = E.STARTUP_TIME
AND B.END_INTERVAL_TIME < E.END_INTERVAL_TIME),
2)
from dual;
####GCS/GES messages received::per transaction
select round(((SELECT e.VALUE - b.value
FROM DBA_HIST_SYSSTAT b, DBA_HIST_SYSSTAT e WHERE B.SNAP_ID = &beg_snap
AND E.SNAP_ID = &end_snap AND B.DBID = &DBID
AND E.DBID = &DBID
AND B.INSTANCE_NUMBER = &INST_NUM
AND E.INSTANCE_NUMBER = &INST_NUM
and e.STAT_NAME = 'gcs msgs received'
and b.stat_name = 'gcs msgs received') + (SELECT e.VALUE - b.value
FROM DBA_HIST_SYSSTAT b, DBA_HIST_SYSSTAT e WHERE B.SNAP_ID = &beg_snap
AND E.SNAP_ID = &end_snap AND B.DBID = &DBID
AND E.DBID = &DBID
AND B.INSTANCE_NUMBER = &INST_NUM
AND E.INSTANCE_NUMBER = &INST_NUM
and e.STAT_NAME = 'ges msgs received'
and b.STAT_NAME = 'ges msgs received')) / (SELECT sum(e.value) - sum(b.value)
FROM DBA_HIST_SYSSTAT B, DBA_HIST_SYSSTAT E
WHERE B.SNAP_ID = &beg_snap
AND E.SNAP_ID = &end_snap AND B.DBID = &DBID
AND E.DBID = &DBID
AND B.INSTANCE_NUMBER = &INST_NUM
AND E.INSTANCE_NUMBER = &INST_NUM
and e.STAT_NAME in ('user rollbacks', 'user commits')
and b.STAT_NAME in ('user rollbacks', 'user commits')),
2)
from dual;
####GCS/GES messages sent:per second
select round(((SELECT e.VALUE - b.value
FROM DBA_HIST_SYSSTAT b, DBA_HIST_SYSSTAT e
WHERE B.SNAP_ID = &beg_snap
AND E.SNAP_ID = &end_snap AND B.DBID = &DBID
AND E.DBID = &DBID
AND B.INSTANCE_NUMBER = &INST_NUM
AND E.INSTANCE_NUMBER = &INST_NUM
and e.STAT_NAME = 'gcs messages sent'
and b.stat_name = 'gcs messages sent') + (SELECT e.VALUE - b.value
FROM DBA_HIST_SYSSTAT b, DBA_HIST_SYSSTAT e
WHERE B.SNAP_ID = &beg_snap
AND E.SNAP_ID = &end_snap AND B.DBID = &DBID
AND E.DBID = &DBID
AND B.INSTANCE_NUMBER = &INST_NUM
AND E.INSTANCE_NUMBER = &INST_NUM
and e.STAT_NAME = 'ges messages sent'
and b.stat_name = 'ges messages sent ')) /
(SELECT EXTRACT(DAY FROM
E.END_INTERVAL_TIME - B.END_INTERVAL_TIME) * 86400 + EXTRACT(HOUR FROM E.END_INTERVAL_TIME -
B.END_INTERVAL_TIME) * 3600 +
EXTRACT(MINUTE FROM
E.END_INTERVAL_TIME - B.END_INTERVAL_TIME) * 60 +
EXTRACT(SECOND FROM
E.END_INTERVAL_TIME - B.END_INTERVAL_TIME) FROM DBA_HIST_SNAPSHOT B, DBA_HIST_SNAPSHOT E
WHERE B.SNAP_ID = &beg_snap
AND E.SNAP_ID = &end_snap AND B.DBID = &DBID
AND E.DBID = &DBID
AND B.INSTANCE_NUMBER = &INST_NUM
AND E.INSTANCE_NUMBER = &INST_NUM
AND B.STARTUP_TIME = E.STARTUP_TIME
AND B.END_INTERVAL_TIME < E.END_INTERVAL_TIME),
2)
from dual;
####GCS/GES messages sent:per transaction
select round(((SELECT e.VALUE - b.value
FROM DBA_HIST_SYSSTAT b, DBA_HIST_SYSSTAT e
WHERE B.SNAP_ID = &beg_snap
AND E.SNAP_ID = &end_snap AND B.DBID = &DBID
AND E.DBID = &DBID
AND B.INSTANCE_NUMBER = &INST_NUM
AND E.INSTANCE_NUMBER = &INST_NUM
and e.STAT_NAME = 'gcs messages sent'
and b.stat_name = 'gcs messages sent') + (SELECT e.VALUE - b.value
FROM DBA_HIST_SYSSTAT b, DBA_HIST_SYSSTAT e
WHERE B.SNAP_ID = &beg_snap
AND E.SNAP_ID = &end_snap AND B.DBID = &DBID
AND E.DBID = &DBID
AND B.INSTANCE_NUMBER = &INST_NUM
AND E.INSTANCE_NUMBER = &INST_NUM
and e.STAT_NAME = 'ges messages sent'
and b.stat_name = 'ges messages sent ')) /
(SELECT sum(e.value) - sum(b.value)
FROM DBA_HIST_SYSSTAT B, DBA_HIST_SYSSTAT E WHERE B.SNAP_ID = &beg_snap
AND E.SNAP_ID = &end_snap AND B.DBID = &DBID
AND E.DBID = &DBID
AND B.INSTANCE_NUMBER = &INST_NUM
AND E.INSTANCE_NUMBER = &INST_NUM
and e.STAT_NAME in ('user rollbacks', 'user commits')
and b.STAT_NAME in ('user rollbacks', 'user commits')),
2)
from dual;
####DBWR Fusion writes:per second
select round((SELECT e.VALUE - b.value
FROM DBA_HIST_SYSSTAT b, DBA_HIST_SYSSTAT e
WHERE B.SNAP_ID = &beg_snap
AND E.SNAP_ID = &end_snap
AND B.DBID = &DBID
AND E.DBID = &DBID
AND B.INSTANCE_NUMBER = &INST_NUM
AND E.INSTANCE_NUMBER = &INST_NUM
and e.STAT_NAME = 'DBWR fusion writes'
and b.stat_name = 'DBWR fusion writes') /
(SELECT EXTRACT(DAY FROM
E.END_INTERVAL_TIME - B.END_INTERVAL_TIME) *
86400 + EXTRACT(HOUR FROM E.END_INTERVAL_TIME -
B.END_INTERVAL_TIME) * 3600 +
EXTRACT(MINUTE FROM
E.END_INTERVAL_TIME - B.END_INTERVAL_TIME) * 60 +
EXTRACT(SECOND FROM
E.END_INTERVAL_TIME - B.END_INTERVAL_TIME)
FROM DBA_HIST_SNAPSHOT B, DBA_HIST_SNAPSHOT E
WHERE B.SNAP_ID = &beg_snap
AND E.SNAP_ID = &end_snap
AND B.DBID = &DBID
AND E.DBID = &DBID
AND B.INSTANCE_NUMBER = &INST_NUM
AND E.INSTANCE_NUMBER = &INST_NUM
AND B.STARTUP_TIME = E.STARTUP_TIME
AND B.END_INTERVAL_TIME < E.END_INTERVAL_TIME),
2)
from dual;
####DBWR Fusion writes:per transaction
select round((SELECT e.VALUE - b.value
FROM DBA_HIST_SYSSTAT b, DBA_HIST_SYSSTAT e WHERE B.SNAP_ID = &beg_snap
AND E.SNAP_ID = &end_snap
AND B.DBID = &DBID
AND E.DBID = &DBID
AND B.INSTANCE_NUMBER = &INST_NUM
AND E.INSTANCE_NUMBER = &INST_NUM
and e.STAT_NAME = 'DBWR fusion writes'
and b.stat_name = 'DBWR fusion writes') / (SELECT sum(e.value) - sum(b.value)
FROM DBA_HIST_SYSSTAT B, DBA_HIST_SYSSTAT E
WHERE B.SNAP_ID = &beg_snap
AND E.SNAP_ID = &end_snap AND B.DBID = &DBID
AND E.DBID = &DBID
AND B.INSTANCE_NUMBER = &INST_NUM
AND E.INSTANCE_NUMBER = &INST_NUM
and e.STAT_NAME in ('user rollbacks', 'user commits')
and b.STAT_NAME in ('user rollbacks', 'user commits')),
2)
from dual;
#### Estd Interconnect traffic (KB)
select round(((SELECT VALUE
FROM DBA_HIST_PARAMETER WHERE SNAP_ID = &beg_snap
AND DBID = &DBID
AND INSTANCE_NUMBER = &INST_NUM
AND PARAMETER_NAME = 'db_block_size') *
(SELECT sum(e.VALUE) - sum(b.value)
FROM DBA_HIST_SYSSTAT b, DBA_HIST_SYSSTAT e WHERE B.SNAP_ID = &beg_snap
AND E.SNAP_ID = &end_snap AND B.DBID = &DBID
AND E.DBID = &DBID
AND B.INSTANCE_NUMBER = &INST_NUM AND E.INSTANCE_NUMBER = &INST_NUM and e.STAT_NAME in
('gc cr blocks received', 'gc current blocks received',
'gc current blocks received',
'gc current blocks served') and b.stat_name in
('gc cr blocks received', 'gc current blocks received',
'gc current blocks received',
'gc current blocks served')) +
200 *
(SELECT sum(e.VALUE) - sum(b.value)
FROM DBA_HIST_SYSSTAT b, DBA_HIST_SYSSTAT e WHERE B.SNAP_ID = &beg_snap
AND E.SNAP_ID = &end_snap AND B.DBID = &DBID
AND E.DBID = &DBID
AND B.INSTANCE_NUMBER = &INST_NUM AND E.INSTANCE_NUMBER = &INST_NUM and e.STAT_NAME in
('gcs msgs received', 'ges msgs received',
'gcs messages sent', 'ges messages sent')
and b.stat_name in
('gcs msgs received', 'ges msgs received',
'gcs messages sent', 'ges messages sent'))) / 1024 /
(SELECT EXTRACT(DAY FROM
E.END_INTERVAL_TIME - B.END_INTERVAL_TIME) *
86400 + EXTRACT(HOUR FROM E.END_INTERVAL_TIME -
B.END_INTERVAL_TIME) * 3600 +
EXTRACT(MINUTE FROM
E.END_INTERVAL_TIME - B.END_INTERVAL_TIME) * 60 +
EXTRACT(SECOND FROM
E.END_INTERVAL_TIME - B.END_INTERVAL_TIME)
FROM DBA_HIST_SNAPSHOT B, DBA_HIST_SNAPSHOT E
WHERE B.SNAP_ID = &beg_snap
AND E.SNAP_ID = &end_snap
AND B.DBID = &DBID
AND E.DBID = &DBID
AND B.INSTANCE_NUMBER = &INST_NUM
AND E.INSTANCE_NUMBER = &INST_NUM
AND B.STARTUP_TIME = E.STARTUP_TIME
AND B.END_INTERVAL_TIME < E.END_INTERVAL_TIME),
2)
from dual;
#### Buffer access - local cache %:
select round(100 *
(1 - ((SELECT sum(e.VALUE) - sum(b.value)
FROM DBA_HIST_SYSSTAT b, DBA_HIST_SYSSTAT e WHERE B.SNAP_ID = &beg_snap
AND E.SNAP_ID = &end_snap AND B.DBID = &DBID
AND E.DBID = &DBID
AND B.INSTANCE_NUMBER = &INST_NUM AND E.INSTANCE_NUMBER = &INST_NUM and e.STAT_NAME in
('physical reads', 'gc cr blocks received', 'gc current blocks received')
and b.STAT_NAME in
('physical reads', 'gc cr blocks received', 'gc current blocks received')) -
(SELECT sum(e.VALUE) - sum(b.value)
FROM DBA_HIST_SYSSTAT b, DBA_HIST_SYSSTAT e WHERE B.SNAP_ID = &beg_snap
AND E.SNAP_ID = &end_snap AND B.DBID = &DBID
AND E.DBID = &DBID
AND B.INSTANCE_NUMBER = &INST_NUM
AND E.INSTANCE_NUMBER = &INST_NUM
and e.STAT_NAME in ('physical reads direct',
'physical reads direct (lob)')
and b.stat_name in ('physical reads direct',
'physical reads direct (lob)'))) /
(SELECT sum(e.VALUE) - sum(b.value)
FROM DBA_HIST_SYSSTAT b, DBA_HIST_SYSSTAT e WHERE B.SNAP_ID = &beg_snap
AND E.SNAP_ID = &end_snap AND B.DBID = &DBID
AND E.DBID = &DBID
AND B.INSTANCE_NUMBER = &INST_NUM
AND E.INSTANCE_NUMBER = &INST_NUM
and e.STAT_NAME = 'session logical reads'
and b.stat_name = 'session logical reads')),
2)
from dual;
#### Buffer access - remote cache %:
select round(100 * ((SELECT sum(e.VALUE) - sum(b.value)
FROM DBA_HIST_SYSSTAT b, DBA_HIST_SYSSTAT e WHERE B.SNAP_ID = &beg_snap
AND E.SNAP_ID = &end_snap AND B.DBID = &DBID
AND E.DBID = &DBID
AND B.INSTANCE_NUMBER = &INST_NUM
AND E.INSTANCE_NUMBER = &INST_NUM
and e.STAT_NAME in ('gc cr blocks received',
'gc current blocks received')
and b.stat_name in ('gc cr blocks received',
'gc current blocks received')) /
(SELECT sum(e.VALUE) - sum(b.value)
FROM DBA_HIST_SYSSTAT b, DBA_HIST_SYSSTAT e WHERE B.SNAP_ID = &beg_snap
AND E.SNAP_ID = &end_snap AND B.DBID = &DBID
AND E.DBID = &DBID
AND B.INSTANCE_NUMBER = &INST_NUM
AND E.INSTANCE_NUMBER = &INST_NUM
and e.STAT_NAME = 'session logical reads'
and b.stat_name = 'session logical reads')),
2)
from dual;
####Buffer access - disk %:
select round(100 * (((SELECT sum(e.VALUE) - sum(b.value)
FROM DBA_HIST_SYSSTAT b, DBA_HIST_SYSSTAT e
WHERE B.SNAP_ID = &beg_snap
AND E.SNAP_ID = &end_snap AND B.DBID = &DBID
AND E.DBID = &DBID
AND B.INSTANCE_NUMBER = &INST_NUM
AND E.INSTANCE_NUMBER = &INST_NUM
and e.STAT_NAME = 'physical reads'
and b.STAT_NAME = 'physical reads') -
(SELECT sum(e.VALUE) - sum(b.value)
FROM DBA_HIST_SYSSTAT b, DBA_HIST_SYSSTAT e
WHERE B.SNAP_ID = &beg_snap
AND E.SNAP_ID = &end_snap AND B.DBID = &DBID
AND E.DBID = &DBID
AND B.INSTANCE_NUMBER = &INST_NUM
AND E.INSTANCE_NUMBER = &INST_NUM
and e.STAT_NAME in ('physical reads direct',
'physical reads direct (lob)')
and b.stat_name in ('physical reads direct',
'physical reads direct (lob)'))) /
(SELECT sum(e.VALUE) - sum(b.value)
FROM DBA_HIST_SYSSTAT b, DBA_HIST_SYSSTAT e WHERE B.SNAP_ID = &beg_snap
AND E.SNAP_ID = &end_snap AND B.DBID = &DBID
AND E.DBID = &DBID
AND B.INSTANCE_NUMBER = &INST_NUM
AND E.INSTANCE_NUMBER = &INST_NUM
and e.STAT_NAME = 'session logical reads'
and b.stat_name = 'session logical reads')),
2)
from dual;
#### Avg global enqueue get time (ms):
select decode(v_2, 0, '', v_1 / v_2)
from (select 10 * (SELECT sum(e.VALUE) - sum(b.value)
FROM DBA_HIST_SYSSTAT b, DBA_HIST_SYSSTAT e WHERE B.SNAP_ID = &beg_snap
AND E.SNAP_ID = &end_snap AND B.DBID = &DBID
AND E.DBID = &DBID
AND B.INSTANCE_NUMBER = &INST_NUM
AND E.INSTANCE_NUMBER = &INST_NUM
and e.STAT_NAME = 'global enqueue get time'
and e.STAT_NAME = 'global enqueue get time') v_1,
(SELECT sum(e.VALUE) - sum(b.value)
FROM DBA_HIST_SYSSTAT b, DBA_HIST_SYSSTAT e WHERE B.SNAP_ID = &beg_snap
AND E.SNAP_ID = &end_snap AND B.DBID = &DBID
AND E.DBID = &DBID
AND B.INSTANCE_NUMBER = &INST_NUM
AND E.INSTANCE_NUMBER = &INST_NUM
and e.STAT_NAME in ('global enqueue gets async',
'global enqueue gets sync')
and b.stat_name in ('global enqueue gets async',
'global enqueue gets sync')) v_2
from dual);
#### Avg global cache cr block receive time (ms):
select decode(v_2, 0, '', v_1 / v_2)
from (select 10 * (SELECT sum(e.VALUE) - sum(b.value)
FROM DBA_HIST_SYSSTAT b, DBA_HIST_SYSSTAT e WHERE B.SNAP_ID = &beg_snap
AND E.SNAP_ID = &end_snap AND B.DBID = &DBID
AND E.DBID = &DBID
AND B.INSTANCE_NUMBER = &INST_NUM
AND E.INSTANCE_NUMBER = &INST_NUM
and e.STAT_NAME = 'gc cr block receive time'
and e.STAT_NAME = 'gc cr block receive time') v_1,
(SELECT sum(e.VALUE) - sum(b.value)
FROM DBA_HIST_SYSSTAT b, DBA_HIST_SYSSTAT e
WHERE B.SNAP_ID = &beg_snap
AND E.SNAP_ID = &end_snap
AND B.DBID = &DBID
AND E.DBID = &DBID
AND B.INSTANCE_NUMBER = &INST_NUM
AND E.INSTANCE_NUMBER = &INST_NUM
and e.STAT_NAME = 'gc cr blocks received'
and b.stat_name = 'gc cr blocks received') v_2
from dual);
#### Avg global cache current block receive time (ms):
select decode(v_2, 0, '', v_1 / v_2)
from (select 10 *
(SELECT sum(e.VALUE) - sum(b.value)
FROM DBA_HIST_SYSSTAT b, DBA_HIST_SYSSTAT e
WHERE B.SNAP_ID = &beg_snap
AND E.SNAP_ID = &end_snap
AND B.DBID = &DBID
AND E.DBID = &DBID
AND B.INSTANCE_NUMBER = &INST_NUM
AND E.INSTANCE_NUMBER = &INST_NUM
and e.STAT_NAME = 'gc current block receive time'
and e.STAT_NAME = 'gc current block receive time') v_1,
(SELECT sum(e.VALUE) - sum(b.value)
FROM DBA_HIST_SYSSTAT b, DBA_HIST_SYSSTAT e
WHERE B.SNAP_ID = &beg_snap
AND E.SNAP_ID = &end_snap
AND B.DBID = &DBID
AND E.DBID = &DBID
AND B.INSTANCE_NUMBER = &INST_NUM
AND E.INSTANCE_NUMBER = &INST_NUM
and e.STAT_NAME = 'gc current blocks received'
and b.stat_name = 'gc current blocks received') v_2
from dual);
#### Avg global cache cr block build time (ms):
select decode(v_2, 0, '', v_1 / v_2)
from (select 10 * (SELECT sum(e.VALUE) - sum(b.value)
FROM DBA_HIST_SYSSTAT b, DBA_HIST_SYSSTAT e WHERE B.SNAP_ID = &beg_snap
AND E.SNAP_ID = &end_snap AND B.DBID = &DBID
AND E.DBID = &DBID
AND B.INSTANCE_NUMBER = &INST_NUM
AND E.INSTANCE_NUMBER = &INST_NUM
and e.STAT_NAME = 'gc cr block build time'
and e.STAT_NAME = 'gc cr block build time') v_1,
(SELECT sum(e.VALUE) - sum(b.value)
FROM DBA_HIST_SYSSTAT b, DBA_HIST_SYSSTAT e WHERE B.SNAP_ID = &beg_snap
AND E.SNAP_ID = &end_snap AND B.DBID = &DBID
AND E.DBID = &DBID
AND B.INSTANCE_NUMBER = &INST_NUM
AND E.INSTANCE_NUMBER = &INST_NUM
and e.STAT_NAME = 'gc cr blocks served'
and b.stat_name = 'gc cr blocks served') v_2 from dual);
#### Avg global cache cr block send time (ms): select decode(v_2, 0, '', v_1 / v_2)
from (select 10 * (SELECT sum(e.VALUE) - sum(b.value)
FROM DBA_HIST_SYSSTAT b, DBA_HIST_SYSSTAT e
WHERE B.SNAP_ID = &beg_snap
AND E.SNAP_ID = &end_snap AND B.DBID = &DBID
AND E.DBID = &DBID
AND B.INSTANCE_NUMBER = &INST_NUM
AND E.INSTANCE_NUMBER = &INST_NUM
and e.STAT_NAME = 'gc cr block send time'
and e.STAT_NAME = 'gc cr block send time') v_1,
(SELECT sum(e.VALUE) - sum(b.value)
FROM DBA_HIST_SYSSTAT b, DBA_HIST_SYSSTAT e
WHERE B.SNAP_ID = &beg_snap
AND E.SNAP_ID = &end_snap
AND B.DBID = &DBID
AND E.DBID = &DBID
AND B.INSTANCE_NUMBER = &INST_NUM
AND E.INSTANCE_NUMBER = &INST_NUM
and e.STAT_NAME = 'gc cr blocks served'
and b.stat_name = 'gc cr blocks served') v_2
from dual);
#### Global cache log flushes for cr blocks served %:
select decode(v_2, 0, '', v_1 / v_2)
from (select 10 * (SELECT sum(e.VALUE) - sum(b.value)
FROM DBA_HIST_SYSSTAT b, DBA_HIST_SYSSTAT e WHERE B.SNAP_ID = &beg_snap
AND E.SNAP_ID = &end_snap AND B.DBID = &DBID
AND E.DBID = &DBID
AND B.INSTANCE_NUMBER = &INST_NUM
AND E.INSTANCE_NUMBER = &INST_NUM
and e.STAT_NAME = 'cr_flushes'
and e.STAT_NAME = 'cr_flushes') v_1, (SELECT sum(e.VALUE) - sum(b.value)
FROM DBA_HIST_SYSSTAT b, DBA_HIST_SYSSTAT e
WHERE B.SNAP_ID = &beg_snap
AND E.SNAP_ID = &end_snap AND B.DBID = &DBID
AND E.DBID = &DBID
AND B.INSTANCE_NUMBER = &INST_NUM
AND E.INSTANCE_NUMBER = &INST_NUM
and e.STAT_NAME = 'gc cr blocks served'
and b.stat_name = 'gc cr blocks served') v_2 from dual);
#### Avg global cache cr block flush time (ms): select decode(v_2, 0, '', v_1 / v_2)
from (select 10 * (SELECT sum(e.VALUE) - sum(b.value)
FROM DBA_HIST_SYSSTAT b, DBA_HIST_SYSSTAT e
WHERE B.SNAP_ID = &beg_snap
AND E.SNAP_ID = &end_snap AND B.DBID = &DBID
AND E.DBID = &DBID
AND B.INSTANCE_NUMBER = &INST_NUM
AND E.INSTANCE_NUMBER = &INST_NUM
and e.STAT_NAME = 'gc cr block flush time'
and e.STAT_NAME = 'gc cr block flush time') v_1,
(SELECT sum(e.VALUE) - sum(b.value)
FROM DBA_HIST_SYSSTAT b, DBA_HIST_SYSSTAT e WHERE B.SNAP_ID = &beg_snap
AND E.SNAP_ID = &end_snap AND B.DBID = &DBID
AND E.DBID = &DBID
AND B.INSTANCE_NUMBER = &INST_NUM
AND E.INSTANCE_NUMBER = &INST_NUM
and e.STAT_NAME = 'cr_flushes'
and b.stat_name = 'cr_flushes') v_2 from dual);
#### Avg global cache current block pin time (ms):
select decode(v_2, 0, '', v_1 / v_2)
from (select 10 * (SELECT sum(e.VALUE) - sum(b.value)
FROM DBA_HIST_SYSSTAT b, DBA_HIST_SYSSTAT e WHERE B.SNAP_ID = &beg_snap
AND E.SNAP_ID = &end_snap AND B.DBID = &DBID
AND E.DBID = &DBID
AND B.INSTANCE_NUMBER = &INST_NUM
AND E.INSTANCE_NUMBER = &INST_NUM
and e.STAT_NAME = 'gc current block pin time'
and e.STAT_NAME = 'gc current block pin time') v_1,
(SELECT sum(e.VALUE) - sum(b.value)
FROM DBA_HIST_SYSSTAT b, DBA_HIST_SYSSTAT e WHERE B.SNAP_ID = &beg_snap
AND E.SNAP_ID = &end_snap AND B.DBID = &DBID
AND E.DBID = &DBID
AND B.INSTANCE_NUMBER = &INST_NUM
AND E.INSTANCE_NUMBER = &INST_NUM
and e.STAT_NAME = 'gc current blocks served'
and b.stat_name = 'gc current blocks served') v_2
from dual);
#### Avg global cache current block send time (ms):
select decode(v_2, 0, '', v_1 / v_2)
from (select 10 * (SELECT sum(e.VALUE) - sum(b.value)
FROM DBA_HIST_SYSSTAT b, DBA_HIST_SYSSTAT e
WHERE B.SNAP_ID = &beg_snap
AND E.SNAP_ID = &end_snap AND B.DBID = &DBID
AND E.DBID = &DBID
AND B.INSTANCE_NUMBER = &INST_NUM
AND E.INSTANCE_NUMBER = &INST_NUM
and e.STAT_NAME = 'gc current block send time'
and e.STAT_NAME = 'gc current block send time') v_1,
(SELECT sum(e.VALUE) - sum(b.value)
FROM DBA_HIST_SYSSTAT b, DBA_HIST_SYSSTAT e WHERE B.SNAP_ID = &beg_snap
AND E.SNAP_ID = &end_snap AND B.DBID = &DBID
AND E.DBID = &DBID
AND B.INSTANCE_NUMBER = &INST_NUM
AND E.INSTANCE_NUMBER = &INST_NUM
and e.STAT_NAME = 'gc current blocks served'
and b.stat_name = 'gc current blocks served') v_2 from dual);
#### Global cache log flushes for current blocks served %: select decode(v_2, 0, '', v_1 / v_2)
from (select 10 *
(SELECT sum(e.VALUE) - sum(b.value)
FROM DBA_HIST_SYSSTAT b, DBA_HIST_SYSSTAT e
WHERE B.SNAP_ID = &beg_snap
AND E.SNAP_ID = &end_snap
AND B.DBID = &DBID
AND E.DBID = &DBID
AND B.INSTANCE_NUMBER = &INST_NUM
AND E.INSTANCE_NUMBER = &INST_NUM
and e.STAT_NAME = 'gc current block flush time'
and e.STAT_NAME = 'gc current block flush time') v_1,
(SELECT sum(e.VALUE) - sum(b.value)
FROM DBA_HIST_SYSSTAT b, DBA_HIST_SYSSTAT e
WHERE B.SNAP_ID = &beg_snap
AND E.SNAP_ID = &end_snap
AND B.DBID = &DBID
AND E.DBID = &DBID
AND B.INSTANCE_NUMBER = &INST_NUM
AND E.INSTANCE_NUMBER = &INST_NUM
and e.STAT_NAME = 'current_flushes'
and b.stat_name = 'current_flushes') v_2
from dual);
#### Avg global cache current block flush time (ms):
select decode(v_2, 0, '', v_1 / v_2)
from (select 10 * (SELECT sum(e.VALUE) - sum(b.value)
FROM DBA_HIST_SYSSTAT b, DBA_HIST_SYSSTAT e
WHERE B.SNAP_ID = &beg_snap
AND E.SNAP_ID = &end_snap AND B.DBID = &DBID
AND E.DBID = &DBID
AND B.INSTANCE_NUMBER = &INST_NUM
AND E.INSTANCE_NUMBER = &INST_NUM
and e.STAT_NAME = 'current_flushes'
and e.STAT_NAME = 'current_flushes') v_1, (SELECT sum(e.VALUE) - sum(b.value)
FROM DBA_HIST_SYSSTAT b, DBA_HIST_SYSSTAT e
WHERE B.SNAP_ID = &beg_snap
AND E.SNAP_ID = &end_snap
AND B.DBID = &DBID
AND E.DBID = &DBID
AND B.INSTANCE_NUMBER = &INST_NUM
AND E.INSTANCE_NUMBER = &INST_NUM
and e.STAT_NAME = 'gc current blocks served'
and b.stat_name = 'gc current blocks served') v_2 from dual);
#### Avg message sent queue time (ms):
select decode(v_2, 0, '', v_1 / v_2)
from (select (SELECT sum(e.VALUE) - sum(b.value)
FROM DBA_HIST_SYSSTAT b, DBA_HIST_SYSSTAT e WHERE B.SNAP_ID = &beg_snap
AND E.SNAP_ID = &end_snap
AND B.DBID = &DBID
AND E.DBID = &DBID
AND B.INSTANCE_NUMBER = &INST_NUM
AND E.INSTANCE_NUMBER = &INST_NUM
and e.STAT_NAME = 'msgs sent queue time (ms)'
and e.STAT_NAME = 'msgs sent queue time (ms)') v_1,
(SELECT sum(e.VALUE) - sum(b.value)
FROM DBA_HIST_SYSSTAT b, DBA_HIST_SYSSTAT e
WHERE B.SNAP_ID = &beg_snap
AND E.SNAP_ID = &end_snap
AND B.DBID = &DBID
AND E.DBID = &DBID
AND B.INSTANCE_NUMBER = &INST_NUM
AND E.INSTANCE_NUMBER = &INST_NUM
and e.STAT_NAME = 'msgs sent queued'
and b.stat_name = 'msgs sent queued') v_2
from dual);
#### Avg message sent queue time on ksxp (ms):
select decode(v_2, 0, '', v_1 / v_2)
from (select (SELECT sum(e.VALUE) - sum(b.value)
FROM DBA_HIST_SYSSTAT b, DBA_HIST_SYSSTAT e WHERE B.SNAP_ID = &beg_snap
AND E.SNAP_ID = &end_snap AND B.DBID = &DBID
AND E.DBID = &DBID
AND B.INSTANCE_NUMBER = &INST_NUM
AND E.INSTANCE_NUMBER = &INST_NUM
and e.STAT_NAME = 'msgs sent queue time on ksxp (ms)'
and e.STAT_NAME = 'msgs sent queue time on ksxp (ms)') v_1,
(SELECT sum(e.VALUE) - sum(b.value)
FROM DBA_HIST_SYSSTAT b, DBA_HIST_SYSSTAT e WHERE B.SNAP_ID = &beg_snap
AND E.SNAP_ID = &end_snap AND B.DBID = &DBID
AND E.DBID = &DBID
AND B.INSTANCE_NUMBER = &INST_NUM
AND E.INSTANCE_NUMBER = &INST_NUM
and e.STAT_NAME = 'msgs sent queued on ksxp'
and b.stat_name = 'msgs sent queued on ksxp') v_2
from dual);
#### Avg message received queue time (ms):
select decode(v_2, 0, '', v_1 / v_2)
from (select (SELECT sum(e.VALUE) - sum(b.value)
FROM DBA_HIST_SYSSTAT b, DBA_HIST_SYSSTAT e WHERE B.SNAP_ID = &beg_snap
AND E.SNAP_ID = &end_snap
AND B.DBID = &DBID
AND E.DBID = &DBID
AND B.INSTANCE_NUMBER = &INST_NUM
AND E.INSTANCE_NUMBER = &INST_NUM
and e.STAT_NAME = 'msgs received queue time (ms)'
and e.STAT_NAME = 'msgs received queue time (ms)') v_1,
(SELECT sum(e.VALUE) - sum(b.value)
FROM DBA_HIST_SYSSTAT b, DBA_HIST_SYSSTAT e
WHERE B.SNAP_ID = &beg_snap
AND E.SNAP_ID = &end_snap
AND B.DBID = &DBID
AND E.DBID = &DBID
AND B.INSTANCE_NUMBER = &INST_NUM
AND E.INSTANCE_NUMBER = &INST_NUM
and e.STAT_NAME = 'msgs received queued'
and b.stat_name = 'msgs received queued') v_2
from dual);
#### Avg GCS message process time (ms):
select decode(v_2, 0, '', v_1 / v_2)
from (select (SELECT sum(e.VALUE) - sum(b.value)
FROM DBA_HIST_SYSSTAT b, DBA_HIST_SYSSTAT e WHERE B.SNAP_ID = &beg_snap
AND E.SNAP_ID = &end_snap AND B.DBID = &DBID
AND E.DBID = &DBID
AND B.INSTANCE_NUMBER = &INST_NUM
AND E.INSTANCE_NUMBER = &INST_NUM
and e.STAT_NAME = 'gcs msgs process time(ms)'
and e.STAT_NAME = 'gcs msgs process time(ms)') v_1,
(SELECT sum(e.VALUE) - sum(b.value)
FROM DBA_HIST_SYSSTAT b, DBA_HIST_SYSSTAT e WHERE B.SNAP_ID = &beg_snap
AND E.SNAP_ID = &end_snap AND B.DBID = &DBID
AND E.DBID = &DBID
AND B.INSTANCE_NUMBER = &INST_NUM
AND E.INSTANCE_NUMBER = &INST_NUM
and e.STAT_NAME = 'gcs msgs received'
and b.stat_name = 'gcs msgs received') v_2 from dual);
#### Avg GES message process time (ms):
select decode(v_2, 0, '', v_1 / v_2)
from (select (SELECT sum(e.VALUE) - sum(b.value)
FROM DBA_HIST_SYSSTAT b, DBA_HIST_SYSSTAT e WHERE B.SNAP_ID = &beg_snap
AND E.SNAP_ID = &end_snap
AND B.DBID = &DBID
AND E.DBID = &DBID
AND B.INSTANCE_NUMBER = &INST_NUM
AND E.INSTANCE_NUMBER = &INST_NUM
and e.STAT_NAME = 'ges msgs process time(ms)'
and e.STAT_NAME = 'ges msgs process time(ms)') v_1,
(SELECT sum(e.VALUE) - sum(b.value)
FROM DBA_HIST_SYSSTAT b, DBA_HIST_SYSSTAT e
WHERE B.SNAP_ID = &beg_snap
AND E.SNAP_ID = &end_snap
AND B.DBID = &DBID
AND E.DBID = &DBID
AND B.INSTANCE_NUMBER = &INST_NUM
AND E.INSTANCE_NUMBER = &INST_NUM
and e.STAT_NAME = 'ges msgs received'
and b.stat_name = 'ges msgs received') v_2
from dual);
#### % of direct sent messages:
select decode(v_2, 0, '', v_1 / v_2)
from (select 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 = 'messages sent directly'
and e.STAT_NAME = 'messages sent directly') 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
('messages sent directly', 'messages sent indirectly', 'messages flow controlled')
and b.stat_name in
('messages sent directly', 'messages sent indirectly',
'messages flow controlled')) v_2
from dual);
#### % of indirect sent messages:
select decode(v_2, 0, '', v_1 / v_2)
from (select 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 = 'messages sent indirectly'
and e.STAT_NAME = 'messages sent indirectly') 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
('messages sent directly', 'messages sent indirectly', 'messages flow controlled')
and b.stat_name in
('messages sent directly', 'messages sent indirectly', 'messages flow controlled')) v_2
from dual);
#### % of flow controlled messages:
select decode(v_2, 0, '', v_1 / v_2)
from (select 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 = 'messages flow controlled'
and e.STAT_NAME = 'messages flow controlled') 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
('messages sent directly', 'messages sent indirectly',
'messages flow controlled')
and b.stat_name in
('messages sent directly', 'messages sent indirectly',
'messages flow controlled')) v_2
from dual);
####Time Model Statistics
select stat_name,
seconds,
decode((dbt + bglast), 0, percent, to_number(null)), (dbt + bglast) order_col
from (select b.stat_name,
(b.value - a.value) / 1000000 as seconds, 100 *
((b.value - a.value) /
(SELECT sum(e.value) - sum(b.value)
FROM DBA_HIST_SYS_TIME_MODEL b, DBA_HIST_SYS_TIME_MODEL 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.STAT_NAME = 'DB time'
and e.stat_name = 'DB time')) as percent, decode(b.stat_name, 'DB time', 1, 0) dbt,
decode(instr(b.stat_name, 'background'), 1, 2, 0) bglast from dba_hist_sys_time_model a, dba_hist_sys_time_model b where a.dbid = &dbid
and b.dbid = &dbid
and a.instance_number = &inst_num
and b.instance_number = &inst_num
and a.snap_id = &beg_snap
and b.snap_id = &end_snap
and a.stat_id = b.stat_id
and b.value - a.value > 0)
order by order_col asc, seconds desc, stat_name;
####Wait Class
select e.wait_class wait_class,
sum(e.total_waits - nvl(b.total_waits, 0)) waits,
decode(sum(e.total_waits - nvl(b.total_waits, 0)),
0,
to_number(NULL),
100 * sum(e.total_timeouts - nvl(b.total_timeouts, 0)) / sum(e.total_waits - nvl(b.total_waits, 0))) topct,
sum(e.time_waited_micro - nvl(b.time_waited_micro, 0)) / 1000000 time,
decode(sum(e.total_waits - nvl(b.total_waits, 0)),
0,
to_number(NULL),
(sum(e.time_waited_micro - nvl(b.time_waited_micro, 0)) / 1000) /
sum(e.total_waits - nvl(b.total_waits, 0))) avgwt,
sum(e.total_waits - nvl(b.total_waits, 0)) /
(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')) txwaits
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'
group by e.wait_class
order by time desc, waits desc, wait_class;
####Wait Events
select e.event_name event,
e.total_waits - nvl(b.total_waits, 0) waits,
decode(e.total_waits - nvl(b.total_waits, 0),
0,
to_number(NULL),
100 * (e.total_timeouts - nvl(b.total_timeouts, 0)) /
(e.total_waits - nvl(b.total_waits, 0))) pctto,
(e.time_waited_micro - nvl(b.time_waited_micro, 0)) / 1000000 time,
decode((e.total_waits - nvl(b.total_waits, 0)),
0,
to_number(NULL),
((e.time_waited_micro - nvl(b.time_waited_micro, 0)) / 1000) /
(e.total_waits - nvl(b.total_waits, 0))) avgwt,
(e.total_waits - nvl(b.total_waits, 0)) /
(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')) txwaits, decode(e.wait_class, 'Idle', 99, 0) idle
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.event_name not in ('smon timer',
'pmon timer',
'dispatcher timer',
'dispatcher listen timer', 'rdbms ipc message')
order by idle, time desc, waits desc, event;
####Background Wait Events
select e.event_name event,
e.total_waits - nvl(b.total_waits, 0) waits,
decode(e.total_waits - nvl(b.total_waits, 0),
0,
to_number(NULL),
100 * (e.total_timeouts - nvl(b.total_timeouts, 0)) /
(e.total_waits - nvl(b.total_waits, 0))) pctto,
(e.time_waited_micro - nvl(b.time_waited_micro, 0)) / 1000000 time,
decode((e.total_waits - nvl(b.total_waits, 0)),
0,
to_number(NULL),
((e.time_waited_micro - nvl(b.time_waited_micro, 0)) / 1000) /
(e.total_waits - nvl(b.total_waits, 0))) avgwt,
(e.total_waits - nvl(b.total_waits, 0)) /
(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')) txwaits, decode(e.wait_class, ' Idle ', 99, 0) idle
from dba_hist_bg_event_summary b, dba_hist_bg_event_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.event_id(+) = e.event_id
and e.total_waits > nvl(b.total_waits, 0)
order by idle, time desc, waits desc, event;
####Operating System Statistics
select
substr(e.stat_name, 1, 35) as name,
(case when e.stat_name like 'NUM_CPU%'
when e.stat_name = 'LOAD'
then e.value
then e.value
when e.stat_name = 'PHYSICAL_MEMORY_BYTES' then e.value
else e.value - b.value
end) as value,
( decode(instrb(e.stat_name, 'TIME'), 0, 0, 1)
+ decode(instrb(e.stat_name, 'LOAD'), 0, 0, 2)
+ decode(instrb(e.stat_name, 'CPU_WAIT'), 0, 0, 3)
+ decode(instrb(e.stat_name, 'VM_'), 0, 0, 4)
+ decode(instrb(e.stat_name, 'PHYSICAL_MEMORY'), 0, 0, 5)
+ decode(instrb(e.stat_name, 'NUM_CPU'), 0, 0, 6)
) styp
from dba_hist_osstat b,
dba_hist_osstat e
where b.dbid = &dbid
and e.dbid = &dbid
and b.instance_number = &inst_num and e.instance_number = &inst_num and b.snap_id = &beg_snap
and e.snap_id = &end_snap
and b.stat_id = e.stat_id
order by styp asc, name asc
####Service Statistics
select service_name,
round(db_time / 1000000, 1),
round(db_cpu / 1000000, 1),
phy_reads,
log_reads
from (select s1.service_name,
sum(decode(s1.stat_name, 'DB time', s1.diff, 0)) db_time,
sum(decode(s1.stat_name, 'DB CPU', s1.diff, 0)) db_cpu,
sum(decode(s1.stat_name, 'physical reads', s1.diff, 0)) phy_reads,
sum(decode(s1.stat_name,
' session logical reads ', s1.diff,
0)) log_reads
from (select e.service_name service_name,
e.stat_name stat_name,
e.value - b.value diff
from dba_hist_service_stat b, dba_hist_service_stat 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.stat_id = e.stat_id
and b.service_name_hash = e.service_name_hash) s1
group by s1.service_name
order by db_time desc, service_name) where rownum <= 5;
####Service Wait Class Stats
select service_name,
uio_waits,
uio_time,
con_waits,
con_time,
adm_waits,
adm_time,
net_waits,
net_time
from (select stat1.service_name service_name,
db_time,
uio_waits,
uio_time,
con_waits,
con_time,
adm_waits,
adm_time,
net_waits,
net_time
from (select s1.service_name,
sum(decode(s1.stat_name, ' DB time ', s1.diff, 0)) db_time
from (select e.service_name service_name,
e.stat_name stat_name,
e.value - b.value diff
from dba_hist_service_stat b,
dba_hist_service_stat 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.stat_id = e.stat_id
and b.service_name_hash =
group by s1.service_name) stat1, (select s2.service_name,
sum(decode(s2.wait_class,
'User I/O',
s2.total_waits,
0)) uio_waits,
sum(decode(s2.wait_class,
'User I/O',
s2.time_waited,
0)) uio_time,
sum(decode(s2.wait_class,
'Concurrency',
s2.total_waits,
0)) con_waits,
sum(decode(s2.wait_class,
'Concurrency',
s2.time_waited,
0)) con_time,
sum(decode(s2.wait_class,
'Administrative',
s2.total_waits,
0)) adm_waits,
sum(decode(s2.wait_class,
'Administrative',
s2.time_waited,
0)) adm_time,
e.service_name_hash) s1
sum(decode(s2.wait_class, 'Network', s2.total_waits, 0))
net_waits,
sum(decode(s2.wait_class, 'Network', s2.time_waited, 0))
net_time
from (select e.service_name service_name,
e.wait_class wait_class,
e.total_waits - b.total_waits total_waits,
e.time_waited - b.time_waited time_waited
from dba_hist_service_wait_class b,
dba_hist_service_wait_class 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.wait_class_id = e.wait_class_id
and b.service_name_hash = e.service_name_hash) s2 group by s2.service_name) stat2
where stat1.service_name = stat2.service_name
order by db_time desc, service_name)
where rownum <= 5;
####SQL ordered by Elapsed Time
select *
from (select nvl((sqt.elap / 1000000), to_number(null)),
nvl((sqt.cput / 1000000), to_number(null)), sqt.exec,
decode(sqt.exec,
0,
to_number(null),
(sqt.elap / sqt.exec / 1000000)),
(100 *
(sqt.elap / (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 = 'DB time'
and b.stat_name = 'DB time'))) norm_val,
sqt.sql_id,
to_clob(decode(sqt.module,
null,
null,
'Module: ' || sqt.module)),
nvl(st.sql_text, to_clob(' ** SQL Text Not Available ** '))
from (select sql_id,
max(module) module,
sum(elapsed_time_delta) elap,
sum(cpu_time_delta) cput,
sum(executions_delta) exec
from dba_hist_sqlstat
where dbid = &dbid
and instance_number = &inst_num and &beg_snap < snap_id
and snap_id <= &end_snap
group by sql_id) sqt,
dba_hist_sqltext st
where st.sql_id(+) = sqt.sql_id
and st.dbid(+) = &dbid
order by nvl(sqt.elap, -1) desc, sqt.sql_id) where rownum < 65
and (rownum <=10 or norm_val > 1);
####SQL ordered by CPU Time
select *
from (select nvl((sqt.cput / 1000000), to_number(null)),
nvl((sqt.elap / 1000000), to_number(null)), sqt.exec,
decode(sqt.exec,
0,
to_number(null),
(sqt.cput / sqt.exec / 1000000)), (100 * (sqt.elap /
(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 = 'DB time'
and b.stat_name = 'DB time'))) norm_val,
sqt.sql_id,
to_clob(decode(sqt.module,
null,
null,
'Module: ' || sqt.module)),
nvl(st.sql_text, to_clob('** SQL Text Not Available **'))
from (select sql_id,
max(module) module,
sum(cpu_time_delta) cput,
sum(elapsed_time_delta) elap,
sum(executions_delta) exec
from dba_hist_sqlstat
where dbid = &dbid
and instance_number = &inst_num and &beg_snap < snap_id
and snap_id <= &end_snap
group by sql_id) sqt,
dba_hist_sqltext st
where st.sql_id(+) = sqt.sql_id
and st.dbid(+) = &dbid
order by nvl(sqt.cput, -1) desc, sqt.sql_id) where rownum < 65
and (rownum <= 10 or norm_val > 1)
####SQL ordered by Gets
select *
from (select sqt.bget,
sqt.exec,
decode(sqt.exec, 0, to_number(null), (sqt.bget / sqt.exec)),
(100 * sqt.bget) /
(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') norm_val,
nvl((sqt.cput / 1000000), to_number(null)), nvl((sqt.elap / 1000000), to_number(null)), sqt.sql_id,
to_clob(decode(sqt.module,
null,
null,
'Module: ' || sqt.module)),
nvl(st.sql_text, to_clob('** SQL Text Not Available **')) from (select sql_id,
max(module) module,
sum(buffer_gets_delta) bget,
sum(executions_delta) exec,
sum(cpu_time_delta) cput,
sum(elapsed_time_delta) elap from dba_hist_sqlstat
where dbid = &dbid
and instance_number = &inst_num and &beg_snap < snap_id
and snap_id <= &end_snap
group by sql_id) sqt,
dba_hist_sqltext st
where st.sql_id(+) = sqt.sql_id
and st.dbid(+) = &dbid
order by nvl(sqt.bget, -1) desc, sqt.sql_id) where rownum < 65
and (rownum <= 10 or norm_val > 1)
####SQL ordered by Reads
select *
from (select sqt.dskr,
sqt.exec,
decode(sqt.exec, 0, to_number(null), (sqt.dskr / sqt.exec)),
(100 * sqt.dskr) /
(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') norm_val, nvl((sqt.cput / 1000000), to_number(null)),
nvl((sqt.elap / 1000000), to_number(null)),
sqt.sql_id,
decode(sqt.module, null, null, 'Module: ' || sqt.module),
nvl(st.sql_text, to_clob('** SQL Text Not Available **'))
from (select sql_id,
max(module) module,
sum(disk_reads_delta) dskr,
sum(executions_delta) exec,
sum(cpu_time_delta) cput,
sum(elapsed_time_delta) elap from dba_hist_sqlstat
where dbid = &dbid
and instance_number = &inst_num and &beg_snap < snap_id
and snap_id <= &end_snap
group by sql_id) sqt,
dba_hist_sqltext st
where st.sql_id(+) = sqt.sql_id
and st.dbid(+) = &dbid
and (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
order by nvl(sqt.dskr, -1) desc, sqt.sql_id)
where rownum < 65and(rownum <= 10
or norm_val > 1)
####SQL ordered by Executions
select *
from (select sqt.exec exec,
sqt.rowp,
sqt.rowp / sqt.exec,
sqt.cput / sqt.exec / 1000000, sqt.elap / sqt.exec / 1000000, sqt.sql_id,
decode(sqt.module, null, null, 'Module: ' || sqt.module),
nvl(st.sql_text, to_clob('** SQL Text Not Available **')) from (select sql_id,
max(module) module,
sum(executions_delta) exec,
sum(rows_processed_delta) rowp,
sum(cpu_time_delta) cput,
sum(elapsed_time_delta) elap
from dba_hist_sqlstat
where dbid = &dbid
and instance_number = &inst_num and &beg_snap < snap_id
and snap_id <= &end_snap
group by sql_id) sqt,
dba_hist_sqltext st
where st.sql_id(+) = sqt.sql_id
and st.dbid(+) = &dbid
and sqt.exec > 0
order by nvl(sqt.exec, -1) desc, sqt.sql_id) where rownum < 65
and (rownum <= 10 or
(100 * exec) /
(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 = 'execute count'
and b.stat_name = 'execute count') > 1)
####SQL ordered by Parse Calls
select *
from (select sqt.prsc,
sqt.exec,
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 = 'parse count (total)'
and b.stat_name = 'parse count (total)'),
0,
0,
100 * sqt.prsc /
(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 = 'parse count (total)'
and b.stat_name = 'parse count (total)')) norm_val,
sqt.sql_id,
decode(sqt.module, null, null, 'Module: ' || sqt.module),
nvl(st.sql_text, to_clob('** SQL Text Not Available **'))
from (select sql_id,
max(module) module,
sum(buffer_gets_delta) bget,
sum(disk_reads_delta) dskr,
sum(executions_delta) exec,
sum(parse_calls_delta) prsc from dba_hist_sqlstat
where dbid = &dbid
and instance_number = &inst_num and &beg_snap < snap_id
and snap_id <= &end_snap
group by sql_id) sqt,
dba_hist_sqltext st
where st.sql_id(+) = sqt.sql_id
and st.dbid(+) = &dbid
order by nvl(sqt.prsc, -1) desc, sqt.sql_id)
where rownum < 65
and (rownum <= 10 or norm_val > 1)
####SQL ordered by Sharable Memory
select *
from (select
e.sharable_mem,
sqt.exec,
decode((SELECT sum(value)
FROM DBA_HIST_PARAMETER WHERE SNAP_ID = &end_snap
AND DBID = &DBID
AND INSTANCE_NUMBER = &INST_NUM
AND PARAMETER_NAME = '__shared_pool_size'),
0,
0,
100 * e.sharable_mem /
(SELECT sum(value)
FROM DBA_HIST_PARAMETER WHERE SNAP_ID = &end_snap
AND DBID = &DBID
AND INSTANCE_NUMBER = &INST_NUM
AND PARAMETER_NAME = '__shared_pool_size')),
e.sql_id,
decode(e.module, null, null, 'Module: ' || e.module),
nvl(st.sql_text, to_clob('** SQL Text Not Available **'))
from dba_hist_sqlstat e,
(select sql_id, sum(executions_delta) exec
from dba_hist_sqlstat
where dbid = &dbid
and instance_number = &inst_num and &beg_snap < snap_id
and snap_id <= &end_snap
group by sql_id) sqt,
dba_hist_sqltext st
where sqt.sql_id(+) = e.sql_id
and e.snap_id = &end_snap
and e.dbid = &dbid
and e.instance_number = &inst_num
and st.sql_id(+) = e.sql_id
and st.dbid(+) = &dbid
and e.sharable_mem > 1048576
order by nvl(e.sharable_mem, -1) desc, e.sql_id) where rownum < 65;
####SQL ordered by Version Count
select *
from (select
e.version_count,
sqt.exec,
e.sql_id,
decode(e.module, null, null, 'Module: ' || e.module),
nvl(st.sql_text, to_clob('** SQL Text Not Available **')) from dba_hist_sqlstat e,
(select sql_id, sum(executions_delta) exec
from dba_hist_sqlstat
where dbid = &dbid
and instance_number = &inst_num
and &beg_snap < snap_id
and snap_id <= &end_snap
group by sql_id) sqt,
dba_hist_sqltext st
where sqt.sql_id(+) = e.sql_id
and e.snap_id = &end_snap
and e.dbid = &dbid
and e.instance_number = &inst_num
and st.sql_id(+) = e.sql_id
and st.dbid(+) = &dbid
and e.version_count > 20
order by nvl(e.version_count, -1) desc, e.sql_id) where rownum < 65;
####SQL ordered by Cluster Wait Time
select *
from (select
sqt.clwait / 1000000,
decode(sqt.elap, 0, sqt.clwait, 100 * sqt.clwait / sqt.elap), sqt.elap / 1000000,
sqt.cput / 1000000,
sqt.exec,
sqt.sql_id,
decode(sqt.module, null, null, 'Module: ' || sqt.module), nvl(st.sql_text, to_clob('** SQL Text Not Available **')) from (select sql_id, max(module) module,
sum(executions_delta) exec,
sum(cpu_time_delta) cput,
sum(elapsed_time_delta) elap,
sum(clwait_delta) clwait
from dba_hist_sqlstat
where dbid = &dbid
and instance_number = &inst_num
and &beg_snap < snap_id
and snap_id <= &end_snap
group by sql_id) sqt,
dba_hist_sqltext st
where st.sql_id(+) = sqt.sql_id
and st.dbid(+) = &dbid
and sqt.clwait > 5000
order by nvl(sqt.clwait, -1) desc, sqt.sql_id) where rownum < 65
####Instance Activity Stats
select b.stat_name st,
e.value - b.value,
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),
2),
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')),
2)
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 b.stat_id = e.stat_id
and e.stat_name not in
('logons current', 'opened cursors current',
'workarea memory allocated', 'session cursor cache count') and e.value >= b.value
and e.value > 0
order by st;
####Instance Activity Stats - Absolute Values
select b.stat_name st,
b.value,
e.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 b.stat_id = e.stat_id
and e.stat_name in
('logons current',
'opened cursors current',
'workarea memory allocated',
'session cursor cache count')
and e.value > 0
####Instance Activity Stats - Thread Activity select 'log switches (derived)',
e.sequence# - b.sequence#,
(e.sequence# - b.sequence#) /
((SELECT EXTRACT(DAY FROM E.END_INTERVAL_TIME -
86400 +
EXTRACT(HOUR FROM E.END_INTERVAL_TIME
3600 +
EXTRACT(MINUTE FROM
B.END_INTERVAL_TIME) *
- B.END_INTERVAL_TIME) *
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) / 3600)
from dba_hist_thread e, dba_hist_thread b
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.thread# = e.thread#
and b.thread_instance_number = e.thread_instance_number and e.thread_instance_number = &inst_num
####Operating System Statistics
select
substr(e.stat_name, 1, 35) as name,
(case when e.stat_name like 'NUM_CPU%' then e.value
when e.stat_name = 'LOAD' then e.value
when e.stat_name = 'PHYSICAL_MEMORY_BYTES' then e.value
else e.value - b.value
end) as value,
( decode(instrb(e.stat_name, 'TIME'), 0, 0, 1)
+ decode(instrb(e.stat_name, 'LOAD'), 0, 0, 2)
+ decode(instrb(e.stat_name, 'CPU_WAIT'), 0, 0, 3)
+ decode(instrb(e.stat_name, 'VM_'), 0, 0, 4)
+ decode(instrb(e.stat_name, 'PHYSICAL_MEMORY'), 0, 0, 5)
+ decode(instrb(e.stat_name, 'NUM_CPU'), 0, 0, 6)
) styp
from dba_hist_osstat b,
dba_hist_osstat e
where b.dbid = &dbid
and e.dbid = &dbid
and b.instance_number = &inst_num and e.instance_number = &inst_num and b.snap_id = &beg_snap
and e.snap_id = &end_snap
and b.stat_id = e.stat_id
order by styp asc, name asc
####Service Statistics
select service_name,
round(db_time / 1000000, 1),
round(db_cpu / 1000000, 1),
phy_reads,
log_reads
from (select s1.service_name,
sum(decode(s1.stat_name, 'DB time', s1.diff, 0)) db_time,
sum(decode(s1.stat_name, 'DB CPU', s1.diff, 0)) db_cpu,
sum(decode(s1.stat_name, 'physical reads', s1.diff, 0)) phy_reads,
sum(decode(s1.stat_name,
' session logical reads ',
s1.diff,
0)) log_reads
from (select e.service_name service_name,
e.stat_name stat_name,
e.value - b.value diff
from dba_hist_service_stat b, dba_hist_service_stat 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.stat_id = e.stat_id
and b.service_name_hash = e.service_name_hash) s1
group by s1.service_name
order by db_time desc, service_name) where rownum <= 5;
####Service Wait Class Stats
select service_name,
uio_waits,
uio_time,
con_waits,
con_time,
adm_waits,
adm_time,
net_waits,
net_time
from (select stat1.service_name service_name,
db_time,
uio_waits,
uio_time,
con_waits,
con_time,
adm_waits,
adm_time,
net_waits,
net_time
from (select s1.service_name,
sum(decode(s1.stat_name, ' DB time ', s1.diff, 0)) db_time
from (select e.service_name service_name,
e.stat_name stat_name,
e.value - b.value diff
from dba_hist_service_stat b,
dba_hist_service_stat 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.stat_id = e.stat_id
and b.service_name_hash =
group by s1.service_name) stat1, (select s2.service_name,
sum(decode(s2.wait_class,
'User I/O',
s2.total_waits,
0)) uio_waits,
sum(decode(s2.wait_class,
'User I/O',
s2.time_waited,
0)) uio_time,
sum(decode(s2.wait_class,
'Concurrency',
s2.total_waits,
0)) con_waits,
sum(decode(s2.wait_class,
'Concurrency',
s2.time_waited,
0)) con_time,
sum(decode(s2.wait_class,
'Administrative',
s2.total_waits,
0)) adm_waits,
sum(decode(s2.wait_class,
'Administrative',
s2.time_waited,
0)) adm_time,
e.service_name_hash) s1
sum(decode(s2.wait_class, 'Network', s2.total_waits, 0))
net_waits,
sum(decode(s2.wait_class, 'Network', s2.time_waited, 0))
net_time
from (select e.service_name service_name,
e.wait_class wait_class,
e.total_waits - b.total_waits total_waits,
e.time_waited - b.time_waited time_waited
from dba_hist_service_wait_class b,
dba_hist_service_wait_class 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.wait_class_id = e.wait_class_id
and b.service_name_hash = e.service_name_hash) s2 group by s2.service_name) stat2
where stat1.service_name = stat2.service_name
order by db_time desc, service_name)
where rownum <= 5;
####Tablespace IO Stats
select e.tsname tsname,
sum(e.phyrds - nvl(b.phyrds, 0)) reads, sum(e.phyrds - nvl(b.phyrds, 0)) /
(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) rps,
decode(sum(e.phyrds - nvl(b.phyrds, 0)),
0,
0,
10 * (sum(e.readtim - nvl(b.readtim, 0)) /
sum(e.phyrds - nvl(b.phyrds, 0)))) atpr,
decode(sum(e.phyrds - nvl(b.phyrds, 0)),
0,
0,
sum(e.phyblkrd - nvl(b.phyblkrd, 0)) /
sum(e.phyrds - nvl(b.phyrds, 0))) bpr, sum(e.phywrts - nvl(b.phywrts, 0)) writes, sum(e.phywrts - nvl(b.phywrts, 0)) /
(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) wps,
sum(e.wait_count - nvl(b.wait_count, 0)) waits,
decode(sum(e.wait_count - nvl(b.wait_count, 0)),
0,
0,
10 * (sum(e.time - nvl(b.time, 0)) /
sum(e.wait_count - nvl(b.wait_count, 0)))) atpwt, sum(e.phyrds - nvl(b.phyrds, 0)) +
sum(e.phywrts - nvl(b.phywrts, 0)) ios
from dba_hist_filestatxs e, dba_hist_filestatxs b
where b.snap_id(+) = &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.tsname(+) = e.tsname
and b.file#(+) = e.file#
and b.creation_change#(+) = e.creation_change#
and ((e.phyrds - nvl(b.phyrds, 0)) + (e.phywrts - nvl(b.phywrts, 0))) > 0
group by e.tsname
union all
select e.tsname tsname,
sum(e.phyrds - nvl(b.phyrds, 0)) reads,
sum(e.phyrds - nvl(b.phyrds, 0)) /
(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) rps,
decode(sum(e.phyrds - nvl(b.phyrds, 0)),
0,
0,
(sum(e.readtim - nvl(b.readtim, 0)) /
sum(e.phyrds - nvl(b.phyrds, 0))) * 10) atpr,
decode(sum(e.phyrds - nvl(b.phyrds, 0)),
0,
to_number(NULL),
sum(e.phyblkrd - nvl(b.phyblkrd, 0)) /
sum(e.phyrds - nvl(b.phyrds, 0))) bpr,
sum(e.phywrts - nvl(b.phywrts, 0)) writes,
sum(e.phywrts - nvl(b.phywrts, 0)) /
(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) wps,
sum(e.wait_count - nvl(b.wait_count, 0)) waits,
decode(sum(e.wait_count - nvl(b.wait_count, 0)),
0,
0,
(sum(e.time - nvl(b.time, 0)) /
sum(e.wait_count - nvl(b.wait_count, 0))) * 10) atpwt,
sum(e.phyrds - nvl(b.phyrds, 0)) +
sum(e.phywrts - nvl(b.phywrts, 0)) ios
from dba_hist_tempstatxs e, dba_hist_tempstatxs b
where b.snap_id(+) = &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.tsname(+) = e.tsname
and b.file#(+) = e.file#
and b.creation_change#(+) = e.creation_change#
and ((e.phyrds - nvl(b.phyrds, 0)) + (e.phywrts - nvl(b.phywrts, 0))) > 0
group by e.tsname
order by ios desc, tsname;
####File IO Stats
select e.tsname,
substr(e.filename, 1, 52) filename,
e.phyrds - nvl(b.phyrds, 0) reads,
(e.phyrds - nvl(b.phyrds, 0)) /
(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) rps, decode((e.phyrds - nvl(b.phyrds, 0)),
0,
to_number(NULL),
((e.readtim - nvl(b.readtim, 0)) /
(e.phyrds - nvl(b.phyrds, 0))) * 10) atpr,
decode((e.phyrds - nvl(b.phyrds, 0)),
0,
to_number(NULL),
(e.phyblkrd - nvl(b.phyblkrd, 0)) /
(e.phyrds - nvl(b.phyrds, 0))) bpr,
e.phywrts - nvl(b.phywrts, 0) writes,
(e.phywrts - nvl(b.phywrts, 0)) /
(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) wps,
e.wait_count - nvl(b.wait_count, 0) waits,
decode((e.wait_count - nvl(b.wait_count, 0)),
0,
0,
((e.time - nvl(b.time, 0)) /
(e.wait_count - nvl(b.wait_count, 0))) * 10) atpwt from dba_hist_filestatxs e, dba_hist_filestatxs b
where b.snap_id(+) = &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.tsname(+) = e.tsname
and b.file#(+) = e.file#
and b.creation_change#(+) = e.creation_change#
and ((e.phyrds - nvl(b.phyrds, 0)) + (e.phywrts - nvl(b.phywrts, 0))) > 0 union all
select e.tsname,
substr(e.filename, 1, 52) filename,
e.phyrds - nvl(b.phyrds, 0) reads,
(e.phyrds - nvl(b.phyrds, 0)) /
(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) rps, decode((e.phyrds - nvl(b.phyrds, 0)),
0,
to_number(NULL),
((e.readtim - nvl(b.readtim, 0)) /
(e.phyrds - nvl(b.phyrds, 0))) * 10) atpr,
decode((e.phyrds - nvl(b.phyrds, 0)),
0,
to_number(NULL),
(e.phyblkrd - nvl(b.phyblkrd, 0)) /
(e.phyrds - nvl(b.phyrds, 0))) bpr,
e.phywrts - nvl(b.phywrts, 0) writes,
(e.phywrts - nvl(b.phywrts, 0)) /
(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) wps,
e.wait_count - nvl(b.wait_count, 0) waits,
decode((e.wait_count - nvl(b.wait_count, 0)),
0,
to_number(NULL),
((e.time - nvl(b.time, 0)) /
(e.wait_count - nvl(b.wait_count, 0))) * 10) atpwt
from dba_hist_tempstatxs e, dba_hist_tempstatxs b
where b.snap_id(+) = &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.tsname(+) = e.tsname
and b.file#(+) = e.file#
and b.creation_change#(+) = e.creation_change#
and ((e.phyrds - nvl(b.phyrds, 0)) + (e.phywrts - nvl(b.phywrts, 0))) > 0
order by tsname, filename;
####Buffer Pool Statistics
select replace(e.block_size / 1024 || 'k',
(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') / 1024 || 'k', substr(e.name, 1, 1)) name,
e.set_msize numbufs,
decode(e.db_block_gets - nvl(b.db_block_gets, 0) + e.consistent_gets -
nvl(b.consistent_gets, 0),
0,
to_number(null),
(100 * (1 - ((e.physical_reads - nvl(b.physical_reads, 0)) /
(e.db_block_gets - nvl(b.db_block_gets, 0) +
e.consistent_gets - nvl(b.consistent_gets, 0)))))) poolhr,
e.db_block_gets - nvl(b.db_block_gets, 0) + e.consistent_gets -
nvl(b.consistent_gets, 0) buffs,
e.physical_reads - nvl(b.physical_reads, 0) phread,
e.physical_writes - nvl(b.physical_writes, 0) phwrite,
e.free_buffer_wait - nvl(b.free_buffer_wait, 0) fbwait,
e.write_complete_wait - nvl(b.write_complete_wait, 0) wcwait,
e.buffer_busy_wait - nvl(b.buffer_busy_wait, 0) bbwait
from dba_hist_buffer_pool_stat b, dba_hist_buffer_pool_stat e
where b.snap_id(+) = &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.id(+) = e.id
order by e.name;
####Instance Recovery Stats
select 'B' beg,
target_mttr tm,
estimated_mttr em,
recovery_estimated_ios rei,
actual_redo_blks arb,
target_redo_blks trb,
log_file_size_redo_blks lfrb,
log_chkpt_timeout_redo_blks lctrb, log_chkpt_interval_redo_blks lcirb, snap_id snid
from dba_hist_instance_recovery b
where b.snap_id = &beg_snap
and b.dbid = &dbid
and b.instance_number = &inst_num
union all
select 'E' beg,
target_mttr tm,
estimated_mttr em,
recovery_estimated_ios rei,
actual_redo_blks arb,
target_redo_blks trb,
log_file_size_redo_blks lfrb,
log_chkpt_timeout_redo_blks lctrb, log_chkpt_interval_redo_blks lcirb, snap_id snid
from dba_hist_instance_recovery e
where e.snap_id = &end_snap
and e.dbid = &dbid
and e.instance_number = &inst_num
order by snid;
####Buffer Pool Advisory
select replace(block_size / 1024 || 'k',
(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') / 1024 || 'k',
substr(name, 1, 1)) name,
size_for_estimate,
size_factor,
buffers_for_estimate,
decode(base_physical_reads,
0,
to_number(null),
round((physical_reads / base_physical_reads), 4))
estd_physical_read_factor,
decode(base_physical_reads,
0,
physical_reads,
round((physical_reads *
(actual_physical_reads / base_physical_reads)),
0)) estd_physical_reads,
decode(e.block_size,
(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'),
1,
2) order_def_bs
from dba_hist_db_cache_advice e
where snap_id = &end_snap
and dbid = &dbid
and instance_number = &inst_num
and physical_reads > 0
order by order_def_bs, block_size, e.name, buffers_for_estimate;
####PGA Aggr Summary
select 100
* (e.bytes - nvl(b.bytes,0))
/ (e.bytes - nvl(b.bytes,0) + e.bytesrw - nvl(b.bytesrw,0))
, (e.bytes - nvl(b.bytes,0)) /1024/1024 tbp
, (e.bytesrw - nvl(b.bytesrw,0))/1024/1024 tbrw
from (select sum(case when name = 'bytes processed'
then value else 0 end) bytes
, sum(case when name = 'extra bytes read/written'
then value else 0 end) bytesrw
from dba_hist_pgastat e1
where e1.snap_id = &end_snap
and e1.dbid = &dbid
and e1.instance_number = &inst_num
and e1.name in ('bytes processed',
'extra bytes read/written')
) e
, (select sum(case when name = 'bytes processed'
then value else 0 end) bytes
, sum(case when name = 'extra bytes read/written'
then value else 0 end) bytesrw
from dba_hist_pgastat b1
where b1.snap_id = &beg_snap
and b1.dbid = &dbid
and b1.instance_number = &inst_num
and b1.name in ('bytes processed',
'extra bytes read/written')
) b
where e.bytes - nvl(b.bytes,0) > 0;
####PGA Aggr Target Stats
select 'B' snap,
to_number(p.value) / 1024 / 1024 pgaat, mu.pat / 1024 / 1024 pat,
mu.PGA_alloc / 1024 / 1024 tot_pga_allo,
(mu.PGA_used_auto + mu.PGA_used_man) / 1024 / 1024 tot_tun_used, 100 * (mu.PGA_used_auto + mu.PGA_used_man) / PGA_alloc pct_tun, decode(mu.PGA_used_auto + mu.PGA_used_man,
0,
0,
100 * mu.PGA_used_auto / (mu.PGA_used_auto
pct_auto_tun,
decode(mu.PGA_used_auto + mu.PGA_used_man,
0,
0,
100 * mu.PGA_used_man / (mu.PGA_used_auto
pct_man_tun,
mu.glob_mem_bnd / 1024 glo_mem_bnd
from (select sum(case
when name = 'total PGA allocated' then
value
else
0
end) PGA_alloc,
sum(case
+ mu.PGA_used_man))
+ mu.PGA_used_man))
when name = 'total PGA used for auto workareas' then value
else
0
end) PGA_used_auto,
sum(case
when name = 'total PGA used for manual workareas' then value
else
0
end) PGA_used_man,
sum(case
when name = 'global memory bound' then value
else
0
end) glob_mem_bnd,
sum(case
when name = 'aggregate PGA auto target' then value
else
0
end) pat
from dba_hist_pgastat pga
where pga.snap_id = &beg_snap
and pga.dbid = &dbid
and pga.instance_number = &inst_num) mu,
dba_hist_parameter p
where p.snap_id = &beg_snap
and p.dbid = &dbid
and p.instance_number = &inst_num
and p.parameter_name = 'pga_aggregate_target' and p.value != '0'
union
select 'E' snap,
to_number(p.value) / 1024 / 1024 pgaat, mu.pat / 1024 / 1024 pat,
mu.PGA_alloc / 1024 / 1024 tot_pga_allo,
(mu.PGA_used_auto + mu.PGA_used_man) / 1024 / 1024 tot_tun_used, 100 * (mu.PGA_used_auto + mu.PGA_used_man) / PGA_alloc pct_tun, decode(mu.PGA_used_auto + mu.PGA_used_man,
0,
0,
100 * mu.PGA_used_auto / (mu.PGA_used_auto
pct_auto_tun,
decode(mu.PGA_used_auto + mu.PGA_used_man,
0,
0,
100 * mu.PGA_used_man / (mu.PGA_used_auto
pct_man_tun,
mu.glob_mem_bnd / 1024 glo_mem_bnd
from (select sum(case
when name = 'total PGA allocated' then
value
else
0
end) PGA_alloc,
sum(case
+ mu.PGA_used_man))
+ mu.PGA_used_man))
when name = 'total PGA used for auto workareas' then value
else
0
end) PGA_used_auto,
sum(case
when name = 'total PGA used for manual workareas' then value
else
0
end) PGA_used_man,
sum(case
when name = 'global memory bound' then
value
else
0
end) glob_mem_bnd,
sum(case
when name = 'aggregate PGA auto target' then
value
else
0
end) pat
from dba_hist_pgastat pga
where pga.snap_id = &end_snap
and pga.dbid = &dbid
and pga.instance_number = &inst_num) mu, dba_hist_parameter p
where p.snap_id = &end_snap
and p.dbid = &dbid
and p.instance_number = &inst_num
and p.parameter_name = 'pga_aggregate_target' and p.value != '0'
order by snap;
####PGA Aggr Target Histogram
select case
when e.low_optimal_size >= 1024 * 1024 * 1024 * 1024 then
lpad(round(e.low_optimal_size / 1024 / 1024 / 1024 / 1024) || 'T',7)
when e.low_optimal_size >= 1024 * 1024 * 1024 then
lpad(round(e.low_optimal_size / 1024 / 1024 / 1024) || 'G', 7) when e.low_optimal_size >= 1024 * 1024 then
lpad(round(e.low_optimal_size / 1024 / 1024) || 'M', 7) when e.low_optimal_size >= 1024 then
lpad(round(e.low_optimal_size / 1024) || 'K', 7)
else
lpad(e.low_optimal_size || ' B ', 7) end low_o,
case
when e.high_optimal_size >= 1024 * 1024 * 1024 * 1024 then
lpad(round(e.high_optimal_size / 1024 / 1024 / 1024 / 1024) || 'T', 7)
when e.high_optimal_size >= 1024 * 1024 * 1024 then
lpad(round(e.high_optimal_size / 1024 / 1024 / 1024) || 'G', 7) when e.high_optimal_size >= 1024 * 1024 then
lpad(round(e.high_optimal_size / 1024 / 1024) || 'M', 7) when e.high_optimal_size >= 1024 then
lpad(round(e.high_optimal_size / 1024) || 'K', 7)
else
e.high_optimal_size || ' B '
end high_o,
e.total_executions - nvl(b.total_executions, 0) tot_e,
e.optimal_executions - nvl(b.optimal_executions, 0) opt_e,
e.onepass_executions - nvl(b.onepass_executions, 0) one_e,
e.multipasses_executions - nvl(b.multipasses_executions, 0) mul_e
from dba_hist_sql_workarea_hstgrm e, dba_hist_sql_workarea_hstgrm b
where e.snap_id = &end_snap
and e.dbid = &dbid
and e.instance_number = &inst_num
and b.snap_id(+) = &beg_snap
and b.dbid(+) = e.dbid
and b.instance_number(+) = e.instance_number
and b.low_optimal_size(+) = e.low_optimal_size
and b.high_optimal_size(+) = e.high_optimal_size
and e.total_executions - nvl(b.total_executions, 0) > 0
order by e.low_optimal_size;
####PGA Memory Advisory
select pga_target_for_estimate / 1024 / 1024 pga_t,
pga_target_factor pga_tf,
bytes_processed / 1024 / 1024 byt_p,
estd_extra_bytes_rw / 1024 / 1024 byt_rw, estd_pga_cache_hit_percentage epchp,
estd_overalloc_count eoc
from dba_hist_pga_target_advice e
where snap_id = &end_snap
and dbid = &dbid
and instance_number = &inst_num
order by pga_target_for_estimate;
####Shared Pool Advisory
select shared_pool_size_for_estimate,
shared_pool_size_factor,
estd_lc_size,
estd_lc_memory_objects,
estd_lc_time_saved,
estd_lc_time_saved_factor,
estd_lc_load_time,
estd_lc_load_time_factor,
estd_lc_memory_object_hits
from dba_hist_shared_pool_advice
where snap_id = &end_snap
and dbid = &dbid
and instance_number = &inst_num
order by shared_pool_size_for_estimate;
####SGA Target Advisory
select sga_size, sga_size_factor, estd_db_time,
from dba_hist_sga_target_advice e
where snap_id = &end_snap
and dbid = &dbid
and instance_number = &inst_num
order by sga_size;
####Streams Pool Advisory
select size_for_estimate,
size_factor,
estd_spill_count,
estd_spill_time,
estd_unspill_count,
estd_unspill_time
from dba_hist_streams_pool_advice e where snap_id = &end_snap
and dbid = &dbid
and instance_number = &inst_num
order by size_factor;
####Java Pool Advisory
select java_pool_size_for_estimate,
java_pool_size_factor,
estd_lc_size,
estd_lc_memory_objects,
estd_lc_time_saved,
nvl(estd_lc_time_saved_factor, 0),
estd_lc_load_time,
nvl(estd_lc_load_time_factor, 0),
estd_lc_memory_object_hits
from dba_hist_java_pool_advice
where snap_id = &end_snap
and dbid = &dbid
and instance_number = &inst_num
and estd_lc_memory_objects > 0
order by java_pool_size_for_estimate;
####Buffer Wait Statistics
estd_physical_reads
select e.class class,
e.wait_count - nvl(b.wait_count, 0) icnt,
(e.time - nvl(b.time, 0)) / 100 itim,
10 * (e.time - nvl(b.time, 0)) /
(e.wait_count - nvl(b.wait_count, 0)) iavg
from dba_hist_waitstat b, dba_hist_waitstat e where b.snap_id = &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.class = e.class
and b.wait_count < e.wait_count
order by itim desc, icnt desc, class;
####Enqueue Activity
select
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
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,
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) <
n.subobject_name
else
owner, subobject_name)
11 then
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) *
e.pins - b.pins pins,
to_number(decode(e.pins,
b.pins,
null,
100 -
(e.pinhits - b.pinhits) *
e.reloads - b.reloads reloads,
e.invalidations - b.invalidations inv
100 / (e.gets - b.gets))) getm,
100 / (e.pins - b.pins))) pinm,
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;
####Streams CPU/IO Usage
select e.session_type,
e.sum_cpu_time - nvl(b.sum_cpu_time, 0) cpu,
e.sum_user_io_wait - nvl(b.sum_user_io_wait, 0),
e.sum_sys_io_wait - nvl(b.sum_sys_io_wait, 0)
from dba_hist_sess_time_stats b, dba_hist_sess_time_stats e where b.snap_id(+) = &beg_snap
and e.snap_id = &end_snap
and b.dbid(+) = e.dbid
and e.dbid = &dbid
and b.instance_number(+) = e.instance_number and e.instance_number = &inst_num
and b.min_logon_time(+) = e.min_logon_time
and b.session_type(+) = e.session_type
order by cpu desc;
####Streams Capture
select e.capture_name capname,
(e.total_messages_captured - nvl(b.total_messages_captured, 0)) /
(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),
(e.total_messages_enqueued - nvl(b.total_messages_enqueued, 0)) /
(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), (e.lag - nvl(b.lag, 0)) * 24 * 60 * 60,
(e.elapsed_rule_time - nvl(b.elapsed_rule_time, 0)) /
(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),
(e.elapsed_enqueue_time - nvl(b.elapsed_enqueue_time, 0)) /
(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),
(e.elapsed_redo_wait_time - nvl(b.elapsed_redo_wait_time, 0)) /
(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),
(e.elapsed_pause_time - nvl(b.elapsed_pause_time, 0)) /
(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)
from dba_hist_streams_capture b, dba_hist_streams_capture e
where b.snap_id(+) = &beg_snap
and e.snap_id = &end_snap
and b.dbid(+) = e.dbid
and e.dbid = &dbid
and b.instance_number(+) = e.instance_number and e.instance_number = &inst_num
and b.startup_time(+) = e.startup_time
and b.capture_name(+) = e.capture_name
order by capname;
####Streams Apply
select e.apply_name appname,
(e.coord_total_applied - nvl(b.coord_total_applied, 0)) /
(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), 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 in ('user rollbacks', 'user commits')
and b.STAT_NAME in ('user rollbacks', 'user commits')),
0,
0,
100 * (e.coord_total_applied - nvl(b.coord_total_applied, 0)) / (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'))),
decode((e.coord_total_applied - nvl(b.coord_total_applied, 0)),
0,
0,
((e.coord_total_wait_deps - nvl(b.coord_total_wait_deps, 0)) * 100)
/
(e.coord_total_applied - nvl(b.coord_total_applied, 0))),
decode((e.coord_total_applied - nvl(b.coord_total_applied, 0)),
0,
0,
((e.coord_total_wait_cmts - nvl(b.coord_total_wait_cmts, 0)) * 100)
/
(e.coord_total_applied - nvl(b.coord_total_applied, 0))),
decode((e.coord_total_applied - nvl(b.coord_total_applied, 0)),
0,
0,
((e.coord_total_rollbacks - nvl(b.coord_total_rollbacks, 0)) * 100)
/
(e.coord_total_applied - nvl(b.coord_total_applied, 0))), (e.server_total_messages_applied -
nvl(b.server_total_messages_applied, 0)) /
(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),
decode(e.server_total_messages_applied -
nvl(b.server_total_messages_applied, 0),
0,
0,
10 * (e.server_elapsed_dequeue_time -
nvl(b.server_elapsed_dequeue_time, 0)) /
(e.server_total_messages_applied -
nvl(b.server_total_messages_applied, 0))),
decode(e.server_total_messages_applied -
nvl(b.server_total_messages_applied, 0),
0,
0,
10 * (e.server_elapsed_apply_time -
nvl(b.server_elapsed_apply_time, 0)) /
(e.server_total_messages_applied -
nvl(b.server_total_messages_applied, 0))),
(e.coord_lwm_lag - nvl(b.coord_lwm_lag, 0)) * 24 * 60 * 60
from dba_hist_streams_apply_sum b, dba_hist_streams_apply_sum 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.startup_time(+) = e.startup_time
and b.apply_name(+) = e.apply_name
order by appname;
####Buffered Queues
select e.queue_schema || ' . ' || e.queue_name queuename,
(e.cnum_msgs - nvl(b.cnum_msgs, 0)) /
(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),
(e.cnum_msgs - e.num_msgs - nvl((b.cnum_msgs - b.num_msgs), 0)) /
(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),
(e.cspill_msgs - nvl(b.cspill_msgs, 0)) /
(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) spillrate,
(decode(e.num_msgs, 0, 0, (e.spill_msgs / e.num_msgs)) -
nvl(decode(b.num_msgs, 0, 0, (b.spill_msgs / b.num_msgs)), 0)) * 100
from dba_hist_buffered_queues b, dba_hist_buffered_queues 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.queue_schema(+) = e.queue_schema
and b.queue_name(+) = e.queue_name
order by spillrate desc, queuename;
####Buffered Subscribers
select decode(e.subscriber_type,
'PROXY',
'PROXY: ' || e.subscriber_address,
e.subscriber_name) subsname,
(e.cnum_msgs - nvl(b.cnum_msgs, 0)) /
(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),
(e.cnum_msgs - e.num_msgs - nvl((b.cnum_msgs - b.num_msgs), 0)) /
(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),
(e.total_spilled_msg - nvl(b.total_spilled_msg, 0)) /
(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) spillrate
from dba_hist_buffered_subscribers b, dba_hist_buffered_subscribers 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.subscriber_id(+) = e.subscriber_id
order by spillrate desc, subsname;
####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 CR Served Stats
select 'CR Block Requests', e.cr_requests - b.cr_requests
from dba_hist_cr_block_server b,
dba_hist_cr_block_server 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
union all
select 'CURRENT Block Requests', e.current_requests - b.current_requests
from dba_hist_cr_block_server b,
dba_hist_cr_block_server 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
union all
select 'Data Block Requests', e.data_requests - b.data_requests
from dba_hist_cr_block_server b,
dba_hist_cr_block_server 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
union all
select 'Undo Block Requests', e.undo_requests - b.undo_requests
from dba_hist_cr_block_server b,
dba_hist_cr_block_server 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
union all
select 'TX Block Requests', e.tx_requests -
from dba_hist_cr_block_server b,
dba_hist_cr_block_server 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
union all
select 'Current Results', e.current_results
from dba_hist_cr_block_server b,
dba_hist_cr_block_server 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
union all
select 'Private results', e.private_results
from dba_hist_cr_block_server b,
dba_hist_cr_block_server 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
union all
b.tx_requests
- b.current_results
- b.private_results
select 'Zero Results', e.zero_results - b.zero_results
from dba_hist_cr_block_server b,
dba_hist_cr_block_server 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
union all
select 'Disk Read Results', e.disk_read_results -b.disk_read_results
from dba_hist_cr_block_server b,
dba_hist_cr_block_server 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
union all
select 'Fail Results', e.fail_results - b.fail_results
from dba_hist_cr_block_server b,
dba_hist_cr_block_server 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
union all
select 'Fairness Down Converts',
e.fairness_down_converts - b.fairness_down_converts from dba_hist_cr_block_server b,
dba_hist_cr_block_server 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
union all
select 'Fairness Clears', e.fairness_clears - b.fairness_clears
from dba_hist_cr_block_server b,
dba_hist_cr_block_server 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
union all
select 'Free GC Elements', e.free_gc_elements - b.free_gc_elements
from dba_hist_cr_block_server b,
dba_hist_cr_block_server 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
union all
select 'Flushes', e.flushes - b.flushes
from dba_hist_cr_block_server b,
dba_hist_cr_block_server 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
union all
select 'Flushes Queued', e.flushes_queued - b.flushes_queued
from dba_hist_cr_block_server b,
dba_hist_cr_block_server 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
union all
select 'Flush Queue Full', e.flush_queue_full - b.flush_queue_full
from dba_hist_cr_block_server b,
dba_hist_cr_block_server 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
union all
select 'Flush Max Time (us)', e.flush_max_time - b.flush_max_time
from dba_hist_cr_block_server b,
dba_hist_cr_block_server 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
union all
select 'Light Works', e.light_works - b.light_works
from dba_hist_cr_block_server b,
dba_hist_cr_block_server 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
union all
select 'Errors', e.errors - b.errors
from dba_hist_cr_block_server b,
dba_hist_cr_block_server 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;
####Global CURRENT Served Stats
select 'Pins', pins,
decode(pins,0,0,100*pin1/pins),
decode(pins,0,0,100*pin10/pins),
decode(pins,0,0,100*pin100/pins),
decode(pins,0,0,100*pin1000/pins),
decode(pins,0,0,100*pin10000/pins)
from (select (e.pin1+e.pin10+e.pin100+e.pin1000+e.pin10000 -
(b.pin1+b.pin10+b.pin100+b.pin1000+b.pin10000)) pins,
e.pin1 - b.pin1 pin1,
e.pin10 - b.pin10 pin10,
e.pin100 - b.pin100 pin100,
e.pin1000 - b.pin1000 pin1000,
e.pin10000 - b.pin10000 pin10000
from dba_hist_current_block_server b
, dba_hist_current_block_server 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)
union all
select 'Flushes', flushes,
decode(flushes,0,0,100*flush1/flushes),
decode(flushes,0,0,100*flush10/flushes),
decode(flushes,0,0,100*flush100/flushes),
decode(flushes,0,0,100*flush1000/flushes),
decode(flushes,0,0,100*flush10000/flushes)
from (select
(e.flush1+e.flush10+e.flush100+e.flush1000+e.flush10000 -
(b.flush1+b.flush10+b.flush100+b.flush1000+b.flush10000)) flushes,
e.flush1 - b.flush1 flush1,
e.flush10 - b.flush10 flush10,
e.flush100 - b.flush100 flush100,
e.flush1000 - b.flush1000 flush1000,
e.flush10000 - b.flush10000 flush10000
from dba_hist_current_block_server b
, dba_hist_current_block_server 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)
union all
select 'Writes', writes,
decode(writes,0,0,100*write1/writes),
decode(writes,0,0,100*write10/writes),
decode(writes,0,0,100*write100/writes),
decode(writes,0,0,100*write1000/writes),
decode(writes,0,0,100*write10000/writes)
from (select
(e.write1+e.write10+e.write100+e.write1000+e.write10000 -
(b.write1+b.write10+b.write100+b.write1000+b.write10000)) writes,
e.write1 - b.write1 write1,
e.write10 - b.write10 write10,
e.write100 - b.write100 write100,
e.write1000 - b.write1000 write1000,
e.write10000 - b.write10000 write10000
from dba_hist_current_block_server b
, dba_hist_current_block_server 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);
####Global Cache Transfer Stats
select inst,
class,
crtotal,
100 * decode(crtotal, 0, to_number(null),
100 * decode(crtotal, 0, to_number(null),
100 * decode(crtotal, 0, to_number(null),
cutotal,
100 * decode(cutotal, 0, to_number(null),
100 * decode(cutotal, 0, to_number(null),
100 * decode(cutotal, 0, to_number(null), from (select e.instance inst,
e.class class,
crblk / crtotal),
crbusy / crtotal),
crcong / crtotal),
cublk / cutotal),
cubusy / cutotal),
cucong / cutotal)
e.cr_block - nvl(b.cr_block, 0) crblk,
e.cr_busy - nvl(b.cr_busy, 0) crbusy,
e.cr_congested - nvl(b.cr_congested, 0) crcong,
(e.cr_block - nvl(b.cr_block, 0) + e.cr_busy -
nvl(b.cr_busy, 0) + e.cr_congested - nvl(b.cr_congested, 0))
crtotal,
e.current_block - nvl(b.current_block, 0) cublk,
e.current_busy - nvl(b.current_busy, 0) cubusy,
e.current_congested - nvl(b.current_congested, 0) cucong,
(e.current_block - nvl(b.current_block, 0) + e.current_busy -
nvl(b.current_busy, 0) + e.current_congested -
nvl(b.current_congested, 0)) cutotal
from dba_hist_inst_cache_transfer b,
dba_hist_inst_cache_transfer e
where b.snap_id(+) = &beg_snap
and e.snap_id = &end_snap
and b.instance_number(+) = &inst_num
and e.instance_number = &inst_num
and b.dbid(+) = &dbid
and e.dbid = &dbid
and b.class(+) = e.class
and b.instance(+) = e.instance
and (e.cr_block + e.current_block + e.cr_busy + e.current_busy +
e.cr_congested + e.current_congested) -
(nvl(b.cr_block, 0) + nvl(b.current_block, 0) +
nvl(b.cr_busy, 0) + nvl(b.cr_congested, 0) +
nvl(b.current_busy, 0) + nvl(b.current_congested, 0)) > 0)
order by crtotal + cutotal desc, class;
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
