Re: CBO: statistics collection

wagen123_at_yahoo.com wrote:

> Oracle8i 8.1.7.4 (64 bit)
> Solaris 2.9
> OLTP db
>
> We generate statistics once a day using the "analyze table xyz estimate
> statistics". Will be switching to DBMS_STATS soon after migrating to
> 9i/10g. One fine day a specific query which used the index started
> doing full table scans. Creating a histogram (10 buckets) fixed the
> problem. Looks like skewed data.
>
> There are many queries that search on columns that are much more skewed
> than the above example, but we don't see a problem here.
>
> How does one go about determing when to generate statistics using
> histograms? What should be the strategy from an overall db perspective?
>
> Any pointers?
>
> Thanks

When there is a defined problem based on testing or user complaints.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)