Lesson 9	Tune the common redo log operations
Objective	Change parameters to relieve redo log contention.

Tune the Common Redo Log Operations

Where redo tuning applies in Oracle 23ai

In modern Oracle deployments, the scope of redo tuning depends on the service model:

Autonomous Database: redo log sizing/placement and many internal settings are managed by Oracle. Your work shifts toward workload design (commit frequency, batch strategy), observability, and availability architecture.
Co-managed OCI services (Base Database Service on VM/BM, Exadata Database Service): redo tuning remains a core DBA responsibility. You still diagnose redo-related waits and apply storage + configuration changes to eliminate contention.

What redo log contention looks like

Redo contention is not “one thing.” It typically presents as elevated commit latency or foreground waits that point to redo I/O or log switching pressure. Common indicators include:

log file sync waits (sessions waiting for commit to be hardened)
log file parallel write waits (LGWR write latency)
log buffer space waits (foreground cannot allocate redo in memory)
Frequent log switches, “cannot allocate new log”, or archiving pressure

Measure first using modern tooling

The legacy utlbstat/utlestat era is long over. In Oracle 23ai, you typically diagnose redo contention with:

AWR / ASH (preferred): confirm the top waits, when they occur, and which SQL/workload drives them.
Dynamic performance views: validate redo rates, switch frequency, and wait-event distribution.

Start with the system-wide wait profile for redo-related events:

SELECT event, total_waits, time_waited
FROM   v$system_event
WHERE  event IN ('log file sync',
                'log file parallel write',
                'log buffer space',
                'log file switch (checkpoint incomplete)',
                'log file switch completion')
ORDER  BY time_waited DESC;

Next, confirm whether the database is switching logs too frequently. A widely used guideline is to avoid excessive switching and target a reasonable interval (for many OLTP systems, on the order of tens of minutes rather than every few minutes). Oracle’s Performance Tuning Guide includes a rough guide of switching at most once every ~20 minutes.

-- Hourly switch counts (adjust interval granularity as needed)
SELECT TRUNC(first_time, 'HH24') AS hour_start,
       COUNT(*) AS switches
FROM   v$log_history
GROUP  BY TRUNC(first_time, 'HH24')
ORDER  BY hour_start;

Reference views commonly used for log groups and switch history include V$LOG and V$LOG_HISTORY.

Modern levers that relieve redo contention

1) Fix redo I/O latency first

If log file parallel write is high, the most effective “tuning” is usually not a parameter tweak:

Place redo logs on low-latency storage (ASM disk groups on fast media; Exadata/flash where applicable).
Eliminate noisy-neighbor I/O contention on the redo device path.
Validate write latency at the OS/storage layer (especially for VM-based deployments).

The general principle is simple: LGWR write latency drives commit latency.

2) Size redo logs and provide enough groups

Redo logs that are too small can cause frequent switches, increasing checkpoint and archiving overhead and amplifying contention.

Size online redo logs to reduce “thrash” while keeping recovery objectives in mind.
Ensure enough redo log groups so LGWR is not forced to wait for archiving before reuse.

3) Prevent archiving bottlenecks and FRA exhaustion

A full archive destination can stall the database in many configurations. In modern environments, use the Fast Recovery Area (FRA) and monitor it proactively:

Size DB_RECOVERY_FILE_DEST_SIZE to your redo generation rate and retention/backup strategy.
Automate RMAN backup-and-delete for archive logs in co-managed environments.
Alert on FRA usage before it becomes critical.

SELECT name, space_limit, space_used, space_reclaimable, number_of_files
FROM   v$recovery_file_dest;

4) Reduce commit pressure from the application

If log file sync is high and LGWR write latency is not the bottleneck, the workload may be committing too frequently:

Batch small transactions where appropriate (application design decision).
Avoid “row-by-row commit” patterns in ETL/maintenance jobs.
For distributed/HA designs (Data Guard), validate whether synchronous configuration choices are increasing commit time.

5) Treat legacy init.ora redo parameters as suspect

Much of the older redo “parameter tuning” guidance referenced settings that are obsolete in modern Oracle releases.

Parameters such as LOG_SIMULTANEOUS_COPIES and LOG_SMALL_ENTRY_MAX_SIZE appear in older documentation and have long been treated as obsolete/legacy in modern tuning practice.
Instead of chasing legacy latch-era knobs, focus on measurable symptoms (wait events), redo I/O latency, log switch frequency, archiving throughput, and application commit patterns.

If you inherit an older init.ora/spfile with unusual redo-related settings, validate them against current Oracle documentation and remove unnecessary overrides so the database can use modern defaults and algorithms.

Compute redo rates without legacy report.txt output

You can estimate redo “shape” using V$SYSSTAT by sampling at two points in time and computing deltas (or by using AWR). For a quick manual sample:


-- Sample 1 (note timestamp)
SELECT name, value
FROM   v$sysstat
WHERE  name IN ('redo size',
                'redo entries',
                'redo buffer allocation retries',
                'redo log space requests');

After a few minutes, run it again and compute:

Average redo entry size ≈ (Δ redo size) / (Δ redo entries)
If redo buffer allocation retries increases materially during the interval, investigate log buffer space waits and redo generation bursts.

For sustained issues, prefer AWR/ASH so you can correlate redo pressure to specific SQL and time windows.

The next lesson continues with redo log checkpoint behavior and how to interpret log-switch and checkpoint-related signals in a modern environment.