Hourly Redo Activity Analysis Using V$LOG_HISTORY

Understanding redo generation patterns is essential for Oracle performance monitoring, backup planning, and Data Guard analysis.

This query provides a daily and hourly breakdown of redo activity based on V$LOG_HISTORY, allowing DBAs to quickly identify:

• Peak workload hours
• Batch processing windows
• Night vs day activity
• Redo generation trends over time

Purpose of the Query

This query answers a key operational question:

“At what hours of the day is my database generating redo activity?”

SELECT to_char(first_time,'fmDD-MONTH-YYYY') DAY,

       TO_NUMBER(SUM(DECODE(SUBSTR(TO_CHAR(first_time,'DD-MM-YY HH24:MI:SS'),10,2),'00',1,0)),'999') "00h",
       TO_NUMBER(SUM(DECODE(SUBSTR(TO_CHAR(first_time,'DD-MM-YY HH24:MI:SS'),10,2),'01',1,0)),'999') "01h",
       TO_NUMBER(SUM(DECODE(SUBSTR(TO_CHAR(first_time,'DD-MM-YY HH24:MI:SS'),10,2),'02',1,0)),'999') "02h",
       TO_NUMBER(SUM(DECODE(SUBSTR(TO_CHAR(first_time,'DD-MM-YY HH24:MI:SS'),10,2),'03',1,0)),'999') "03h",
       TO_NUMBER(SUM(DECODE(SUBSTR(TO_CHAR(first_time,'DD-MM-YY HH24:MI:SS'),10,2),'04',1,0)),'999') "04h",
       TO_NUMBER(SUM(DECODE(SUBSTR(TO_CHAR(first_time,'DD-MM-YY HH24:MI:SS'),10,2),'05',1,0)),'999') "05h",
       TO_NUMBER(SUM(DECODE(SUBSTR(TO_CHAR(first_time,'DD-MM-YY HH24:MI:SS'),10,2),'06',1,0)),'999') "06h",
       TO_NUMBER(SUM(DECODE(SUBSTR(TO_CHAR(first_time,'DD-MM-YY HH24:MI:SS'),10,2),'07',1,0)),'999') "07h",
       TO_NUMBER(SUM(DECODE(SUBSTR(TO_CHAR(first_time,'DD-MM-YY HH24:MI:SS'),10,2),'08',1,0)),'999') "08h",
       TO_NUMBER(SUM(DECODE(SUBSTR(TO_CHAR(first_time,'DD-MM-YY HH24:MI:SS'),10,2),'09',1,0)),'999') "09h",
       TO_NUMBER(SUM(DECODE(SUBSTR(TO_CHAR(first_time,'DD-MM-YY HH24:MI:SS'),10,2),'10',1,0)),'999') "10h",
       TO_NUMBER(SUM(DECODE(SUBSTR(TO_CHAR(first_time,'DD-MM-YY HH24:MI:SS'),10,2),'11',1,0)),'999') "11h",
       TO_NUMBER(SUM(DECODE(SUBSTR(TO_CHAR(first_time,'DD-MM-YY HH24:MI:SS'),10,2),'12',1,0)),'999') "12h",
       TO_NUMBER(SUM(DECODE(SUBSTR(TO_CHAR(first_time,'DD-MM-YY HH24:MI:SS'),10,2),'13',1,0)),'999') "13h",
       TO_NUMBER(SUM(DECODE(SUBSTR(TO_CHAR(first_time,'DD-MM-YY HH24:MI:SS'),10,2),'14',1,0)),'999') "14h",
       TO_NUMBER(SUM(DECODE(SUBSTR(TO_CHAR(first_time,'DD-MM-YY HH24:MI:SS'),10,2),'15',1,0)),'999') "15h",
       TO_NUMBER(SUM(DECODE(SUBSTR(TO_CHAR(first_time,'DD-MM-YY HH24:MI:SS'),10,2),'16',1,0)),'999') "16h",
       TO_NUMBER(SUM(DECODE(SUBSTR(TO_CHAR(first_time,'DD-MM-YY HH24:MI:SS'),10,2),'17',1,0)),'999') "17h",
       TO_NUMBER(SUM(DECODE(SUBSTR(TO_CHAR(first_time,'DD-MM-YY HH24:MI:SS'),10,2),'18',1,0)),'999') "18h",
       TO_NUMBER(SUM(DECODE(SUBSTR(TO_CHAR(first_time,'DD-MM-YY HH24:MI:SS'),10,2),'19',1,0)),'999') "19h",
       TO_NUMBER(SUM(DECODE(SUBSTR(TO_CHAR(first_time,'DD-MM-YY HH24:MI:SS'),10,2),'20',1,0)),'999') "20h",
       TO_NUMBER(SUM(DECODE(SUBSTR(TO_CHAR(first_time,'DD-MM-YY HH24:MI:SS'),10,2),'21',1,0)),'999') "21h",
       TO_NUMBER(SUM(DECODE(SUBSTR(TO_CHAR(first_time,'DD-MM-YY HH24:MI:SS'),10,2),'22',1,0)),'999') "22h",
       TO_NUMBER(SUM(DECODE(SUBSTR(TO_CHAR(first_time,'DD-MM-YY HH24:MI:SS'),10,2),'23',1,0)),'999') "23h"

FROM v$log_history
WHERE first_time > SYSDATE - 21
GROUP BY to_char(first_time,'fmDD-MONTH-YYYY')
ORDER BY SUBSTR(DAY,4,6), SUBSTR(DAY,1,2);

How It Works

1. Source of data: V$LOG_HISTORY

This view contains historical information about:

• Redo log switches
• Archive log generation
• Log timestamps

2. Time filtering

WHERE first_time > SYSDATE - 21

This limits analysis to the last 21 days, providing a recent workload pattern view. Each row represents a log switch event.

Interpretation

1. Peak hours identified

• High values indicate redo-intensive periods
• Typically correspond to:
  • Batch jobs
  • ETL processes
  • Reporting loads

2. Low activity hours

• Usually nighttime or maintenance windows
• Ideal for:
  • RMAN backups
  • Data refresh
  • Index rebuilds

Practical Use Cases

This query is useful for:

• Database workload profiling
• Backup window optimization
• Data Guard redo analysis
• Identifying batch job impact
• Capacity planning