WAITEVENT: "log file sync" Reference Note [ID 34592.1]
| WAITEVENT: "log file sync" Reference Note [ID 34592.1] |
 Modified:06-Feb-2012Type:REFERENCEStatus:PUBLISHEDPriority:3 |
|
"log file sync" Reference Note
This is a reference note for the wait event "log file sync" which includes the following subsections:- Brief definition
- Individual wait details (eg: For waits seen in <>)
- Systemwide wait details (eg: For waits seen in <>)
- Reducing waits / wait times
- Data Guard Perspective
- Known bugs
- Versions:7.0 - 11.1 Documentation:
- When a user session(foreground process) COMMITs (or rolls back), the session's redo information needs to be flushed to the redo logfile. The user session will post the LGWR to write all redo required from the log buffer to the redo log file. When the LGWR has finished it will post the user session. The user session waits on this wait event while waiting for LGWR to post it back to confirm all redo changes are safely on disk.
This may be described further as the time user session/foreground process spends waiting for redo to be flushed to make the commit durable. Therefore, we may think of these waits as commit latency from the foreground process (or commit client generally).
See Reducing Waits section below for more detailed breakdown of this wait event.
("log file sync" also applies to ROLLBACK/UNDO in that once the rollback/undo is complete the end of the rollback/undo operation requires all changes to complete the rollback/undo to be flushed to the redo log)
Parameters:
- P1 =
- P2 = Not used
- P3 = Not used
- buffer#
All changes up to this buffer number (in the log buffer) must be flushed to disk and the writes confirmed to ensure that the transaction is committed , and will remain committed upon an instance crash. Hence the wait is for LGWR to flush up to this buffer#.
buffer# then theSEQ#column of <> should increment every second. If not then the local session has a problem with wait event timeouts. If theSEQ#column is incrementing then the blocking process is the LGWR process. Check to see what LGWR is waiting on as it may be stuck.
Wait Time:
The wait is entirely dependent on LGWR to write out the necessary redo blocks and confirm completion back to the user session. The wait time includes the writing of the log buffer and the post. The waiter times out and increments the sequence number every second while waiting.
Finding Blockers:
If a session continues to wait on the the same
Systemwide figures for waits on "log file sync" show the time spent waiting for COMMITs to complete. If this is significant then there may be a problem with LGWR's ability to flush redo out quickly enough. One can also look at:
- "log file parallel write" waits for LGWR (See
- "user commits" statistic shows the number of commits.
Here are 3 main general tuning tips to help you reduce waits on "log file sync":
- Tune LGWR to get good throughput to disk . eg: Do not put redo logs on RAID 5.
- If there are lots of short duration transactions see if it is possible to BATCH transactions together so there are fewer distinct COMMIT operations. Each commit has to have it confirmed that the relevant REDO is on disk. Although commits can be "piggybacked" by Oracle reducing the overall number of commits by batching transactions can have a very beneficial effect.
- See if any of the processing can use the COMMIT NOWAIT option (be sure to understand the semantics of this before using it).
- See if any activity can safely be done with NOLOGGING / UNRECOVERABLE options.
- Check to see if redologs are large enough. Enlarge the redologs so the logs switch between 15 to 20 minutes.
For more detailed analysis for reducing waits on LOG FILE SYNC please see below:
The overall wait time for LOG FILE SYNC may be broken down into subsections or components.
If your system still shows high "log file sync" wait times after ensuring the general tuning tips above are completed, you should break down the total wait time into the individual components, then tune those components that make up the largest time.
The log file sync wait may be broken down into the following components:
1. Wakeup LGWR if idle
2. LGWR gathers the redo to be written and issue the I/O
3. Time for the log write I/O to complete
4. LGWR I/O post processing
5. LGWR posting the foreground/user session that the write has completed
6. Foreground/user session wakeup
Tuning advice based on log file sync component breakdown above:
Steps 2 and 3 are accumulated in the "redo write time" statistic. (i.e. as found under STATISICS section of Statspack and AWR)
Step 3 is the "log file parallel write" wait event. (
Steps 5 and 6 may become very significant as the system load increases. This is because even after the foreground has been posted it may take a some time for the OS to schedule it to run. May require monitoring from O/S level.
For Data Guard with synchronous (SYNC) transport and commit WAIT defaults, the above tuning steps still apply, except step 3 also includes the time for the network write and the RFS/redo write to the standby redo logs.
This wait event and how it applies to Data Guard is explained in detail in the MAA OTN white paper:
Known Bugs
You can restrict the list below to issues likely to affect one of the following versions by clicking the relevant button:
Data Guard Perspective:Reducing Waits / Wait times:Systemwide Waits:Individual Waits:Definition:Note 387174.1:MAA - Data Guard Redo Transport and Network Best Practices.
NB Bug Fixed Description 13074706 12.1.0.0 Long "log file sync" waits not correlated with slow writes 12378147 11.2.0.2.BP10, 11.2.0.3, 12.1.0.0 Long broadcast ack warning messages, and/or many Log File Sync timeouts in foregrounds in RAC 9095696 11.2.0.4, 12.1.0.0 "log file sync" wait time spikes with ARCHIVE_LAG_TARGET set 8490879 10.2.0.4.4, 10.2.0.5, 11.1.0.7.3, 11.2.0.1 Long "log file sync" latencies due to broadcast on commit scheme 8220734 10.2.0.4.4, 10.2.0.5, 11.1.0.7.3, 11.2.0.1 Long "log file sync" wait in RAC 7716356 10.2.0.5, 11.2.0.1 Long "log file sync" latencies with broadcast on commit scheme in RAC 7643632 10.2.0.4.1, 10.2.0.5, 11.1.0.7.4, 11.2.0.1 High log file sync in Data Guard maximum availability (sync) mode 7610362 10.2.0.4.4, 10.2.0.5, 11.1.0.7.3, 11.2.0.1 Long "log file sync" waits in RAC with broadcast on commit in RAC P 7568734 10.2.0.5, 11.2.0.1 AIX: Sporadic spikes of 'log file sync' on AIX with heavy commit concurrency 7452373 10.2.0.5, 11.1.0.7.1, 11.2.0.1 "log file sync" timeout is not configurable 6319685 10.2.0.4, 11.1.0.7, 11.2.0.1 LGWR posts do not scale on some platforms 6193945 10.2.0.4.1, 10.2.0.5, 11.1.0.7, 11.2.0.1 High LGWR CPU use and long 'log file sync' latency in RAC 9776431 11.1.0.7.4 11.1.0.7.3 fix for 8220734 is incomplete - "log file sync" timeout set to 1 second 5896963 10.2.0.4, 11.1.0.6 High LGWR CPU and longer "log file sync" with fix for bug 5065930 5147386 10.2.0.4.1, 10.2.0.5, 11.1.0.6 Long waits on "log file sync" /random ORA-27152 "attempt to post process failed" 5087592 10.2.0.4, 11.1.0.6 "log file sync" waits from read only commits 5065930 10.2.0.3, 11.1.0.6 "log file sync" timeouts can occur 5061068 10.2.0.3, 11.1.0.6 RAC using "broadcast on commit" can see delayed commit times 3311210 9.2.0.5, 10.1.0.2 Unnecessary 0.5 seconds waits for "Broadcast on commit" SCN scheme 2663122 9.2.0.5, 10.1.0.2 Unneccessarily long waits on "log file sync" can occur 2640686 9.2.0.5, 10.1.0.2 Long waits for "log file sync" with broadcast SCN in RAC
- '*' against a bug indicates that an alert exists for that issue.
- '+' indicates a particularly notable bug.
- 'P' indicates a port specific bug.
- Fixed versions use "BPnn" to indicate Exadata bundle nn.
- "OERI:xxxx" may be used as shorthand for ORA-600 [xxxx].
Note.34583.1:"log file parallel write" Reference Note:)Note:34583.1) buffer#11g 10g
0 comentarios