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

一时失言乱红尘 2023-01-18 15:23 16阅读 0赞

以下脚本可以用于列出最近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