Check Redo Log Switch Frequency with V$LOG_HISTORY

calendar Jul 12, 2026 / · 11 min read · Oracle DBA V$LOG_HISTORY Redo Log Log Switch Frequency Oracle Administration Dynamic Performance Views Redo Log Sizing Oracle Performance  ·
Share on: linkedin copy

Check Redo Log Switch Frequency with V$LOG_HISTORY

Purpose

How many times does the online redo log switch in a single hour, and does that number spike during the nightly batch window or stay flat around the clock? A redo log sizing decision made from a single "switches per day" total hides the answer to that question. A database averaging twelve switches a day could be switching once an hour evenly, or it could be sitting idle for twenty hours and then switching ten times in a two-hour ETL run. Those two patterns call for different log file sizes, and only an hourly breakdown tells them apart.

V$LOG_HISTORY is the view that makes the hourly breakdown possible. It is a historical record of every completed log switch still retained in the control file, carrying the sequence number, the thread, and the timestamp at which each switch occurred. Unlike V$LOG, which shows only the current state of each online redo log group, V$LOG_HISTORY accumulates a rolling record of switches over time — enough history, on a database with a reasonably sized control file, to build a multi-day hourly matrix and see exactly when switch activity concentrates.

This post is the diagnostic counterpart to a redo log sizing exercise. Sizing a redo log group correctly starts with knowing the real switch cadence, not an assumed one. The queries below build that cadence: a full hourly matrix across a rolling window, a daily switch-count summary, an average-minutes-between-switches calculation, and a check of the current online log group status from V$LOG.

Code

 1-- Query 1: Hourly switch-count matrix, last 7 days
 2-- Rows = calendar day, columns = hour of day (00-23), cell = switch count
 3SELECT TO_CHAR(first_time, 'YYYY-MM-DD')                              AS log_date,
 4       COUNT(CASE WHEN TO_CHAR(first_time, 'HH24') = '00' THEN 1 END) AS "00",
 5       COUNT(CASE WHEN TO_CHAR(first_time, 'HH24') = '01' THEN 1 END) AS "01",
 6       COUNT(CASE WHEN TO_CHAR(first_time, 'HH24') = '02' THEN 1 END) AS "02",
 7       COUNT(CASE WHEN TO_CHAR(first_time, 'HH24') = '03' THEN 1 END) AS "03",
 8       COUNT(CASE WHEN TO_CHAR(first_time, 'HH24') = '04' THEN 1 END) AS "04",
 9       COUNT(CASE WHEN TO_CHAR(first_time, 'HH24') = '05' THEN 1 END) AS "05",
10       COUNT(CASE WHEN TO_CHAR(first_time, 'HH24') = '06' THEN 1 END) AS "06",
11       COUNT(CASE WHEN TO_CHAR(first_time, 'HH24') = '07' THEN 1 END) AS "07",
12       COUNT(CASE WHEN TO_CHAR(first_time, 'HH24') = '08' THEN 1 END) AS "08",
13       COUNT(CASE WHEN TO_CHAR(first_time, 'HH24') = '09' THEN 1 END) AS "09",
14       COUNT(CASE WHEN TO_CHAR(first_time, 'HH24') = '10' THEN 1 END) AS "10",
15       COUNT(CASE WHEN TO_CHAR(first_time, 'HH24') = '11' THEN 1 END) AS "11",
16       COUNT(CASE WHEN TO_CHAR(first_time, 'HH24') = '12' THEN 1 END) AS "12",
17       COUNT(CASE WHEN TO_CHAR(first_time, 'HH24') = '13' THEN 1 END) AS "13",
18       COUNT(CASE WHEN TO_CHAR(first_time, 'HH24') = '14' THEN 1 END) AS "14",
19       COUNT(CASE WHEN TO_CHAR(first_time, 'HH24') = '15' THEN 1 END) AS "15",
20       COUNT(CASE WHEN TO_CHAR(first_time, 'HH24') = '16' THEN 1 END) AS "16",
21       COUNT(CASE WHEN TO_CHAR(first_time, 'HH24') = '17' THEN 1 END) AS "17",
22       COUNT(CASE WHEN TO_CHAR(first_time, 'HH24') = '18' THEN 1 END) AS "18",
23       COUNT(CASE WHEN TO_CHAR(first_time, 'HH24') = '19' THEN 1 END) AS "19",
24       COUNT(CASE WHEN TO_CHAR(first_time, 'HH24') = '20' THEN 1 END) AS "20",
25       COUNT(CASE WHEN TO_CHAR(first_time, 'HH24') = '21' THEN 1 END) AS "21",
26       COUNT(CASE WHEN TO_CHAR(first_time, 'HH24') = '22' THEN 1 END) AS "22",
27       COUNT(CASE WHEN TO_CHAR(first_time, 'HH24') = '23' THEN 1 END) AS "23"
28FROM   v$log_history
29WHERE  first_time > SYSDATE - 7
30GROUP  BY TO_CHAR(first_time, 'YYYY-MM-DD')
31ORDER  BY 1;
32
33-- Query 2: Total switches per calendar day, last 14 days
34SELECT TO_CHAR(first_time, 'YYYY-MM-DD') AS log_date,
35       COUNT(*)                          AS switch_count
36FROM   v$log_history
37WHERE  first_time > SYSDATE - 14
38GROUP  BY TO_CHAR(first_time, 'YYYY-MM-DD')
39ORDER  BY 1;
40
41-- Query 3: Average minutes between switches, last 24 hours
42SELECT ROUND(
43         (MAX(first_time) - MIN(first_time)) * 24 * 60
44         / NULLIF(COUNT(*) - 1, 0), 1
45       ) AS avg_minutes_between_switches,
46       COUNT(*) AS switches_in_window
47FROM   v$log_history
48WHERE  first_time > SYSDATE - 1;
49
50-- Query 4: Current online redo log group status and size
51SELECT l.group#,
52       l.thread#,
53       l.sequence#,
54       l.bytes / 1024 / 1024 AS size_mb,
55       l.members,
56       l.status,
57       l.first_time
58FROM   v$log l
59ORDER  BY l.group#;

Code Breakdown

Query 1: the hourly matrix

This is the core diagnostic. V$LOG_HISTORY.FIRST_TIME records the timestamp of the moment each logged switch began. TO_CHAR(first_time, 'HH24') extracts the two-digit hour, and 24 COUNT(CASE WHEN ... THEN 1 END) expressions — one per hour — pivot those hourly counts into columns against a row per calendar day. The result reads like a spreadsheet: each row is a day, each column an hour, and each cell the number of log switches that started in that hour on that day.

Reading this matrix across a week surfaces the pattern that a single daily total cannot: a column of zeros through the overnight hours followed by a cluster of high numbers in the 01:00-04:00 columns points directly at a batch or ETL window. A roughly even spread across every column instead points at a steady, transaction-driven workload with no dominant peak.

Query 2: daily totals

The simpler daily summary answers the coarser question — is total switch volume trending up over two weeks? A rising daily total with a stable hourly pattern usually means data volume or transaction rate is growing, not that the workload shape has changed. Run this alongside Query 1 rather than instead of it; the daily total tells you whether volume changed, the hourly matrix tells you where.

Query 3: average minutes between switches

(MAX(first_time) - MIN(first_time)) returns a date-arithmetic interval in days. Multiplying by 24 * 60 converts that interval to minutes. Dividing by COUNT(*) - 1 — the number of gaps between N switches — gives the average number of minutes elapsed from one switch to the next over the trailing 24 hours. NULLIF(COUNT(*) - 1, 0) guards against a divide-by-zero error on a database with one or zero switches in the window. This single number is the fastest sanity check before sizing: if the average is well under the target window Oracle recommends for a healthy switch cadence, the redo logs are undersized for current volume; if it is far above, the logs may be larger than the workload needs.

Query 4: current V$LOG status

V$LOG differs from V$LOG_HISTORY in scope: it shows only the present state of every configured redo log group, not history. STATUS shows whether a group is CURRENT (actively being written), ACTIVE (needed for instance recovery but not current), INACTIVE (no longer needed for recovery), or UNUSED (never written since creation). BYTES divided down to megabytes gives the configured group size directly — the number that any sizing recommendation from the hourly matrix ultimately changes.

Key Points

  • V$LOG_HISTORY is bounded by control file retention, not calendar time. The view holds only as many historical entries as the control file's CONTROL_FILE_RECORD_KEEP_TIME and record section sizing allow. A database with heavy switch volume can lose history from a week ago faster than a quiet database of the same age — check the oldest FIRST_TIME returned before assuming a full 7 or 14 day window is actually present.
  • FIRST_TIME is a DATE, not a TIMESTAMP. TO_CHAR formatting to the hour is accurate to the minute Oracle recorded, but sub-minute switch timing is not available from this view.
  • RAC and multi-thread instances need THREAD# in the grouping. On a RAC database, V$LOG_HISTORY carries rows from every thread. Omitting a THREAD# filter or GROUP BY column merges switch activity across instances into one count, which hides an imbalance where one instance is switching far more often than its neighbors.
  • A single day's matrix can mislead. Weekend workloads, month-end batch cycles, and quarterly close processing all produce hourly patterns that differ sharply from an ordinary weekday. Pull at least 7, and ideally 14, days before drawing a sizing conclusion.
  • The matrix diagnoses; it does not size. This query answers when and how often switches occur. Translating that cadence into a specific log file size and group count is a separate calculation that also accounts for the media recovery and Data Guard transport implications of larger or smaller redo files.
  • V$LOG.STATUS = 'ACTIVE' on more than one group at once is a signal worth investigating alongside the matrix. If active groups are accumulating faster than checkpoint activity clears them during a high-switch-frequency hour, the redo logs may be too small for the checkpoint completion time the workload needs.

Insights and Best Practices

Read the matrix before touching log file size

The most common mistake in redo log sizing is changing group size or count based on a single symptom — an alert log full of "Checkpoint not complete" messages, or a support ticket about slow commits — without first confirming when the underlying switch frequency actually spikes. Running the hourly matrix across two weeks before making any sizing change turns a reactive resize into an evidence-based one, and it prevents a redo log that is sized for the wrong part of the day.

Correlate the matrix with the batch calendar

Once the hourly matrix shows a concentration of switches in a specific window, cross-reference that window against the job scheduler calendar — DBA_SCHEDULER_JOBS or the enterprise scheduler that drives nightly ETL. A cluster of switches at 02:00-04:00 that lines up exactly with a known load job confirms the batch window as the driver, rather than an unexplained application behavior that needs its own investigation.

Track the trend, not just the current window

Running Query 2 on a recurring schedule — weekly, saved to a small tracking table — builds a trend line of daily switch volume over months. A gradual increase in daily switches with a stable hourly shape is ordinary growth and calls for periodic log size review on a normal cadence. A sudden jump in daily switches with no change in workload is worth investigating on its own, since it can indicate an inefficient SQL statement generating excess redo, a missing index causing full-table scans with heavy undo generation, or an application change that increased commit frequency.

Watch for uneven distribution across RAC threads

On a RAC configuration, pull the matrix per thread and compare instances side by side. An instance switching noticeably more often than its peers under an otherwise balanced workload is a candidate for investigation — either genuine workload imbalance from connection routing, or an instance-specific process generating disproportionate redo.

Pair the average-gap calculation with a target range

A commonly used target for online redo log sizing is a switch roughly every fifteen to twenty minutes during peak activity — frequent enough to bound instance recovery time, infrequent enough to avoid the checkpoint and archiver overhead of very frequent switching. Query 3's average-minutes-between-switches number, read against that kind of target range for the busiest hours identified in the matrix (not the 24-hour blended average, which dilutes a real peak), is the number that actually informs whether a redo log group needs to grow, shrink, or add members.

When to Run This Check

  • Before any redo log resize or group-count change, to confirm the actual switch cadence rather than an assumed one.
  • After a "Checkpoint not complete" message appears in the alert log, to see whether switch frequency has genuinely increased or whether checkpoint completion has slowed for another reason.
  • Following an application release or a batch job schedule change, to confirm the new switch pattern matches expectations.
  • As a recurring weekly or monthly check on any production database, to catch a slow drift toward more frequent switching before it becomes an incident.
  • Before sizing redo logs for a new environment being cloned from production, using the source database's real matrix rather than a generic sizing guideline.
  • When investigating Data Guard transport lag, since switch frequency directly drives the volume of redo shipped to a standby in a given hour.

Troubleshooting Common Issues

The matrix shows fewer days than expected, or the earliest date is missing entries. This almost always traces back to control file retention. Check the oldest FIRST_TIME actually returned by V$LOG_HISTORY before trusting a query filtered to SYSDATE - 14; if the earliest available row is more recent than that, the control file has already aged out older history and the true window is shorter than requested.

Hourly counts look too low compared to what the alert log or archiver shows. Confirm the query is not silently filtering out a RAC thread. Add thread# to both the SELECT list and the GROUP BY clause and compare per-thread totals against the combined figure to see whether one thread is being under- or over-represented.

The average-minutes-between-switches figure swings wildly day to day. This is expected on a workload with a strong batch peak, since a 24-hour blended average dilutes a short, intense burst of switches into a number that looks unremarkable. Narrow Query 3's WHERE clause to the specific peak hour identified in the matrix rather than the full day to get a figure that reflects actual peak-load behavior.

Switch counts appear elevated but no obvious batch job or workload change explains it. Cross-check V$LOG.STATUS for groups stuck in ACTIVE for longer than expected, which points at slow checkpoint completion rather than a genuine increase in transaction volume — the redo log may be undersized for current checkpoint I/O capacity rather than the transaction rate itself having grown.

References

Posts in this series