Session ID: 565
In this presentation I will show you how to find the DML SQL associated with a commit. Using an AWR report won't help because there isno "Top DML SQL" section to the report. Analyzing AWR SQL data is also problematic because it is based on multi-minute snapshots and summarized data.
One of our clues is the wait event associated with a commit is, log file sync. This is not as simple as it seems because a commit has no SQL_ID. So, how are we supposed to find the application DML SQL associated with a commit?
With ASH sample session based data, we have a chance to solve this mystery. By isolating an active log file sync session and closely looking at the session activity, we can infer the DML SQL associated with a commit.
This process pushes ASH data analysis to the limit by using a variety of sample based data analysis techniques. Join me as we diagnose one of the most complicated and frustrating of all Oracle performance issues.
Objective 1: The problem of AWR report log file sync diagnosis
Objective 2: Understanding log file sync event and commits
Objective 3: How to diagnose a log file sync issue using ASH data