1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Sintax to DBMS_STATS - Help

Discussion in 'SQL PL/SQL' started by mariogus, Jan 2, 2014.

  1. mariogus

    mariogus Guest

    My syntax is correct for the commands below?

    current
    DBMS_STATS.GATHER_TABLE_STATS (ownname => SYS_CONTEXT ('USERENV', 'CURRENT_SCHEMA'), tabname => 'PLANT_ALLOCATION');

    suggested

    DBMS_STATS.GATHER_TABLE_STATS (ownname => SYS_CONTEXT ('USERENV', 'CURRENT_SCHEMA'), tabname => 'PLANT_ALLOCATION' estimate_percent => dbms_stats.auto_sample_size, cascade => TRUE, METHOD_OPT => 'FOR ALL COLUMNS SIZE AUTO');

    and
    current
    execute immediate 'call dbms_stats.gather_schema_stats (''' | | DESTINE | |'' ')';
    ****
    suggested

    execute immediate 'call dbms_stats.gather_schema_stats (''' | | DESTINE | |'' ', options =>' GATHER AUTO ', estimate_percent => dbms_stats.auto_sample_size, cascade => TRUE, METHOD_OPT =>' FOR ALL COLUMNS SIZE AUTO ') ';
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Why are you using SYS_CONTEXT as an argument? If ownname is NULL then it defaults to the current schema. Also you are obviously expecting this to be run under a specific schema as you are listing a specific table name. In addition estimate_percent, by default in 10g and later releases, uses dbms_stats.auto_sample_size so that is redundant an therefore unnecessary. I suggest the following syntax:


    exec dbms_stats.gather_table_stats(tabname=>'PLANT_ALLOCATION', method_opt =>'FOR ALL COLUMNS SIZE AUTO')
    exec dbms_stats.gather_schema_stats('DESTINE', method_opt=>'FOR ALL COLUMNS SIZE AUTO')


    Your proposed statements make this far more complicated than it needs to be.