Wait Events: "Log File Sync and Log File Parallel Write"

In OLTP environments, one of the biggest performance problems is the excessive wait events for recordings Online Redo Log Files. Every time that Oracle Database waits for recordings in these files can observe events "Log File Sync 'and' Log File Parallel Write".


What is the expected event 'log file sync'?


When a session of a user or application executes a COMMIT, the information stored in memory Redo (LOG_BUFFER) are discharged in the Online Redo Log Files to make permanent changes made in the database. This process is executed by LOG WRITER (LGWR). When the LGWR process ends this recording, a warning is sent to the session stating that the COMMIT process is 100% complete.
The time between the user session / application LGWR request the recording of redos Memory (LOG_BUFFER) for Online Redo Log Files and LGWR inform the meeting that the process was completed is where the event occurs 'log file

How to identify the event 'log file sync'?


To analyze and identify the event 'log file sync' can use the information to:
- Reports of a period AWR timely collection.
- Trace files of LGWR. These files can show many events waiting on 'log file parallel wait'

Events which occur Waiting 'log file sync'?


Events waiting to 'log file sync' can happen at any stage between the user session / application LGWR request the recording of redos Memory (LOG_BUFFER) for Online Redo Log Files and LGWR inform the session that the process was completed.

Performance of LGWR


The main question is "Is there any problem for Recording Performance in Online Redo Logs"?
The steps below can help you find performance problems in the Log Writer (LGWR).

1 - Comparing the average waiting time for 'log file sync' with the average waiting time for 'log file parallel write'


The event hopes to 'log file parallel write' occurs while LGWR is writing redo information on Online Redo Log Files. The Total time of this event is the total recording time in disk files.
Analyzing this event together with the event 'log file sync' can identify the total time consumption and I / O completion of a cycle COMMIT.

Analyzing the information in an AWR report:


In the above example, we can identify a very time-consuming and I / O (ms) for both events 'log file sync' and 'log file parallel write'.
The proportion of time between 'log file sync' and 'log file parallel write' suggests a problem of I / O (Waiting recording redo from memory to disk).

If the time spent in waiting event 'log file parallel write' exceeds 20ms, the likelihood of a problem of I / O is great.


- Check the filesystems where are the Redo Logs, and check possbilidade to optimize the performance of I / O through OS
- Do not use RAID 5 for filesystems / disks where are the Redo Logs.
- Ensure that there are other processes that can be recording information on the same disks where are the Redo Logs. Make sure that the disk has enough throughput for competition of these processes, or change the Redo Logs Filesystem.
- Check if the parameter is not too large LOG_BUFFER. The higher the LOG_BUFFER the longer it takes for the LGWR to write redo the changes to disk.

2 - Check the Traces of the Log Writer (LGWR)


Even if the average waiting time of the event 'log file parallel write' is normal, there may be peaks where the writing of the redo logs is high in influence and then the event 'log file sync'.
From version are generated alert messages in the LGWR trace file when a process LGWR writing takes longer than 500ms. This is a high threshold, but it is a way of identifying problems I / O. Messages are other similar to:



- Check what else is running at the moment to identify gaps recording LGWR.

3 - Check if the size of Redo logs is Enough
An operation 'log file sync' is run every time there is a switch of logfiles to ensure that all information in redos were recorded before the next log starts. As a standard recommendation is that a log switch occurs every 15 or 20 minutes. If the log switches occur in a shorter time, more often, then more wait events 'log file sync' will occur, which means a longer waiting time between sessions.

- You can check the alert.log to check the switch logfile:


In the above switches can be checked every 2 to 4 minutes, which is 5x more frequently than desirable.
- Another option is to check the average Log Switchs through AWR report:
In the example above 29.98 log switches occur every hour, which means about 2 log switches per minute. This is a value much higher than recommended (15 to 20 switches per minute).
- Increasing the size of Redo Log files.

4 - Excessive number of commits by Application


In this case the question is what to do: "The application is performing many commits?"
If so, there may be performance issues, since the redo information are unloaded from memory (LOG_BUFFER) redo logs to disk.
A simple way to identify this scenario is when the average wait for the event 'log file sync' is much larger than average wait event "log file parallel write ', this shows that the longer the waiting is not for recording Disco redo logs. The high CPU time can also show restraint caused by excessive commits.
Another scenario is still where the average wait event "log file sync 'is low but the number of waits is high.
One can compare the average 'User commits' with 'User calls':

The above example could identify 5.76 'user calls' commit by what is considered 5x greater than recommended (1).


- If multiple commits in the application in a short time interval, or small call commits, it is recommended to execute a block of larger changes before executing a commit. Ateração This should be done through the application.
- Enabling Asynchronous Commit. (To learn how to enable this option, see the article:Changing behavior with transactions in Oracle Database Asynchronous... )


How to Minimise Waits for 'Log File Sync'? [MOS ID 857576.1]
Troubleshooting: log file sync 'Waits [MOS ID 1376916.1]


Views: 16903

Comment by Madhava Verma Dantuluri on February 4, 2014 at 5:47am

Excellent article, i learnt something new today.

Comment by Victor Armbrust on February 4, 2014 at 10:03am

Thanks Madhava. Pleasure to help. =D


You need to be a member of Oracle Community to add comments!

Join Oracle Community

Oracle Community On

I'm not a fan of advertising, and so I will not be including any advertisements on OracleCommunity.net. However, managing this community does not come without cost! If you are willing to donate to help pay for the monthly community fees and domain services I accept Bitcoin and PayPal donations.

Donate Bitcoins



Blog Posts

Looking to Publish Scientific, Technical and Medical Articles? Take a look!

Digital publishers who are involved in publishing Scientific, Technical and Medical (STM) content follow a very rigorous routine of editing, formatting, and publishing. Primarily, the content is meticulously scanned for grammatical errors and logical inequalities. Then a final draft is made in adherence to the guidelines set by the publishing platform and forwarded to TO BE PUBLISHED’ folder. All articles, papers, and…


Posted by Alvaro Dee on October 21, 2016 at 3:29am

Apple Opens Its First iOS App Development Centre in Italy

Apple aims at making things rounder and sleeker. With iPhone 7 out in the field, Apple is now investing its time and resources in organizing workshops and opening new iOS app development centers to educate people in advanced methods of iOS app building.

Apple’s highlight of the year? It’s first-ever iOS development Centre in Naples!…


Posted by Alvaro Dee on October 20, 2016 at 12:33am

Outsource Your Data Conversion Services to Get an Edge over Your Competitors

The process of converting data from one standard format to any desired file format, Data conversion makes the data more precise, convenient, and retrievable, while keeping the core data unchanged. The precious asset to an organization, data helps in effective decision making if digitized, organized, and managed properly. Whereas the inefficient management of data makes it a burden on the business enterprise.

If the data is structured and digitized efficiently, it can help in achieving…


Posted by Alvaro Dee on October 19, 2016 at 5:54am

Data Mining Can Help Your Business Get a Competitive Edge

Data mining services are a vital part of business growth. Data mining, sometimes referred as knowledge discovery in databases, is the process of digging through data to discover hidden connections, patterns, and anomalies so that future trends can be predicted. The mined data can help businesses in cutting costs, increasing revenues, reducing risks, improving customer relationships, and more.

The diversity and volume of data that is being captured by companies today is staggering. The…


Posted by Alvaro Dee on September 29, 2016 at 2:17am

© 2016   Created by Steve Karam.   Powered by

Badges  |  Report an Issue  |  Terms of Service