Re: Creating Histograms
- From: Wolfgang Breitling <breitliw@xxxxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Wed, 21 Jul 2004 16:51:56 -0600
I said that I consider it unlikely, even in this case, that the plan will
change if the statistics are not refreshed - unless the range of the
last_update_date values is rather narrow or it has a histogram. I found
that histograms often need to be refreshed much more frequently ( this not
an exception to my rule of not analyzing unless a benefit is established
but a confirmation: the benefit is established). It very much depends on
the column and its usage. If, for example, you have a status column with
two values: 'P' for processed and 'N' for not processed. One of your batch
processes loads rows into the table with a status of 'N', typically
~200,000 (into a multi-million row table). Another batch process picks out
those 200,000 not-yet-processed rows (where status = 'N') and, when done
changes the status to 'P'. The next day the process repeats. This is a case
where an index on status and a histogram makes a big difference. But to the
optimizer it does not matter that the 200,000 status 'N' rows are different
rows every day. It is only interested in the fact that they are a small
minority and that the use of the index makes sense. In this case it is not
necessary to refresh the histogram every day.
At 04:06 PM 7/21/2004, you wrote:
>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.
Regards
Wolfgang Breitling
Centrex Consulting Corporation
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
-----------------------------------------------------------------
Other related posts:
