dbms_stats.gather_schema_stats的前期准备

    There has been many discussion on the differences between analyze command and dbms_stats package on the internet.In this article ,my point is what should be done before gathering statistics. To prevent optimizer from choosing the bad plan after collecting the statistics,the original statistics should be saved!

step 1 : create a table to store the current statistics

      exec dbms_stats.create_stat_table(ownname=>'scott',stattab=>'stat_table');

step 2 : gather the statistics and save the original statistics as follows:

      begin

      dbms_stats.gather_schema_stats(

             ownname => 'test',

             options => 'GATHER',                        --Gathers statistics on all objects in the schema

             estimate_percent => dbms_stats.auto_sample_size,

             method_opt => 'all indexed columns size auto', --default 'FOR ALL COLUMNS SIZE AUTO'

             cascade => true, --this option is equivalent to running GATHER_INDEX_STATS procedure on each index

             statown => 'scott',

             stattab => 'stat_table'

             );

      end;

step 3 :

      If the user believes that the new statistics are causing the optimizer to generate poor plans,

      then the original statistics can be restored as follows:

      begin

             dbms_stats.delete_schema_stats(ownname => 'test');

             dbms_stats.import_schema_stats(

                    ownname => 'test',

                    statown => 'scott',

                    stattab => 'stat_table'

             );

      end;