DBMS_STATS.GATHER_TABLE_STATS & DBMS_STATS.GATHER_SCHEMA_STATS

614497

Hi All,

In the GATHER_TABLE_STATS or GATHER_SCHEMA_STATS procedures if for parameter method_opt we use 'FOR ALL INDEXED COLUMNS size skewonly', does this mean we only collect stats for index columns or collect stats for all columns in the table?

Thanks a lot,

Shirley

Chris Antognini

Shirley

Since the syntax is FOR ALL INDEXED COLUMNS, you are gathering statistics for all indexed columns only. I.e. not for all columns. FOR ALL COLUMNS should be used for that...

HTH
Chris

591186

FOR ALL INDEXED COLUMNS size skewonly',

This is for generating histograms on the indexed columns where column data is skewed. Collects the optimizer statistics for the table, columns, indexes and histograms for columns where the data shape is skewed. Only collects histograms for skewed column. In general it is slower to execute therefore it would not be suitable when the optimizer statistic collection window is short.

EXECUTE DBMS_STATS.GATHER_TABLE_STATS(
ownname => '<table owner>',
tabname => '<table name>',
method_opt => 'FOR ALL COLUMNS SIZE SKEWONLY',
granularity => 'ALL',
cascade => TRUE,
degree => DBMS_STATS.DEFAULT_DEGREE)

Chris Antognini

Anantha

This is for generating histograms on the indexed columns
where column data is skewed. Collects the optimizer
statistics for the table, columns, indexes and histograms
for columns where the data shape is skewed.

This is wrong. Sorry. When the option FOR ALL INDEXED COLUMNS is specified, columns statistics are gathered only for the indexed columns.

Best regards,
Chris

71610

Hello All:
I also believe when you specify "FOR ALL INDEXED COLUMNS " in the method_opt , it generates histogram for the said columns.

Thanks
S~

Chris Antognini

Hi

I also believe when you specify "FOR ALL INDEXED COLUMNS " in
the method_opt , it generates histogram for the said columns.

If the size clause is not specified, whether the histograms are gathered depends on the version and, as of 10g, on the configuration.

My point was that the parameter method_opt not only impacts histograms, but also column statistics.

Cheers,
Chris

614497

Thanks, Chris.

If I want to gather stats for all columns in a table what value I should pass on to parameter method_opt?

Shirley

Jaffy

Hi,

You can use for all columns in method_opt option.

http://www.psoug.org/reference/dbms_stats.html

Regards

Jafar

591186

I agree.

method_opt => 'FOR ALL COLUMNS SIZE SKEWONLY', - For Generating Histograms.

Thanks for correction.