【转载】查看最近的redo生成量
2016-11-30 14:15 AlfredZhao 阅读(797) 评论(0) 编辑 收藏 举报用下面的sql查看最近的redo生成量:
SELECT END_TIME,
INSTANCE_NUMBER,
round(redo_size/1024,2) redo_size_kb
FROM
(SELECT /*+ RULE */ TO_CHAR(C.END_INTERVAL_TIME, 'YYYY-MM-DD HH24:MI') END_TIME,
A.INSTANCE_NUMBER INSTANCE_NUMBER,
CASE
WHEN SUM(A.VALUE-B.VALUE) > 0 THEN ROUND(SUM((A.VALUE-B.VALUE)/C.INTERVAL_2_S))
ELSE 0
END redo_size
FROM DBA_HIST_SYSSTAT A, DBA_HIST_SYSSTAT B,
(SELECT /*+ RULE */ SNAP_ID,
INSTANCE_NUMBER,
END_INTERVAL_TIME,
(CAST(END_INTERVAL_TIME AS DATE) - CAST(BEGIN_INTERVAL_TIME AS DATE))*86400 INTERVAL_2_S
FROM DBA_HIST_SNAPSHOT
WHERE --END_INTERVAL_TIME BETWEEN TO_DATE('201508211000', 'YYYYMMDDHH24MI') AND TO_DATE('201508211100', 'YYYYMMDDHH24MI')
END_INTERVAL_TIME BETWEEN sysdate-30 AND sysdate
--SNAP_ID in (31504,31505)
) C
WHERE A.STAT_NAME IN ('redo size')
AND B.STAT_ID = A.STAT_ID
AND A.SNAP_ID = B.SNAP_ID + 1
AND A.SNAP_ID = C.SNAP_ID
AND B.DBID = A.DBID
AND A.INSTANCE_NUMBER = B.INSTANCE_NUMBER
AND A.INSTANCE_NUMBER = C.INSTANCE_NUMBER
--AND A.INSTANCE_NUMBER = 1
GROUP BY C.END_INTERVAL_TIME,A.INSTANCE_NUMBER
)
ORDER BY 2,1;
AlfredZhao©版权所有「从Oracle起航,领略精彩的IT技术。」