313: SQL Tuning with 10046 Trace Data and DBMS_XPLAN
Mahesh Vallampati , Hotsos    Biography

Objectives: 10046 Trace Files and STAT lines structure in 9i, 10g and 11g
DBMS_XPLAN and how to use it effectively
Use STAT lines and DBMS_XPLAN to tune SQL statements quickly and effectively
Abstract: The 10046 trace data from the Oracle Database at level 12 contains valuable information about the execution of the SQL statement in the form of stat lines. These stat line contain information about the row source execution statistics of the SQL statement. This information in conjunction with DBMS_XPLAN and applying Method R can be used to quickly identify where in the SQL statement most of the time is being spent and focus on that part of the SQL statement and studying it in detail. This method works no matter how complex the SQL statement is. In this presentation, we will review the stat lines, how they can be formatted so they can be studied effectively and how this can be used in conjunction with DBMS_XPLAN to quickly get to the part of the SQL statement that is consuming most of the time in the execution of that SQL statement.

Audiernce Focus: Developers
Expertise: Beginner
Track: Database
Date: Monday, April 14, 2008
Time: 1:00 PM - 2:00 PM