There are some DB wait events could be caused by poor storage performance in Oracle. For example,
When a user commits or rolls back data, the LGWR flushes the session's redo from the log buffer to the redo logs. The log file sync process must wait for this to successfully complete.
If log file sync is a problem (e.g., avg wait > 2 ms), try the following solutions based on its causes:[1-4]
- db File Sequential Read
- db File Scattered Read” wait events
- Direct Path Read
- Direct Path Read/Write temp
- Free Buffer Wait
In this article, I will discuss the log file sync wait event in Oracle AWR reports, which in many cases is caused by poor storage performance.
An Oracle user session issuing a commit command must wait until the LGWR (Log Writer) process writes the log entries associated with the user transaction to the log file on the disk. Oracle must commit the transaction’s entries to disk (because it is a persistent layer) before acknowledging the transaction commit. The log file sync wait event represents the time the session is waiting for the log buffers to be written to disk.
Top 10 Foreground Events by Total Wait Time
| Event | Waits | Total Wait Time (sec) | Wait Avg(ms) | % DB time | Wait Class |
|---|---|---|---|---|---|
| DB CPU | 2590.9 | 96.6 | |||
| SQL*Net break/reset to client | 5,864,510 | 546.4 | 0 | 20.4 | Application |
| log file sync | 19,575 | 25.5 | 1 | 1.0 | Commit |
| SQL*Net message to client | 15,493,939 | 10 | 0 | .4 | Network |
| library cache: mutex X | 8,042 | .6 | 0 | .0 | Concurrency |
| db file sequential read | 93 | .5 | 5 | .0 | User I/O |
| direct path read | 158 | .3 | 2 | .0 | User I/O |
| Disk file operations I/O | 89 | .1 | 1 | .0 | User I/O |
| SQL*Net more data to client | 945 | .1 | 0 | .0 | Network |
| cursor: pin S | 39 | .1 | 1 | .0 | Concurrency |
Log File Sync
An Oracle user session issuing a commit command must wait until the LGWR (Log Writer) process writes the log entries associated with the user transaction to the log file on the disk. Oracle must commit the transaction’s entries to disk (because it is a persistent layer) before acknowledging the transaction commit. The log file sync wait event represents the time the session is waiting for the log buffers to be written to disk.
Sometimes you can find "log file sync" wait event appears on the top list of an AWR report:
Top 10 Foreground Events by Total Wait Time
What does it mean if log file sync is shown on the list?[1]
- Is this noticeably slowing down all commits?
- Disk throughput is only one aspect that affects LGWR. It consumes CPU while executing too.
- If you've maxed out your CPU capacity processing "business transactions", then it will be starved for resource. This can lead to you seeing a lot of "log file sync" waits.
- If your datafiles are on the same disks as the redo logs, then DBWR will also be contending for the same disk.
- Is it just the top wait event on your system?
- Remember that there's always something that has to be "top".
How to Reduce "log file sync" Wait Time
When a user commits or rolls back data, the LGWR flushes the session's redo from the log buffer to the redo logs. The log file sync process must wait for this to successfully complete.
If log file sync is a problem (e.g., avg wait > 2 ms), try the following solutions based on its causes:[1-4]
- Slow disk I/O
- Reduce contention on existing disks
- Put log files on faster disks and/or increasing the log_buffer size above 10 megabytes
- Put alternate redo logs on different disks to minimize the effect of archive processes (log files switches)
- LGWR is not getting enough CPU
- If the vmstat runqueue column is greater than cpu_count, then the instance is CPU-bound and this can manifest itself in high log file sync waits. The solution is to
- Tune SQL (to reduce CPU overhead)
- Add processors
- 'Nice' the dispatching priority of the LGWR process
- High COMMIT activity
- Review application design, use NOLOGGING operations where appropriate, and reduce the frequency of COMMIT statements in the application
- LGWR is paged out
- Check the server for RAM swapping, and add RAM if the instance processes are getting paged-out.
References
- Log file sync wait (Ask TOM)
- Log file sync wait
- Oracle Log File Sync Wait Event
- Expert Consolidation in Oracle Database 12c
- AWR Wait Events: Free Buffer Waits vs. Buffer Busy Waits (Xml and More)
No comments:
Post a Comment