Extract(Read) > Transform(Test&Apply) > Load(Learn) Blog

January 20, 2007

Optimizer debug trace event – 10053 trace file

Filed under: Oracle Performance — H.Tonguç Yılmaz @ 7:53 pm

In the first part, Understanding The Fundamental Performance Equation I mentioned the importance of the fundamental performance equation, how to produce SQL Trace files and analyze them with tkprof utulity. A good understanding of SQL processing is essential for writing optimal SQL statements and these trace files help us to understand what happened behind during execution.

I always wondered how and why Oracle optimizer decides to choose one execution plan to other. Sometimes Oracle seems to be ignoring something that you think it shouldn’t, maybe a hint or a join condition. Usually the best way to work out what’s going on is to look at the optimizer debug trace event, 10053 trace file. Maybe you are hitting a CBO bug and this trace file offers best for you to show Oracle support something unexpected happening.

I tried to create a small demostration for a better understanding of this event dump, following script creates a table with a primary key and unique key constraints and the CBO decides to use the unique key constraint over primary key;

Code listing 63 : Optimizer debug trace event, 10053 trace file

The trace file we produce has lots of information as expected; all session optimizer parameter values and every table, index and possible access path statistics etc. Some abreviations used maybe problematic to understand, here’s a quick explanation of what the abbreviations mean:

CDN Cardinality, a count of rows
NBLKS Number of blocks
AVG_ROW_LEN The computed average row length
COL# Column numbers in the table the index is on
LVLS Number of levels in the B*Tree
#LB Number of leaf blocks
#DK Number of distinct keys
LB/K Number of leaf blocks per key value on average
DB/K Number of base table data blocks per key value; how many table accesses (logical I/O’s) would be made using an equality predicate on this index. Directly related to the cluster factor below.
CLUF Clustering factor of this index; a measure of how sorted a base table is with respect to this index.
resc Serial cost
resp Parallel cost

The 10053 trace file can be big if you are working on lots of joined tables since every possible combination will be dumped to the trace file. This trace event is not documented, but from a search on google you can find several good resources easily. Cost-based query optimization is something we must call the real computer science, a hard thing to do and with every new release Oracle optimizes the code dramatically. So lets enjoy it :)

Testing Information : the scripts mentioned are tested on Oracle Database 10g Express Edition Release 10.2.0.1.0

References Used :
Cost Based Oracle Fundamentals by Jonathan Lewis Chapter 14-The 10053 Trace File, Page 403
10053 thread on Asktom
“A Look under the Hood of CBO – the 10053 Event” by Wolfgang Breitling
Note:338137.1 – CASE STUDY: Analyzing 10053 Trace Files

7 Comments »

  1. […] Continue reading with Part 2 Optimizer Debug Trace Event – 10053 Trace File. […]

    Pingback by Introduction to Oracle Trace Utulity and Understanding The Fundamental Performance Equation « H.Tonguç YILMAZ Oracle Blog — January 20, 2007 @ 7:55 pm | Reply

  2. […] you ever used 10053 or 10046 trace files, you may like what you find inside this big trace file, this time from the […]

    Pingback by Debugging RMAN « H.Tonguç YILMAZ Oracle Blog — August 21, 2007 @ 1:50 pm | Reply

  3. Interpreting Event 10053 trace file example –
    http://www.ubtools.com/jira/browse/QA-31

    Comment by H.Tonguç Yılmaz — September 15, 2007 @ 7:14 pm | Reply

  4. In Oracle 10.1 and above, optimizer environment variables are also externalized at;
    * instance level – V$SYS_OPTIMIZER_ENV
    * session level – V$SES_OPTIMIZER_ENV
    * statement level – V$SQL_OPTIMIZER_ENV

    Comment by H.Tonguç Yılmaz — November 24, 2007 @ 3:29 pm | Reply

  5. […] another change on optimizer parameter to fix Query B brings you back to step 1 :) Use 10046 and 10053 events for detail analysis of CBO decisions, changing the value of an optimizer parameter for a […]

    Pingback by Oracle Best Practices Part 5 « H.Tonguç Yılmaz - Oracle Blog — April 24, 2008 @ 5:50 pm | Reply

  6. To collect the event 10053 trace file, the following syntax was used in SQLPlus:

    SQL> connect / as sysdba
    SQL> oradebug setmypid
    SQL> oradebug unlimit
    SQL> oradebug event 10053 trace name context forever, level 1
    SQL> …enter your query here…
    SQL> oradebug event 10053 trace name context off
    SQL> oradebug tracefile_name
    /../../udump/.._ora_…trc

    The output of “oradebug tracefile_name” will point to the 10053 trace file.

    Comment by H.Tonguç Yılmaz — May 4, 2008 @ 5:25 pm | Reply

  7. Case Study: Analyzing 10053 Trace Files
    https://metalink.oracle.com/metalink/plsql/docs/CS_10053.htm

    Comment by H.Tonguç Yılmaz — May 4, 2008 @ 5:27 pm | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: