Re: Creating Histograms

From: Wolfgang Breitling <breitliw_at_centrexcc.com>
Date: Wed, 21 Jul 2004 16:06:07 -0600
Message-Id: <6.1.0.6.2.20040721154521.031722b8@pop.centrexcc.com>

There are always exceptions but I firmly believe that in the majority of cases the plans do not change as long as the statistics do not. You have to know those cases where it is necessary to refresh the statistics after certain events.

Even in your extreme case I am not certain that the plan does change. Potentially if there is a rather small range of last_update_dates or if there is a histogram on last_update_date. Without a histogram, the selectivity of "col >= value" is (col.hi - value) / (col.hi - col.lo) + 1/col.ndv (there may be exceptions but generally that's the selectivity). Unless value is close to col.lo - i.e. as long as the range extents well beyond the 28 days, the selectivity will change only marginally as value approaches col.hi (which doesn't change without refreshing the statistics). Granted, even a small change in selectivity can cause a change of plan, but it is not very likely. Once value is >= col.hi, the CBO just uses 1/col.ndv as selectivity, as far as I could determine, which won't change at all without statistics refresh and thus the plan won't change if you make it through the 28 days without statistics refresh.

At 03:25 PM 7/21/2004, you wrote:

> >> Otherwise I practice "plan stability" meaning "if the statistics
> >> don't change, the plans won't".
>
>Beg to differ - for example:
>
>The fixed predicate:
> last_update_date >= "calculated constant of 28 days ago"
>is likely to have a selectivity that changes as time passes,
>with a possible change in plan, precisely BECAUSE
>the statistics haven't changed.

Regards

Wolfgang Breitling
Centrex Consulting Corporation
http://www.centrexcc.com

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Wed Jul 21 2004 - 17:02:46 CDT