RE: Creating Histograms
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 21 Jul 2004 18:23:01 -0400
Ah, now here is a challenge to throw down to the Oracle auto everything
group: Sample my database and use thereof over time to calculate when the
workshift weighted value of resources is effectively devoted to statistics
recalculation.
So, in a given operational window, do I have cycles to burn, and if so, are
they well burned bringing certain statistics up to date?
In the meantime, thankfully, there remains a task requiring sense:
Determining which statistics it is useful to recalculate, to what level of
computation, and histogramization, and how often.
Statistics generations can range from being the defrag extents for the sake
of defragging of our day to an essential operation depending on the case in
hand.
mwf
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Wolfgang Breitling
Sent: Wednesday, July 21, 2004 6:06 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: Re: Creating Histograms
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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
