图表化statspack结果

图表化statspack结果

===========================================================

作者: xsb(http://xsb.itpub.net)
发表于:2005.08.22 09:16
分类: Oracle
出处：http://xsb.itpub.net/post/419/38714
---------------------------------------------------------------

图表化statspack结果
1． 物理读写IO 操作：
观察物理IO 访问，可以看出数据库日常访问的峰值及繁忙程度。
脚本：此脚本按时间生成统计数据(注:以下示例以8i 为基础,SQL 脚本中引用的statistic#在不同
版本代表的意义可能不同,对于9i 等版本,你应该修改相应参数值)
SELECT SUBSTR(TO_CHAR(SNAP_TIME, 'yyyy-mm-dd HH24:MI:SS'), 12),
(NEWREADS.VALUE - OLDREADS.VALUE) READS,
(NEWWRITES.VALUE - OLDWRITES.VALUE) WRITES
FROM PERFSTAT.STATS$SYSSTAT OLDREADS, PERFSTAT.STATS$SYSSTAT NEWREADS,
PERFSTAT.STATS$SYSSTAT OLDWRITES, PERFSTAT.STATS$SYSSTAT NEWWRITES,
PERFSTAT.STATS$SNAPSHOT SN
WHERE NEWREADS.SNAP_ID = SN.SNAP_ID
AND NEWWRITES.SNAP_ID = SN.SNAP_ID
AND OLDREADS.SNAP_ID = SN.SNAP_ID - 1
AND OLDWRITES.SNAP_ID = SN.SNAP_ID - 1
AND OLDREADS.STATISTIC# = 42--40 --42 physical reads
AND NEWREADS.STATISTIC# = 42 --40 --42
AND OLDWRITES.STATISTIC# = 46 --41 --46 physical writes
AND NEWWRITES.STATISTIC# = 46 --41 --46
AND (NEWREADS.VALUE - OLDREADS.VALUE) > 0
AND (NEWWRITES.VALUE - OLDWRITES.VALUE) > 0
2． Buffer 命中率
SELECT SUBSTR(TO_CHAR(SNAP_TIME, 'yyyy-mm-dd HH24:MI'), 12),
ROUND(100 * (((A.VALUE - E.VALUE) + (B.VALUE - F.VALUE)) -
(C.VALUE - G.VALUE)) /
((A.VALUE - E.VALUE) + (B.VALUE - F.VALUE))) "BUFFER HIT RATIO"
FROM PERFSTAT.STATS$SYSSTAT A, PERFSTAT.STATS$SYSSTAT B,
PERFSTAT.STATS$SYSSTAT C, PERFSTAT.STATS$SYSSTAT E,
PERFSTAT.STATS$SYSSTAT F, PERFSTAT.STATS$SYSSTAT G,
PERFSTAT.STATS$SNAPSHOT SN
WHERE A.SNAP_ID = SN.SNAP_ID
AND B.SNAP_ID = SN.SNAP_ID
AND C.SNAP_ID = SN.SNAP_ID
AND D.SNAP_ID = SN.SNAP_ID
AND E.SNAP_ID = SN.SNAP_ID - 1
AND F.SNAP_ID = SN.SNAP_ID - 1
AND G.SNAP_ID = SN.SNAP_ID - 1
AND A.STATISTIC# = 41 --39 --41 consistent gets
AND E.STATISTIC# = 41 --39
AND B.STATISTIC# = 40 --38 --40 db block gets
AND F.STATISTIC# = 40 --38
AND C.STATISTIC# = 42 --40 --42 physical reads 
AND G.STATISTIC# = 42 --40