明察秋毫 oracle awr,【学习笔记】Oracle AWR 通过sql语句获取awr/statspack逻辑读/物理读...

天萃荷净

在日常的性能监控中，我们经常需要需要通过一段时间内数据库的逻辑读/物理读来大致反映数据库的性能情况，这里列出通过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逻辑读/物理读