Forum Stats

  • 3,759,951 Users
  • 2,251,621 Discussions
  • 7,870,879 Comments

Discussions

DBMS_STATS.GATHER_TABLE_STATS & DBMS_STATS.GATHER_SCHEMA_STATS

614497
614497 Member Posts: 209
edited Apr 3, 2008 10:14AM in General Database Discussions
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

Comments

  • Chris Antognini
    Chris Antognini Member Posts: 993 Blue Ribbon
    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
    591186 Member Posts: 3,668 Silver Trophy
    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
    Chris Antognini Member Posts: 993 Blue Ribbon
    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
    71610 Member Posts: 216
    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
    Chris Antognini Member Posts: 993 Blue Ribbon
    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
    614497 Member Posts: 209
    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
    Jaffy Member Posts: 1,234
    Hi,

    You can use for all columns in method_opt option.

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

    Regards

    Jafar
  • 591186
    591186 Member Posts: 3,668 Silver Trophy
    I agree.

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

    Thanks for correction.
This discussion has been closed.