oracle 重做日志量,Script:列出Oracle每小时的redo重做日志产生量

以下脚本可以用于列出最近Oracle数据库每小时估算的redo重做日志产生量，因为估算数据来源于archivelog的产生量和大小，所以数据是近似值，可供参考:

WITH times AS

(SELECT /*+ MATERIALIZE */

hour_end_time

FROM (SELECT (TRUNC(SYSDATE, 'HH') + (2 / 24)) - (ROWNUM / 24) hour_end_time

FROM DUAL

CONNECT BY ROWNUM <= (1 * 24) + 3),

v$database

WHERE log_mode = 'ARCHIVELOG')

SELECT hour_end_time, NVL(ROUND(SUM(size_mb), 3), 0) size_mb, i.instance_name

FROM(

SELECT hour_end_time, CASE WHEN(hour_end_time - (1 / 24)) > lag_next_time THEN(next_time + (1 / 24) - hour_end_time) * (size_mb / (next_time - lag_next_time)) ELSE 0 END + CASE WHEN hour_end_time < lead_next_time THEN(hour_end_time - next_time) * (lead_size_mb / (lead_next_time - next_time)) ELSE 0 END + CASE WHEN lag_next_time > (hour_end_time - (1 / 24)) THEN size_mb ELSE 0 END + CASE WHEN next_time IS NULL THEN(1 / 24) * LAST_VALUE(CASE WHEN next_time IS NOT NULL AND lag_next_time IS NULL THEN 0 ELSE(size_mb / (next_time - lag_next_time)) END IGNORE NULLS) OVER(

ORDER BY hour_end_time DESC, next_time DESC) ELSE 0 END size_mb

FROM(

SELECT t.hour_end_time, arc.next_time, arc.lag_next_time, LEAD(arc.next_time) OVER(

ORDER BY arc.next_time ASC) lead_next_time, arc.size_mb, LEAD(arc.size_mb) OVER(

ORDER BY arc.next_time ASC) lead_size_mb

FROM times t,(

SELECT next_time, size_mb, LAG(next_time) OVER(

ORDER BY next_time) lag_next_time

FROM(

SELECT next_time, SUM(size_mb) size_mb

FROM(

SELECT DISTINCT a.sequence#, a.next_time, ROUND(a.blocks * a.block_size / 1024 / 1024) size_mb

FROM v$archived_log a,(

SELECT /*+ no_merge */

CASE WHEN TO_NUMBER(pt.VALUE) = 0 THEN 1 ELSE TO_NUMBER(pt.VALUE) END VALUE

FROM v$parameter pt

WHERE pt.name = 'thread') pt

WHERE a.next_time > SYSDATE - 3 AND a.thread# = pt.VALUE AND ROUND(a.blocks * a.block_size / 1024 / 1024) > 0)

GROUP BY next_time)) arc

WHERE t.hour_end_time = (TRUNC(arc.next_time(+), 'HH') + (1 / 24)))

WHERE hour_end_time > TRUNC(SYSDATE, 'HH') - 1 - (1 / 24)), v$instance i

WHERE hour_end_time <= TRUNC(SYSDATE, 'HH')

GROUP BY hour_end_time, i.instance_name

ORDER BY hour_end_time

/ Sample Output:

HOUR_END_TIME SIZE_MB INSTANCE_NAME

------------- ---------- ----------------

2011/9/29 1:0 2.92 VPROD1

2011/9/29 2:0 2.92 VPROD1

2011/9/29 3:0 2.92 VPROD1

2011/9/29 4:0 2.92 VPROD1

2011/9/29 5:0 2.92 VPROD1

2011/9/29 6:0 2.92 VPROD1

2011/9/29 7:0 2.92 VPROD1

2011/9/29 8:0 2.92 VPROD1

2011/9/29 9:0 2.92 VPROD1

2011/9/29 10: 2.92 VPROD1

2011/9/29 11: 2.92 VPROD1

2011/9/29 12: 3.537 VPROD1

2011/9/29 13: 3.55 VPROD1

2011/9/29 14: 3.55 VPROD1

2011/9/29 15: 3.55 VPROD1

2011/9/29 16: 3.55 VPROD1

2011/9/29 17: 3.55 VPROD1

2011/9/29 18: 3.55 VPROD1

2011/9/29 19: 3.55 VPROD1

2011/9/29 20: 3.55 VPROD1