OracleRedo & archiving

Redo generated per hour

Actual redo volume per hour for the last 7 days, from archived log sizes on the local destination, so early log switches and Data Guard copies don't distort it. The second query lists the five busiest hours per thread: that figure is what the Redo log sizing tool asks for.

Not yet verified. How scripts are tested

ora-redo-per-hour.sql

1CLEAR COLUMNS2SET LINESIZE 120 PAGESIZE 250 TRIMOUT ON TAB OFF3COLUMN hour    HEADING "HOUR"    FORMAT A164COLUMN thread  HEADING "THREAD"  FORMAT 9905COLUMN logs    HEADING "LOGS"    FORMAT 9,9906COLUMN redo_gb HEADING "REDO GB" FORMAT 99,990.997 8SELECT TO_CHAR(TRUNC(first_time, 'HH24'), 'YYYY-MM-DD HH24:MI') AS hour,9       COUNT(*) AS logs,10       ROUND(SUM(blocks * block_size) / POWER(1024, 3), 2) AS redo_gb11FROM v$archived_log12WHERE first_time > TRUNC(SYSDATE) - 713  AND dest_id = 114GROUP BY TRUNC(first_time, 'HH24')15ORDER BY TRUNC(first_time, 'HH24');16 17PROMPT18PROMPT Busiest hours per thread (enter the top one in the Redo log sizing tool)19 20SELECT hour, thread, logs, redo_gb21FROM (SELECT TO_CHAR(TRUNC(first_time, 'HH24'), 'YYYY-MM-DD HH24:MI') AS hour,22             thread# AS thread,23             COUNT(*) AS logs,24             ROUND(SUM(blocks * block_size) / POWER(1024, 3), 2) AS redo_gb25      FROM v$archived_log26      WHERE first_time > TRUNC(SYSDATE) - 727        AND dest_id = 128      GROUP BY TRUNC(first_time, 'HH24'), thread#29      ORDER BY redo_gb DESC)30WHERE ROWNUM <= 5;

CLEAR COLUMNS
SET LINESIZE 120 PAGESIZE 250 TRIMOUT ON TAB OFF
COLUMN hour    HEADING "HOUR"    FORMAT A16
COLUMN thread  HEADING "THREAD"  FORMAT 990
COLUMN logs    HEADING "LOGS"    FORMAT 9,990
COLUMN redo_gb HEADING "REDO GB" FORMAT 99,990.99

SELECT TO_CHAR(TRUNC(first_time, 'HH24'), 'YYYY-MM-DD HH24:MI') AS hour,
       COUNT(*) AS logs,
       ROUND(SUM(blocks * block_size) / POWER(1024, 3), 2) AS redo_gb
FROM v$archived_log
WHERE first_time > TRUNC(SYSDATE) - 7
  AND dest_id = 1
GROUP BY TRUNC(first_time, 'HH24')
ORDER BY TRUNC(first_time, 'HH24');

PROMPT
PROMPT Busiest hours per thread (enter the top one in the Redo log sizing tool)

SELECT hour, thread, logs, redo_gb
FROM (SELECT TO_CHAR(TRUNC(first_time, 'HH24'), 'YYYY-MM-DD HH24:MI') AS hour,
             thread# AS thread,
             COUNT(*) AS logs,
             ROUND(SUM(blocks * block_size) / POWER(1024, 3), 2) AS redo_gb
      FROM v$archived_log
      WHERE first_time > TRUNC(SYSDATE) - 7
        AND dest_id = 1
      GROUP BY TRUNC(first_time, 'HH24'), thread#
      ORDER BY redo_gb DESC)
WHERE ROWNUM <= 5;

Save it as ora-redo-per-hour.sql and run it with SQL> @ora-redo-per-hour.

Open in denrepo Related tool: Redo log sizing

More Oracle scripts: Redo & archiving

Online redo log groups and membersEvery redo log group with its thread, sequence, archived flag, status, member file and size. More than one CURRENT per thread, or members missing…
Log switches per hour, last 7 daysA day-by-hour grid of log switches. Aim for roughly four an hour at peak; consistently more means the redo logs are too small.
Archived redo generated per dayCount and size of archived logs for the last 14 days, from the local destination only so standby copies aren't double-counted. Useful for sizing the…
Sessions generating the most redoConnected sessions ranked by redo generated since logon. Finds the job behind a sudden flood of archive logs.