ITPub博客

首页 > Linux操作系统 > Linux操作系统 > dbms_stats.gather_schema_stats的前期准备

dbms_stats.gather_schema_stats的前期准备

原创 Linux操作系统 作者:fjmingyang 时间:2019-05-30 18:15:05 0 删除 编辑

    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/,如需转载,请注明出处,否则将追究法律责任。

上一篇: libstdc++.so.6
请登录后发表评论 登录
全部评论

注册时间:2006-04-03

  • 博文量
    66
  • 访问量
    48552