event-icon
Description

Session ID: 565

Abstract:

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

Audience: Mid-Level

Tags