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;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/1698901/viewspace-218032/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/1698901/viewspace-218032/