How does DBMS_STATS select columns to generate histograms?

From: Mladen Gogala <gogala_at_sbcglobal.net>
Date: Tue, 22 Mar 2005 12:42:23 +0000
Message-Id: <1111495343l.28786l.0l@medo.noip.com>

DBMS_STATS, when given option 'GATHER AUTO' generates statistics for object= s which=20
do not have statistics and objects which have stale statistics. It also cho= oses
columns for which to generate histograms. This choice is based on the SYS.= COL_USAGE$
table, which is maintained by CBO. The table looks like this:

SQL> desc sys.col_usage$

 Name                                      Null?    Type
 ----------------------------------------- -------- -----------------------=


-----   	=09

 OBJ#                                               NUMBER
 INTCOL#                                            NUMBER
 EQUALITY_PREDS                                     NUMBER
 EQUIJOIN_PREDS                                     NUMBER
 NONEQUIJOIN_PREDS                                  NUMBER
 RANGE_PREDS                                        NUMBER
 LIKE_PREDS                                         NUMBER
 NULL_PREDS                                         NUMBER
 TIMESTAMP                                          DATE

SQL> Does anybody know the where clause used to select columns which will have histograms? Is there a fixed cutoff number (EQUALITY_PREDS>=3D x)? Percenta= ge?
This table is extremely interesting because it can be used for adding index= es.

--=20

Mladen Gogala
Oracle DBA

--

http://www.freelists.org/webpage/oracle-l Received on Tue Mar 22 2005 - 07:45:58 CST