Learn how to optimize Oracle COMMIT processing with the BATCH NOWAIT options!
COMMIT processing is the ultimate bottleneck in the Oracle database. You can’t process transactions faster than they can be committed, and the standard commit processing mechanism has waits built into it. This tutorial examines and demonstrates an alternative model (COMMIT WRITE BATCH NOWAIT) that can be very effective and easy to implement.
John will demonstrate one way to find issues with commits by showing an AWR report that reveals that 60% – 80% of the DB Time was spent waiting for wait event ‘log file sync’.
John then demonstrates reducing a commit-laden test procedure from 6.5 seconds to .44 seconds using
alter session set commit_logging=batch;
alter session set commit_wait=nowait;
When practical to implement, the commit batch nowait feature is a great, often non-invasive (no code change) way to dramatically improve performance.
Presenter: John Watson, Oracle Certified Master DBA
Download the scripts and slides for this presentation – https://skillbuilders.com/wp-content/uploads/2023/03/Tuning-Oracle-Commits.zip
Course Features
- Lecture 1
- Quiz 0
- Duration 9 minutes
- Skill level All levels
- Language English
- Students 0
- Assessments Yes
-
Tutorial
