RDBMS-- LGWR Waits & Advantage of EXADATA in Log Writer IO

This post will be about Log Writer and the waits our sessions encounter while waiting the Log Writer to do its work..

LGWR writes commit records and writes enough information to recover transactions.
LGWR performance may impact negatively on OLTP response times.
For instance; in case of a commit operation, that is when  a session wants to commit, it posts the LGWR to write the redo information of its transactions and commit record, as well.
In this state, the session waits the LGWR and its wait event will be Log file Sync.. The session waits in this wait while LGWR is writing the redo information in to the redologs..
In details we can say that;

When a user session commits or rolls back , Oracle/LGWR needs to flush the contents of log buffer to the Redolog files. So, Log file sync occcurs in these operations.
When we wait for log file sync, we actually wait for; LGWR to wake up if idle, gather the redo to be written, issue IO (this IO is a log file parallel write, can be monitored using log file parallel wait event), process after the IO and acknowledge user session/foreground..
In addition to that, we wait for user session/foreground to wake up lastly.

So , in a matter of fact, the possible bottleneck of the log file sync seems the IO, which is log file parallel write. The possible bottlenecek is IO because , we rarely have insufficient cpu resources, and we suppose that the design of the code that makes LGWR  to wake up,  is optimized ..
Anyways; As a solution to do IO originating LGWR waits; we can always use async commits, but it is not recommended, because in async commit - > we tell the application we have committed, but there is no guarantee, because we dont commit synchronously actually..

Okay, lets try to characterize the causes of Log Writer Waits with and if statements :)

*If avg time per log file sync operation << avg wait time per log file parallel write operation -> IO is bottleneck.
*If the avg wait time per log file sync operation >> avg wait time per log -> CPU bottlenecek , Run queue problem or bug. In such a situation, check CPU consumption.. 

Okay, we said that Log file sync wait occurs if a process waits for LGWR to flush the data from the log buffer to redolog files. That is, the user process commits and waits on this event till LGWR will finish the data to the redolog files, in other words; till LGWR will send a signal indicating that the flush request is finished..

So far so good. Lets examine LGWR behaviour during its lifecycle;
This time bash script type if /end if statements :)

If LGWR is idle, I mean only waiting, it will wait on Rdbms ipc message..
If LGWR is updating headers of the redolog files, you will see log file single write waits..
End if LGWR is writing the redo data from log buffer to redolog group, you will see log file parallel write waits, as this operation can be done in parallel.

So all we see is write,write and write right :)?

Then write performance is very important for LGWR .. Write performance is very important for us too.. It is just because our sessions will wait for LGWR in one way or another..

CPU is very important too, because if our system cant schedule cpu for LGWR , we will wait LGWR to get scheduled actually. ,
On the other hand, cpu problems of LGWR are generally rare and when they appear, they appear to us obviously.
I mean; we can identify a Cpu problem by just using 2 commands in Linux.. 

That's why, I m living Cpu related tuning aside and will write about IO related problems in LGWR.

The next paragraphs will be based on my own practices and Andrew Holdsworth speech in 

Real-World Performance.

As Andrew Holdsworth, Vice President of Oracle Database Real-World Performance at Oracle Corporation,  said "When The thing in the bottom is delayed , everbody gets delayed" , the processes in the backbone of our database is very important for us to work smoothly..

In this context; In order to supply a proper environment for our LGWR to work, we need to be sure our IO Subsystem is well tuned..

Acutally, we need to tune all the IO paths under our Database Layer , but the rest of this post will be about the Storage Layer, as LGWR makes standart IO Not db IO, and we suppose and have seen our Linux/Unix systems are well tuned even when they are freshly installed.. (Filesystem - Storage alignment is another topic. I am living it aside, too)

Like Andrew says, Luns & Volumes are allocated on the basis of space, they are not allocated on the basis of performance. Or we think that they are allocated on basis of performance, but they are acutally not..
I mean , performance does not equal to the disk speed alone.. It is not like that.. We cant say we have a performanceful disk layout, only because we have a lun that is provided from a raid group and from a volume consisting several 15k rpm or SSD disks.

Once in the past, I replied back to one of my customer, who was thinking that its storage is very fast, and their performance problems are caused by the Oracle RDBMS , as follows;

"Your storage can not deal with this heavy IO requests coming from these costly Sql statements"

So , their storage was quite fast on paper , but eveything had a limit ..
So when there were a lot of unplanned IO requests coming to the Storage, the application (Oracle EBS ) was slowing down..
It could be clearly seen  in the AWR report though...
Tablespace IO Stats and File IO stats were showing the wait times in the datafiles..
Also Top 5 timed events section was displaying the IO related waits such as Db File scattered reads and DB File sequential reads..

Okay the real life example above was showing the effect of a slow IO device to Database Performance in general..  This example actually have shown us that although the storage is very fast, it can be slowed down by the large number of IO requests coming from the queries or transactions.. The reason can be write requests or read requests.. It does not matter acutally... At the end of the day, we ll have performance problems..

Lets come back to our topic and see this effect eespecially to LGWR in a real life example..
In one of the Real-World Performance videos of Oracle, Andrew Holdsworth clearly shows this. Okay I will keep in short.
When a lot of systems share the same Storage , we may see a negative performance effect in the LGWR writes, too..
Consider a scenario when a big warehouse and a big EBS database share the same Storage disks.. Or consider an ETL load process started in the warehouse during the peak hours of EBS.. What will happen?  A lot of things may happen actually :)
But in terms of LGWR performance, that 's quite predictable  :) if your storage can not deal with, it ; you may see log file sync waits in both EBS and Warehouse..
The question is ; is your storage capable of responding this kind of multiplexed IO request?
The answer to this question is "It depends actually ".. If you do your sizing in view of this fact, then you are fine.. On the other hand, this is not the case usually. We have seen this may times , that 's why I m writing this post.

One solution to this problem is putting the redologs to their own dedicated disks.. This way, we will be safe.. I mean our dedicated storage will not be affected from IO Storms caused by other databases/or applications which shares the same storage with our LGWR..

In EXADATA, we have a solution which is one step ahead from the solution above..
Withing Exadata, Oracle writes the redo information both to the smart logs which are in Flash devices and storage disks , and accepts the one that comes first..
So, If the disks in Exadata Storage are fast at the time our LGWR makes the write requests, we will have no problems.. If the disks are under pressure, our redo will be written to the flash , and we have no problems again..
In the following video, Andrew Holdsworth shows this in action , you may want to take a look..