Oracle commit latency is an end-to-end path: application commit frequency, redo generation, log buffer behaviour, log writer activity and durable storage all matter. A slow commit is not automatically a storage fault, and a fast storage benchmark does not prove that the database write path is healthy under the production workload.
Classify the visible delay
Compare commit-related waits with user I/O and system I/O waits, redo size, commits per second, log switches, application latency and storage measures. A high commit rate from small transactions can create pressure even when each SQL statement is efficient. Large batches may reduce commit frequency but increase lock duration and recovery risk, so the right boundary is a business decision as well as a performance one.
Check the SQL and transaction shape
- Identify clients that changed commit frequency or introduced synchronous writes.
- Find statements generating unusually high redo or physical I/O.
- Review index maintenance and bulk-load patterns.
- Check log sizing and switch frequency against established operational guidance.
- Correlate storage latency with the exact database wait interval.
Avoid isolated parameter changes
Changing redo, log or I/O settings without a measured cause can move the problem or increase recovery exposure. First determine whether the source is SQL, transaction design, capacity or a real storage service-time issue. Test with representative concurrency and retain an easy rollback path.
The durable outcome is predictable commit latency at the required workload, not merely a different wait ranking on one sample.
Separate foreground commit waiting from redo writes
The log file sync event concerns foreground sessions waiting for commit-related redo completion. The log file parallel write event describes redo-log write activity by the background log writer. Their measurements have different scopes and are not a pair of identical timers. Group commit, scheduling and the workload mix can affect the relationship.
SELECT event, total_waits, time_waited_micro
FROM v$system_event
WHERE event IN ('log file sync', 'log file parallel write');
Capture two samples rather than diagnosing from accumulated totals. Calculate interval changes and note a restart. Compare them with commits, redo bytes, application latency and the actual storage path. A high average can hide occasional extreme waits, while a good average can conceal the tail latency that users report.
Example: a loop commits every row
Imagine an import that previously committed a coherent batch now commits after every row. Commit count rises sharply without a proportionate increase in useful records processed. Before ordering faster disks, inspect this transaction change. A controlled batching experiment may reduce commit overhead, but its size must respect lock duration, restartability and the business's durability boundary.
Do not disable or weaken commit durability as a routine speed fix. Whether an application can tolerate losing acknowledged work is a business contract. A benchmark that bypasses the required durability semantics is testing a different system from the one customers rely on.
Check log switches and downstream responsibilities
Correlate bursts with log switches, checkpoint activity and archive destinations. An unavailable or constrained destination may create a different bottleneck from a slow redo device. Include recovery and standby requirements when reviewing log configuration; space, switch frequency and recovery objectives belong in the same change review.
Define the acceptance window around a complete import and the concurrent customer workload. Check import duration, p95 commit response, lock waits and replica or standby freshness. A larger batch that speeds the import while blocking order updates for much longer may be unacceptable. Keep the original transaction boundary available for rollback and observe the next scheduled run before declaring the problem resolved.
Keep the evidence for the next incident
Mini DBA Oracle monitoring combines query and metric history with sessions, waits and resource diagnostics for incident investigation. Available evidence depends on database permissions and collection configuration; operators must confirm diagnostic entitlements for their environment.
References and further reading