Optimizer debug trace event – 10053 trace file

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

Advertisement

Direct path inserts, nologging option and index cost

This is something confuses lots of us, a direct-path insert will only produce minimum redo(not no redo) if you are using a database in NOARCHIVELOG mode or you are doing this operation on a table which is marked as NOLOGGING.

Some other important points are;
– “with all insert statements append hint will produce minimum redo”, this is not true and actually only with bulk inserts(insert into select from) this feature works.
– only one session can at a time can direct path insert into a table.
– with append you can NOT use the free space below your high water mark.
– during direct load if there are indexes on the table, if you set the indexes as UNUSABLE and rebuild them after the load, redo produced minimizes dramatically.

Folowing is a demostration of these behaviours with autotrace feature;

Code listing 62 : Direct inserts with append hint and Nologging option

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

Refences Used :
Effective Oracle by Design by Thomas Kyte Chapter 2-Your performance toolkit, Page 116