Oracle Hourly Redo Log Switch Count

Shows the log switch count per hour. Tells whether redo log sizing fits the workload and whether checkpoint pressure exists.

#redo#log-switch#v$log_history

oracle · sql

SELECT TO_CHAR(first_time, 'YYYY-MM-DD HH24') AS hour, COUNT(*) AS log_switches
FROM   v$log_history
WHERE  first_time > SYSDATE - 2
GROUP  BY TO_CHAR(first_time, 'YYYY-MM-DD HH24')
ORDER  BY 1 DESC;

SELECT group#, thread#, bytes / 1048576 AS size_mb, members, status FROM v$log;

Operational Notes & Remediation Guidance

More than 4-6 log switches per hour during normal load suggests redo log files are undersized.

Production Safety & Execution Standards

This script is designed for read-only diagnostics; it does not alter schema or modify data directly.
Prior to executing on production systems under high load, verify the target database version (Min. Version: 11g).
Exercise caution with mutating actions (e.g., terminating sessions or dropping indexes) referenced in the notes section.

Other ORACLE Diagnostic Scripts

View All (100)

#61Performance

Oracle Active Sessions and Wait Events

Lists active user sessions with the running SQL_ID and wait event. Shows the live picture during performance issues.

View Script

#62Locking

Find Blocking Sessions in Oracle

Shows sessions that make others wait, with wait times. Identifies the root session to terminate in lock incidents.

View Script

#63Storage

Oracle Tablespace Usage Including Autoextend

Calculates tablespace usage against the real maximum, honoring autoextend limits. Catches space errors like ORA-01653 before they happen.

View Script

#64Performance

Top 20 Most Expensive Oracle SQL with v$sqlstats

Shows costliest SQL by elapsed time with executions, CPU and I/O. Tells you which SQL_ID to investigate for tuning.

View Script