Re: How to speed analyzing histograms

>you are suggesting FOR ALL COLUMNS SIZE SKEWONLY, so
>that the columns that appear in SYS.COL_USAGE$ will be the
>only ones getting histograms?

I dont think this is what happens when you specify SKEWONLY. When you specify SIZE SKEWONLY, Oracle generates Histograms in memory for all columns but it stores the histograms only for those columns which have a skew
in data distribution.

When you specify SIZE AUTO, then oracle generates Histograms for all columns
which are used by the application (as identified in SYS.COL_USAGE$).

What Jonathan said (as it always is) is absolutely right. There is no point in gathering histograms for all columns. (A 2 bucket histogram is always generated for each column when you generate table stats).
Even All columns which appear in SYS.COL_USAGE$ do not need histograms.

I would take a careful look at the popular or costly SQL to identify the columns needing histograms,
look at SYS.COL_USAGE$ and generate Histograms for the needed columns and later on use SIZE REPEAT for regeneration.

regards
srivenu Received on Sun Nov 13 2005 - 00:15:26 CST