Log File Sync as Top Wait Event and Performance Issue
(Doc ID 2340663.1)
Last updated on JULY 20, 2023
Applies to:
Oracle Database Cloud Schema Service - Version N/A and laterOracle Database Exadata Express Cloud Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Backup Service - Version N/A and later
Information in this document applies to any platform.
Symptoms
- Slow performance is observed with queries
- During the time of issue, Log File Sync is the top wait event:
Top 10 Foreground Events by Total Wait Time
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Total Wait % DB
Event Waits Time Avg(ms) time Wait Class
------------------------------ ------------ ---- ------- ------ ----------
log file sync 1,176,095 84.2 72 64.5 Commit
free buffer waits 22,717 8059 355 6.2 Configurat - The wait on I/O for log file parallel write and Log archive I/O is high:
Background Wait Events
Avg
%Time Total Wait wait Waits % bg
Event Waits -outs Time (s) (ms) /txn time
-------------------------- ------------ ----- ---------- ------- -------- ------
log file parallel write 292,078 0 2,452 8 0.2 37.5
db file parallel write 4,636 0 2,080 449 0.0 31.8
Log archive I/O 8,180 0 812 99 0.0 12.4
The Avg wait (ms) column, for db file parallel write and Log archive I/O wait events wait average is 449 and 99 which is very huge. Generally it should be less than 10 ms. This is indicating that there may be latency in storage and network.
Cause
To view full details, sign in with your My Oracle Support account. |
|
Don't have a My Oracle Support account? Click to get started! |
In this Document
| Symptoms |
| Cause |
| Solution |
| References |