Oracle Corner

When tuning SQL, I mostly use the event 10046, level 12 (or dbms_suport.start_sql_trace_in_session for easier typing) and tkprof afterwards. Rarely, when I need to understand why the optimizer has made the decision to use a certain path, I turn on the event 10053, level 1. This event catches the hard parse stage, so the statement needs to be "new", which is easily achieved with a unique comment or similar. The trace file is kind of upside-down, so I mostly start from the end and dig to the beginning (unless I am looking for the specific information).

The optimizer trace file has the following sections:

• Query itself
• Parameters used by the CBO
• Objects' statistics
• Table access paths' calculations
• Joins
• Final plan with adjustments

Metalink has a good article called "Case Study: Analyzing 10053 Trace Files". Also, if you Google "Wolfgang Breitling", you will get heaps of presentations he wrote and they are excellent.

Cost Formulas

I figured out the simple ones myself, as they are quite logical, but others come from WB's presentation and from Metalink.

Table Access Costs:

• Number of blocks/MbDivisor
• MbDivisor (multi block read divisor) is multi_block_readcount for <9.2v
• For Oracle >=9.2, if the system statistics are collected, Oracle will estimate it
• To find out what it is estimated to, run a simple query with the single table access path and then divide "NBLKS:/"Resc:"

Index access costs:

• Unique scan: blevel + 1
• Fast full scan: leaf_ blocks / MbDivisor
• Full scan: Num_blocks/MbDivisor
• Index: blevel + FF* leaf_ blocks
• Range scan: blevel + FF* leaf_ blocks + FF* clustering_ factor
• FF is a Filtering Factor, which is either the density if "=" is used or a formula depending on the predicate.

Join access costs:

• NL join: COST(outer) + [ CARD(outer) * COST(inner)]
• Merge join: Resc(outer) + Resc(inner) + Sort_Cost(outer) + Sort_Cost(inner)
• Hash join: Resp(outer) + Resp(inner) + Par_Sort_Cost(outer) + Par_Sort_Cost(inner)

Note that if the index does not have statistics, you will get the default numbers in the file. The default stats for LEAF_BLOCKS=25, BLEVEL=1, DISTINCT_KEYS=100 and CLUSTERING_FACTOR=800.

For a table, you will get a message that the table is not analyzed. Default assumptions for a table are: 100 block, 100 rows, even data distribution.

What to look for

In the example below the index access is chosen for the table OBJECT_LINKS, with the cost of 4. It initially chooses the wrong index (path 4: *FK2), but later, when optimizer considers the joins, the index with better selectivity looses to the index with less I/Os.

**************************************
SINGLE TABLE ACCESS PATH
Column: TO_OBJECT_ Col#: 3 Table: OBJECT_LINKS Alias: OBJECT_LINKS
NDV: 6543450 NULLS: 10 DENS: 1.5282e-07
HEIGHT BALANCED HISTOGRAM: #BKT: 200 #VAL: 196
Column: LINK_CODE Col#: 2 Table: OBJECT_LINKS Alias: OBJECT_LINKS
NDV: 2 NULLS: 0 DENS: 5.0000e-01
FREQUENCY HISTOGRAM: #BKT: 5144 #VAL: 2
Column: STATUS Col#: 7 Table: OBJECT_LINKS Alias: OBJECT_LINKS
NDV: 3 NULLS: 10387994 DENS: 3.3333e-01
HEIGHT BALANCED HISTOGRAM: #BKT: 3230 #VAL: 2
TABLE: OBJECT_LINKS ORIG CDN: 10800497 ROUNDED CDN: 1 CMPTD CDN: 1
Access path: tsc Resc: 6440 Resp: 6440 -- Path 1, full table scan
Skip scan: ss-sel 0 andv 9169389
ss cost 9169389
table io scan cost 6440
Access path: index (no sta/stp keys) -- Path 2, via CNS* index
Index: CNS_OBL_UNQ
TABLE: OBJECT_LINKS
RSC_CPU: 0 RSC_IO: 113262
IX_SEL: 1.0000e+00 TB_SEL: 7.6412e-08
Access path: index (equal) -- Path 3, via *CB2 index
Index: I_OBJECT_LINKS_CB2
TABLE: OBJECT_LINKS
RSC_CPU: 0 RSC_IO: 4
IX_SEL: 0.0000e+00 TB_SEL: 7.6412e-08
Access path: index (equal) -- Path 4, via *FK2 index
Index: I_OBJECT_LINKS_FK2
TABLE: OBJECT_LINKS
RSC_CPU: 0 RSC_IO: 4
IX_SEL: 0.0000e+00 TB_SEL: 1.5282e-07
BEST_CST: 4.00 PATH: 4 Degree: 1
***************************************

Table scan has a cost of 6440.
Index scan via CNS_OBL_UNQ has a cost of 113262.
Index scan via I_OBJECT_LINKS_CB2 has a cost of 4; both index columns are used in the query, better selectivity, so it appears to be a better candidate, while optimizer chose FK2.
Index scan via I_OBJECT_LINKS_FK2 has also cost of 4, so it is possible that optimizer just picks one of the index for the best cost, not necessarily planning to use it till the end.

***************************************
NL Join
Outer table: cost: 6 cdn: 1 rcz: 30 resp: 6
Inner table: OBJECT_LINKS
Access path: tsc Resc: 6440
Join: Resc: 6446 Resp: 6446
Access path: index (iff)
Index: CNS_OBL_UNQ
TABLE: OBJECT_LINKS
RSC_CPU: 0 RSC_IO: 17191
IX_SEL: 0.0000e+00 TB_SEL: 1.0000e+00
Inner table: OBJECT_LINKS
Access path: iff Resc: 17191
Join: Resc: 17197 Resp: 17197
Access path: index (no sta/stp keys)
Index: CNS_OBL_UNQ
TABLE: OBJECT_LINKS
RSC_CPU: 0 RSC_IO: 113261
IX_SEL: 1.0000e+00 TB_SEL: 1.3622e-07
Join: resc: 113267 resp: 113267
Access path: index (join index)
Index: I_OBJECT_LINKS_CB2
TABLE: OBJECT_LINKS
RSC_CPU: 0 RSC_IO: 17
IX_SEL: 0.0000e+00 TB_SEL: 1.3054e-07
Join: resc: 23 resp: 23
Access path: index (join index)
Index: I_OBJECT_LINKS_FK2
TABLE: OBJECT_LINKS
RSC_CPU: 0 RSC_IO: 29
IX_SEL: 0.0000e+00 TB_SEL: 1.5282e-07
Join: resc: 35 resp: 35
Best NL cost: 23 resp: 23
Join cardinality: 1 = outer (1) * inner (9225774) * sel (1.5947e-07) [flag=0]
***************************************

Join with the serial cost of 23 is chosen and IO cost of 17, which is *_CB2 index.

Generally, it is sometimes interesting to see how optimizer does not even consider some objects. If you think they need to be considered, check whether stats or histograms are missing.