1. The Forum has been upgraded to a modern and advanced system. Please read the announcement about this update.
  2. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Oracle Wait Event: Log File Switch Completion

Discussion in 'Oracle Database General' started by prashant, Oct 26, 2013.

  1. prashant

    prashant Forum Advisor

    Messages:
    85
    Likes Received:
    7
    Trophy Points:
    160
    Location:
    New Delhi, India
    Hi,

    I would like to share my experience with Tuning of Log File Switch Completion.

    Background

    One of our critical setups reported a delay in Call processing and when an AWR was taken we saw Log File Switch Completion as the top most wait event. In one hour we had Event reported 8 times with average time around 1.5 seconds. This was blocking call processing for 1.5 seconds which was unacceptable during peak load of 30 Calls per second. 'Log File Switch Completion' wait event will block all transactions until it is complete.

    Setup Details:-

    1. Oracle 11.2.0.3.6 Database
    2. Data Guard Setup - Single Primary and Single Physical Standby in Max Availability Mode
    3. High speed network, latency is never a problem here.


    Approach
    =========
    The solution was 2 way:-

    1. Optimize the number of Redo log Switches - As a general rule of thumb, it should not be more than 3-4 per hour during peak load.
    2. Optimize the Redo Log Switch Time.

    Addressing point number 1 was very straight forward. Our Redo logs were 256MB in size. We increased them to 512MB and Redo log switches per hour became 3-4. This certainly reduced number of failures per hour by half but further tuning was still required.

    For Point number 2, after some research, we learnt that during Redo Log Switch, Oracle Writes the required information in all the control files on the system SEQUENTIALY and not in Parallel, this happens on both Primary and Standby. We reviewed the setup and found that we have 3 Control files on mount points with internal Hardware Level RAID (1+0).

    Since we already had enough Fault Tolerance with respect to our setup and our control files are backed up on a frequent basis, we decided to keep just one control file instead of 3 on both Primary and Standby.

    This reduced our redo log switch time by about 60%.

    One thing to note is that our System did not have a superfast IO. For systems with High end SAN storage and enough Write Cache, point number 2 might not be required.