Oracle Database / SQL Performance & Memory
Investigate checkpoint-related redo log switch waits
A workload can stall at log switches when checkpoint progress or log configuration cannot keep up. Identify the actual switch-related event.
Environment and inputs: Oracle Database 19c baseline · SQL*Plus-compatible SQL unless RMAN, DGMGRL, ADRCI or a host shell is specified. Check release, patch level, edition and container first.
Start with this diagnostic
Use the stated SQL, RMAN, DGMGRL or host shell and replace sample names with approved targets. DBA and V$ views need authorized catalog access in the correct container. Do not grant broad privileges for an example.
SELECT event, total_waits, time_waited_micro FROM v$system_event WHERE event LIKE 'log file switch%';Read the result
The named wait differentiates checkpoint, archival and other switch conditions. Totals require interval comparison and alert-log correlation.
Resolve and verify
Review redo group size and switch frequency, checkpoint I/O and archival health. Make a targeted supported configuration change after measuring the constraint.
Operational precautions
Do not drop or clear redo groups to remove a switch wait; required redo protects recoverability.