天萃荷净
在日常的性能监控中,我们经常需要需要通过一段时间内数据库的逻辑读/物理读来大致反映数据库的性能情况,这里列出通过awr和statspack来获取相关数据(查询最近30天数据,除掉第一条和最后一条数据)
1.Oracle awr逻辑读
WITH A AS
(SELECT B.SNAP_ID, SUM(VALUE)/1024/1024/1024*8192 VALUE
FROM DBA_HIST_SYSSTAT B
WHERE B.DBID = (SELECT DBID FROM V$DATABASE)
AND b.INSTANCE_NUMBER = &INST_NUM
AND B.STAT_NAME IN ('session logical reads')
GROUP BY B.SNAP_ID
ORDER BY SNAP_ID)
SELECT A.SNAP_ID,
LAG(VALUE, 1, '0') OVER(ORDER BY A.SNAP_ID) "START_VALUE(G)",
VALUE "END_VALUE(G)",
TO_CHAR(END_INTERVAL_TIME,'YYYY-MM-DD HH24:MI:SS')"END_TIME",
VALUE - LAG(VALUE, 1, '0') OVER(ORDER BY A.SNAP_ID) "D-VALUE(G)"
FROM A,(SELECT END_INTERVAL_TIME,SNAP_ID
FROM DBA_HIST_SNAPSHOT B
WHERE B.DBID = (SELECT dbid FROM v$database)
AND B.INSTANCE_NUMBER = &INST_NUM) B
WHERE A.SNAP_ID=B.snap_id
AND END_INTERVAL_TIME>=SYSDATE-30;
2.Oracle awr物理读
WITH A AS
(SELECT B.SNAP_ID, SUM(VALUE)/1024/1024/1024*8192 VALUE
FROM DBA_HIST_SYSSTAT B
WHERE B.DBID = (SELECT DBID FROM V$DATABASE)
AND b.INSTANCE_NUMBER = &INST_NUM
AND B.STAT_NAME IN ('physical reads')
GROUP BY B.SNAP_ID
ORDER BY SNAP_ID)
SELECT A.SNAP_ID,
LAG(VALUE, 1, '0') OVER(ORDER BY A.SNAP_ID) "START_VALUE(G)",
VALUE "END_VALUE(G)",
TO_CHAR(END_INTERVAL_TIME,'YYYY-MM-DD HH24:MI:SS')"END_TIME",
VALUE - LAG(VALUE, 1, '0') OVER(ORDER BY A.SNAP_ID) "D-VALUE(G)"
FROM A,(SELECT END_INTERVAL_TIME,SNAP_ID
FROM DBA_HIST_SNAPSHOT B
WHERE B.DBID = (SELECT dbid FROM v$database)
AND B.INSTANCE_NUMBER = &INST_NUM) B
WHERE A.SNAP_ID=B.snap_id
AND END_INTERVAL_TIME>=SYSDATE-30;
3.Oracle statspack逻辑读
WITH A AS
(SELECT B.SNAP_ID, SUM(VALUE)/1024/1024/1024*8192 VALUE
FROM STATS$SYSSTAT B
WHERE B.DBID = (SELECT DBID FROM V$DATABASE)
AND B.INSTANCE_NUMBER = &INST_NUM
AND B.NAME IN ('session logical reads')
GROUP BY B.SNAP_ID
ORDER BY SNAP_ID)
SELECT A.SNAP_ID,
LAG(VALUE, 1, '0') OVER(ORDER BY A.SNAP_ID) "START_VALUE(G)",
VALUE "END_VALUE(G)",
TO_CHAR(SNAP_TIME, 'YYYY-MM-DD HH24:MI:SS') "END_TIME",
VALUE - LAG(VALUE, 1, '0') OVER(ORDER BY A.SNAP_ID) "D-VALUE(G)"
FROM A,
(SELECT SNAP_TIME, SNAP_ID
FROM STATS$SNAPSHOT B
WHERE B.DBID = (SELECT DBID FROM V$DATABASE)
AND B.INSTANCE_NUMBER = &INST_NUM) B
WHERE A.SNAP_ID = B.SNAP_ID
AND snap_time>=SYSDATE-30;
4.Oracle statspack物理读
WITH A AS
(SELECT B.SNAP_ID, SUM(VALUE)/1024/1024/1024*8192 VALUE
FROM STATS$SYSSTAT B
WHERE B.DBID = (SELECT DBID FROM V$DATABASE)
AND B.INSTANCE_NUMBER = &INST_NUM
AND B.NAME IN ('physical reads')
GROUP BY B.SNAP_ID
ORDER BY SNAP_ID)
SELECT A.SNAP_ID,
LAG(VALUE, 1, '0') OVER(ORDER BY A.SNAP_ID) "START_VALUE(G)",
VALUE "END_VALUE(G)",
TO_CHAR(SNAP_TIME, 'YYYY-MM-DD HH24:MI:SS') "END_TIME",
VALUE - LAG(VALUE, 1, '0') OVER(ORDER BY A.SNAP_ID) "D-VALUE(G)"
FROM A,
(SELECT SNAP_TIME, SNAP_ID
FROM STATS$SNAPSHOT B
WHERE B.DBID = (SELECT DBID FROM V$DATABASE)
AND B.INSTANCE_NUMBER = &INST_NUM) B
WHERE A.SNAP_ID = B.SNAP_ID
AND snap_time>=SYSDATE-30;
--------------------------------------ORACLE-DBA----------------------------------------
最权威、专业的Oracle案例资源汇总之【学习笔记】Oracle AWR 通过sql语句获取awr/statspack逻辑读/物理读