列出Oracle每小时redo重做日志产生量
WITH times AS
(SELECT
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
FROM v$archived_log a,(
SELECT
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
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
HOUR_END_TIME SIZE_MB INSTANCE_NAME
1 2021/8/15 9:00:00 406.035 xspaasa1
2 2021/8/15 10:00:00 406.035 xspaasa1
3 2021/8/15 11:00:00 419.648 xspaasa1
4 2021/8/15 12:00:00 514.944 xspaasa1
5 2021/8/15 13:00:00 514.944 xspaasa1
6 2021/8/15 14:00:00 430.91 xspaasa1
7 2021/8/15 15:00:00 426.848 xspaasa1
8 2021/8/15 16:00:00 426.848 xspaasa1
9 2021/8/15 17:00:00 634.307 xspaasa1
10 2021/8/15 18:00:00 1074.596 xspaasa1
11 2021/8/15 19:00:00 451.45 xspaasa1
12 2021/8/15 20:00:00 244.308 xspaasa1
13 2021/8/15 21:00:00 238.522 xspaasa1
14 2021/8/15 22:00:00 238.522 xspaasa1
15 2021/8/15 23:00:00 238.522 xspaasa1
16 2021/8/16 238.522 xspaasa1
17 2021/8/16 1:00:00 238.522 xspaasa1
18 2021/8/16 2:00:00 1351.084 xspaasa1
19 2021/8/16 3:00:00 786.666 xspaasa1
20 2021/8/16 4:00:00 656.934 xspaasa1
21 2021/8/16 5:00:00 1935.07 xspaasa1
22 2021/8/16 6:00:00 323.107 xspaasa1
23 2021/8/16 7:00:00 323.107 xspaasa1
24 2021/8/16 8:00:00 649.749 xspaasa1
25 2021/8/16 9:00:00 665.539 xspaasa1