Oracle Archived Redo Volume by Minute
Displays archived redo log volume by minute for a specified number of minutes from the current time. Useful for fine-grained workload analysis and identifying short-term redo spikes.
oraclesqlcapacity-planningv1.0.0
0 stars0 downloads14 views0 comments
By OracleDba • Created
Code
(24 lines)123456789101112131415161718192021222324
-- -----------------------------------------------------------------------------------
-- File Name : https://oracle-base.com/dba/monitoring/redo_by_min.sql
-- Author : Tim Hall
-- Description : Lists the volume of archived redo by min for the specified number of hours.
-- Call Syntax : @redo_by_min (N number of minutes from now)
-- Requirements : Access to the v$views.
-- Last Modified: 11/10/2013
-- -----------------------------------------------------------------------------------
SET VERIFY OFF PAGESIZE 100
WITH mins AS (
SELECT TRUNC(SYSDATE, 'MI') - (&1/(24*60)) + ((level-1)/(24*60)) AS mins
FROM dual
CONNECT BY level <= &1
)
SELECT m.mins AS date_min,
ROUND(SUM(blocks * block_size)/1024/1024,2) size_mb
FROM mins m
LEFT OUTER JOIN v$archived_log al ON m.mins = TRUNC(al.first_time, 'MI')
GROUP BY m.mins
ORDER BY m.mins;
SET VERIFY ON PAGESIZE 14