Each commit waits for LGWR to write the transaction's redo to disk. The time grows in two ways: many commits with short waits, or few commits with long waits. The fix for one case does not work for the other.
Symptoms
In the Diagnosis, the finding A lot of time waiting for redo writes at commit (COMMIT) appears. The text says how much of the total time went to the Commit wait class, as a percentage and in seconds. When the trace has commits, it adds how many there were (The session issued and the number) and ends with View profile by class.
The finding appears when the Commit class weighs on the total time. The severity is medium and becomes high when the class weighs more. Only the log file sync event belongs to the Commit class.
The commit count comes from the XCTEND lines with rlbk=0 and includes the transactions that only read (rd_only=1), which end without writing redo. That is why it can exceed the number of log file sync waits.
Cause
On commit and rollback, the session waits for LGWR to write the transaction's redo to disk: that is log file sync. Compare Occurrences and Avg (s) on the event's row in the profile:
- Many short waits: too many commits. The typical case is a commit inside a loop, one per row, or the driver in autocommit, which commits every statement.
- High average per wait: the redo takes long to reach the disk. Slow redo storage, LGWR starved of CPU on a busy server, or transactions that generate a lot of redo at once.
The event's card, which opens when you click its name in the profile, considers a wait under 1 to 2 ms on good storage normal. The finding text notes that an average above a few ms per wait points to the redo storage.
Resolution
Too many commits
- Commit at the end of each business unit of work, not on every row. In PL/SQL, take the
commitout of the loop; in a large load, commit every batch of rows. - In Java, turn off autocommit (
setAutoCommit(false)) and send the rows in batches (addBatchandexecuteBatch), with one commit per batch. - Compare the number of commits in the Summary with what the operation does: commits in the range of the number of rows processed indicate a commit inside the loop.
High average per wait
Compare log file sync with log file parallel write, which is LGWR's own write to disk:
select event, total_waits,
round(time_waited_micro / nullif(total_waits, 0) / 1000, 2) as media_ms
from v$system_event
where event in ('log file sync', 'log file parallel write');The view accumulates since instance startup: for a period, take two readings and compare the difference. If both averages are high, the redo storage is slow: move the redo logs to a faster disk, separate from the data files. If log file parallel write is fast and log file sync is slow, LGWR is waiting for CPU: check the server load in the period.
Asynchronous commit
Oracle accepts a commit that does not wait for the redo write (commit write batch nowait, or the commit_logging and commit_wait parameters). The COMMIT statement documentation describes the options.
With asynchronous commit, a committed transaction can be lost if the instance goes down before the redo is written. Use it only for data that can be redone, and only after reducing the number of commits.
How to confirm
- Apply one change at a time: grouped commits or redo on a faster disk.
- Turn on tracing for the same operation with waits (turn on tracing with DBMS_MONITOR).
- Upload the new file and open the report.
- Compare the number of commits in the Summary and the
log file syncrow in the profile.
Result: with grouped commits, the commits and the Occurrences of log file sync drop; with faster redo, the Avg (s) drops. When the class no longer weighs on the total time, the finding disappears.
In the report
The sample report has no Commit class waits; the links open the same sections in it.
- Summary: the Distinct statements card shows, at the bottom, how many commits and rollbacks the session issued (summary).
- Diagnosis: the finding, with the View profile by class link.
- By wait class: the Commit row (wait classes).
- Response time profile: the
log file syncrow, with Occurrences and Avg (s). The event name opens the Waiting for commit card (profile). - Latency distribution: the
log file synctable shows whether there are many short waits or few long ones (latency). - Top statements by component and by class: the statements that waited the most for commit (statements by component).
A transaction that ends in rollback also waits for log file sync; the number of rollbacks has its own page in rollbacks in the trace.