Tuesday, September 4, 2012

Recognize the magic optimizer numbers

Well I figured I document some of the magic numbers that the optimizer uses to help remember them, and help others. The back ground of this is simple.

I was looking through a query that was running for a long, long time, and the cardinality looked wrong.  I know the developers were using a table operation (looping over a LOB that was treated like  table). 

The Cardinality estimate for the step was 8168, and I thought hmmmm I've seen that before when dynamic sampling didn't happen.  Well after some digging I came across this page. Cardinality

The page contained this handy chart below...  These are important numbers to remember because when you see a cardinality matching this chart it is probably because the optimizer couldn't estimate the correct cardinality, and it couldn't dynamically sample.  Below is a snippet from the query I was investigating. Notice the cardinality on the first line.

(cr=0 pr=0 pw=0 time=0 us cost=29 size=16336 card=8168) 0 0 0 HASH JOIN RIGHT OUTER (cr=0 pr=0 pw=0 time=0 us cost=8757 size=233200 card=100) 0 0 0 VIEW (cr=0 pr=0 pw=0 time=0 us cost=8614 size=14 card=1) 0 0 0 HASH UNIQUE (cr=0 pr=0 pw=0 time=0 us cost=8614 size=2069 card=1) 0 0 0 FILTER (cr=0 pr=0 pw=0 time=0 us) 0 0 0 NESTED LOOPS (cr=0 pr=0 pw=0 time=0 us) 0 0 0 NESTED LOOPS (cr=0 pr=0 pw=0 time=0 us cost=8613 size=2069 card=1) 0 0 0 HASH JOIN (cr=0 pr=0 pw=0 time=0 us cost=8612 size=2044 card=1)

Default cardinality for database objects

The following table demonstrates the estimated cardinalities (using a 8K blocksize) of various objects which have had no statistics generated for them :

Object TypeEstimated Cardinality
Heap Table82
Global Temporary Table8168
Index-Organized Table1
System Generated Materialized View
(such as the output of the TABLE operator)

No comments:

Post a Comment