CBO Plans and Histograms

Its been a long time since i have blogged.

There was an upgrade that happened in one of our systems.We had upgraded recently from 9i to 10g and found an issue with one of our queries.

The Query was not having an issue with 9i but came as a sudden surprise after the upgrade.We had decided to go with 10g Automatic Statistics Collection and with Histograms on key columns.This particular is one which is executed many times and we wanted that this query be a good perfromant.

While doing our testing we did observe that there were some days when this query was performing well and some days it was absolutely barmy!!.

Tyring to bottom out the issue we could confirm some one else had found this issue earlier and before the start of the test they made sure that the statistics on the tables referenced in this query are refreshed.But the Developer being novice to the DBMS_STATS prefered to use the simple ANALYZE option.

Given the above story we came to a conclusion that the freshly gathered STATS were performing well and later on when oracle runs the Automated Stats Collection job,we are back to the problem.

Investigation showed that the density value for one of the columns was showing an absurd value.This lead us to conclude that the problem is with histograms and histogram statistics are reflected only when the automatic stats job comes into picture.

We raised the issue with oracle support and the answer was this is a bug.

Bug 5483301 --- QUERY WITH PREDICATE VALUE NON-EXISTENT IN FREQUENCY
HISTOGRAM RUNS SLOW

When cardinality estimate is very low resulting in the use of Nested loops join with poor performance, and low estimate is due to the presence of a frequency histogram on a column of an equality predicate with a value that is not in the frequency histogram, then you are likely facing this issue.

WORKAROUND:
Drop the frequency histogram that is on the predicate column.

I raised the same with Jonathan Lewis, this is what he had to say about this:

I'm surprised they call that a bug - it's necessary feature of the optimizer,although a slightly extreme one.

If you query the database for a value that is "known" not to exist then the ptimizer has to assume that there is at most one row with that value, which often makes the table the driving table.If there are lots of rows with that value then the performance can be catastrophic.

A potentially better workaround is to use dbms_stats.set_column_stats to set the density to a value that reflects the fraction of the table you expect to get for the 'unregistered' values.

We adopted the above solution and locked the table statistics so that they are not refreshed when the Auto Stats Job Kicks in.