Friday, November 25, 2011

"log file sync" while performing COMMIT and ROLLBACK


When the performance of the database go slow then we go for the ADDM and looks at the symptoms and immediately starts changing the system to fix those symptoms. In my case , i found that the "commit and rollback" has major impact on the slowness of the database . The finding points says  : 
Waits on event “log file sync” while performing COMMIT and ROLLBACK operations were consuming significant database time.

The Recommended action suggest is

Investigate application logic for possible reduction in the number of COMMIT operations by increasing the size of transactions. 

Since,the application is committing too ,so we have to reduce the frequent commit . The reason for the log sync waits occur when sessions wait for redo data to be written to disk. Typically this is caused by slow writes or committing too frequently in the application. Checking the "user commits" section in the AWR report can reveal if the issue is related to frequent committing.  

The following  tips may help to reduce log file sync when writes are slow :

  • Tune  LGWR  to get good  throughput to disk . eg: Do not put redo logs on RAID 5 .
  • If there are lots of short duration transactions, see if it is possible to BATCH transactions together so there are fewer distinct COMMIT operations. Each commit must confirmed that the relevant REDO is on disk before it can complete. Although commits can be "piggybacked" by Oracle, reducing the overall number of commits by batching transactions can have a very beneficial effect.
  • Check the size of the log buffer size 
  • Check the estimated size of the redo log file . Also check how fast the redo logfile is switching 
  • Check if any of the processing can use the COMMIT NO-WAIT option  

In 11g the commit_write  parameter is deprecated . It is retained for backward compatibility only. It is replaced by the COMMIT_LOGGING and COMMIT_WAIT parameters. It is retained for backward compatibility only. It is replaced by the COMMIT_LOGGING and COMMIT_WAIT parameters.For more check the meta-link [857576.1]

As in my case i have perform the following steps and find that the performance of the database has increased and the impact of "COMMIT and ROLLBACK"  is too low. Here are steps

1.) Estimate the size of the Redolog file :  The value for optimal_logfile_size is expressed in MB. This value changes frequently based on the DML load on your database.Use the below query to calculate the estimated size of the redo logfile .

SQL> select   TARGET_MTTR "trgt_mttr",  ESTIMATED_MTTR "est_mttr",  WRITES_MTTR  "wrt_mttr",  WRITES_LOGFILE_SIZE  "wrt_lg_size", OPTIMAL_LOGFILE_SIZE  "opt_lg_size"  from    v$instance_recovery ;

 trgt_mttr     est_mttr     wrt_mttr    wrt_lg_size     opt_lg_size
----------     ----------     --------       ------------     -----------
         0           227          0                    52                300


After this i find that redolog file switch after every 3-4 minutes. Hence, I have increased the size of the redo logfile to 300 MB and the check the log switch. I found that the redo log file switches after approax every 20min . 

Since ,I have set the sga_target ,therefore cannot change the log buffer size. In my case my redolog file is approax 5.5MB . Some expert also suggest to increase the size of log buffer to 10MB or more to reduce the impact of the "commit and rollback ". Though , I have  not tested this and i will come later on this .  

This is what the workaround , i have perform to reduce the "commit and rollback" impact .


Enjoy    :-) 


5 comments:

Anonymous said...

Hi can the background image be changed, so your article can be visioned and read easily

Anonymous said...

I do agree with all of the ideas you've offeгed on yοur pօst.
Theʏ are really convincing aand will ɗefinitely
work. Nonetheless, tҺe posts aгe too quick foг novices.
May just you please extend them a bit from next
time? Thanks for the post.

my blog - bed bug bites

Beneen said...

Hi when I run the query to find the optimal log size there is a null value for that specific column?

Anonymous said...

Hi when I run the query to find the optimal log size there is a null value for that specific column?

Anonymous said...

SALAM & Hey, When I run the query to find the optimal log size there is a null value for that specific column?