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;
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