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.
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 :-)
