Oracle Scratchpad

April 23, 2009

Histogram change

Filed under: CBO,Histograms,Statistics,Troubleshooting — Jonathan Lewis @ 7:17 pm BST Apr 23,2009

When you upgrade from to or 11g, watch out for SQL that depends on the existence of frequency histograms.

In (and earlier versions of Oracle) if you have a frequency histogram on a column, and then use a predicate of the form:

where colX = {value not found in histogram}

then the optimizer would calculate a cardinality of one for this predicate (having recorded a density of 1/(2 * num_rows) in the data dictionary (see comments 21 and 22 of this note on dbms_xplan).


April 25, 2008

Cardinality Change

Filed under: CBO,Execution plans,Histograms,Statistics,Troubleshooting — Jonathan Lewis @ 8:13 pm BST Apr 25,2008

Here’s an interesting point that’s just been raised on the Oracle-L list-server:

Metalink bug: 5483301.8

If a column has a frequency histogram and a query uses an equality predicate over the column looking for a value that does not exist in the histogram then the CBO was estimating a cardinality of 1. This could favor Nested Loops too much.

The fix introduces a notable change in behavior: instead of estimating a cardinality of 1 then with this fix CBO estimates a cardinality of (0.5 * the lowest cardinality found in the histogram). This can result in plan changes.


« Previous Page

The Rubric Theme. Blog at


Get every new post delivered to your Inbox.

Join 3,530 other followers