oracle database 12cr2 使用 dbms_stat 采集统计信息详解数据库

dbms_stat 是oracle database 采集统计信息的集成工具。非常方便和高效。

备份

创建stat_table

begin 
    dbms_stats.create_stat_table( 
        ownname => 'drp', 
        stattab => 'stat_table' 
    ) ;  
end;

导出整个scheme的统计信息

begin 
   dbms_stats.export_schema_stats( 
        ownname => 'drp', 
        stattab => 'stat_table' 
   ) ;  
end;   

分析

begin 
   --固定表的统计信息 
   sys.dbms_stats.gather_fixed_objects_stats; 
   --数据字典的统计信息 
   sys.dbms_stats.gather_dictionary_stats; 
   --instance的统计信息 
   sys.dbms_stats.gather_system_stats(); 
end; 

抽样分析drp用户对象

BEGIN 
   sys.dbms_stats.gather_schema_stats( 
       ownname=> 'drp' , 
       estimate_percent=> 50 ,  
       cascade=> TRUE, 
       --method_opt=>'for all columns size 1 ' 
       --method_opt=>'for all columns size repeat ', 
       method_opt=>'for all indexed columns size skewonly ', 
       degree=>8 
   ); 
END ;

抽样分析drp.order表

BEGIN 
    dbms_stats.gather_table_stats( 
        ownname=> 'drp' , 
        tabname=> 'order', 
        estimate_percent=> 50 ,  
        cascade=> TRUE, 
        --method_opt=>'for all columns size 1 ', 
        --method_opt=>'for all columns size repeat ', 
        method_opt=>'for all indexed columns size skewonly ', 
        no_invalidate=>FALSE, 
        granularity=>'AUTO', 
        degree=>8 
    ); 
END ;

抽样分析drp.order表的idx_order_x1索引

BEGIN 
    sys.dbms_stats.gather_index_stats( 
        ownname=> 'drp' , 
        indname=>'idx_order_x1', 
        estimate_percent=> 100  
    ); 
END ; 

method_opt选项
for table–只统计表　
for all indexed columns–只统计有索引的表列
for all indexes–只分析统计相关索引
for all columns

在method_opt子句中，还有一些重要的新选项，包括skewonly，repeat和auto：
method_opt=>’for all columns size 1’
method_opt=>’for all columns size skewonly’
method_opt=>’for all columns size repeat’
method_opt=>’for all columns size auto’

删除

重新采集后发现效果不好的,可以删除统计信息

删除整个schema的统计信息

begin 
    dbms_stats.delete_schema_stats( 
       ownname => 'drp' 
    ) ; 
end; 

删除表的统计信息

begin 
    dbms_stats.delete_table_stats( 
       ownname => 'drp', 
       tabname => 'order' 
    ) ; 
end;    

导入

导入表的统计信息

begin 
    dbms_stats.import_table_stats( 
       ownname => 'drp', 
       tabname => 'order', 
       stattab => 'stat_table' 
    ) ;  
end;

导入整个schema的统计信息

begin 
    dbms_stats.import_schema_stats( 
       ownname => 'drp', 
       stattab => 'stat_table' 
    ); 
end;