Tuning log file sync

Commit is not complete until LGWR writes log buffers including commit redo recods to log files. This can lead to slower response from LGWR too. LGWR is unable to post the processes fast enough, due to excessive commits. With Private strands, a process can generate few Megabytes of redo before committing.

LGWR is suffering from other database contention such as enqueue waits or latch contention. This is a possible scenario however unlikely.

Various bugs. Statspack is for seconds and there are 8 CPUs in the server. But, seconds were spent waiting. So, this is a major bottleneck for application scalability.

Identify and break down LGWR wait events. Query wait events for LGWR. In this instance LGWR sid is 3 and usually it is. This event, normally, can be ignored. Google it and there is enormous information about this already]. Difference between two snapshots from this view, for the same session, can be quite useful. Tanel Poder has written an excellent tool for this. Using that tool, we can print out 1 second snapshot for LGWR session 3.

Some part of the output removed to improve clarity. Tracing LGWR can detoriate performance further. So, careful consideration must be given before turning sqltrace on LGWR. Few trace lines shown below. From the output below shows that LGWR submitted 2 write calls with 16 redo blocks. Performance of write itself is not entirely bad. From statspack report and one column removed to improve readability. Essentially, redo size is high, but not abnormal.

But, commits per second is on the higher side. Knowledge about application will be useful here. Trace few sessions and understand why there are so many commits, if commit frequency is higher.

If the commit frequency is higher, then LGWR could be essentially spending time signalling user process. Statistics might need to be captured with more granularity to understand the issue. For e. Between and redo blocks written statitics did not change since delta is 0.

Meaning, LGWR did not write any redo blocks. This lead to more granular time frame and finally, it was an hardware switch issue intermittently freezing. Unix tools such as truss,tusc and strace can be used to debug those scenarios if above techniques are not sufficient. But, tools such as truss and strace should be used as a last resort.

Dtrace is safer by design. Brendangregg has dtrace tool kit and wealth of information here. Switching to file systems providing better write throughput is one option. RAW devices are another option. Reducing of log file members in a group is another option as it reduces of write calls.

But, this option comes with a cost. Increasing priority of LGWR is a work around. If commit rate is higher, then decreasing commits is correct step but, in few case, if that is not possible, increasing priority of LGWR using nice or increasing priority class of LGWR to RT might provide some relief.

Solid State Disk devices also can be used if redo size is extreme. In that case, it is also preferable to decrease redo size. If excessive redo size is root cause, redo size can be reduced using various techniques.

More information can be found here. In summary, finding and resolving root cause for log file sync event, would improve application response time.

This blog can be found as pdf file here. This entry was posted on July 7, at pm and is filed under Performance tuning. Tagged: LGWR , log file sync , oracle , performance. You can follow any responses to this entry through the RSS 2. You can leave a response , or trackback from your own site.

I am currently investigating performance problem of an APPS database. The average log file sync wait time is about 10 to 20 times the log file parallel write time. I thought that I was hitting bug its 9. I always thought that a commit resulted in always 1 log file parallel write. Statspack however shows that the number of log file parallel writes is more than 2 times the number or log file syncs. Do you have any clue about why log file sync is so much higher than the log file parallel write?

If 1 log file sync result in about 2 log file parallel write I would expect the ratio to be a bit higher than 2 but not about Hello Hans-Peter Thanks for visiting my blog. LGWR processing is much more than just writes. It is quite possible that another phase of LGWR processing has a bottleneck. Since this version is 9. Do you have any other issues in OS?

High CPU Usage? Paging, swapping etc? What OS are you using? Depending upon that, You could potentially use couple of methods:. Truss or strace lgwr to see if there are any bottlenecks [ Very dangerous since truss or strace can cause LGWR crash and instance crash ] 3. Infact Jonathan Lewis linked me through to your article. You can take that as a compliment Well it is on Solaris10 but I do not have root access. Following section introduces internal workings of commit and LGWR interation in unix platform.

This section is to introduce internals, not necessarily dive deep in to internals. Truss is used to trace LGWR and user process to explain here. You can accidentally cause performance issues, worse yet, shutdown database.

When a session commits, a redo record created and copied in to log buffer. Then, process goes to sleep with semtimedop call, in its own semaphore. Semaphore set id is 9, but semnum is which is for the user process I was tracing. Waiting log writer gets a 0 return code from semtimedop and writes redo records to current redo log file. After successful completion of write s , LGWR Posts semaphore of waiting process using semctl command. Commit is not complete until LGWR writes log buffers including commit redo recods to log files.

Click image for details:. Discussion Real name:. Enter your comment:. Please fill all the letters into the box to prove you're human.

Subscribe to comments. Except where otherwise noted, content on this wiki is licensed under the following license: CC Attribution-Noncommercial-Share Alike 4. Decreasing log file sync waits Oracle Database Tips by Donald Burleson Question: I have "log file sync waits" in my top-5 timed events.

How do I tune to reduce the log file sync wait events? Oracle guru Steve Adams notes details on how Oracle processes log file sync waits:. There is also the possibility that bugs can cause high log file sync waits.