CBO: statistics collection

From: <wagen123_at_yahoo.com>
Date: 21 Feb 2005 17:06:15 -0800
Message-ID: <1109034375.408630.219550@g14g2000cwa.googlegroups.com>

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 Received on Mon Feb 21 2005 - 19:06:15 CST