对于Oracle CBO 来说,SQL语句的执行计划完全依靠于准确的统计信息
除了自动的统计信息分析外,我们可以手动进行分析
下面主要是举例来说,具体命令的参数可参考官方文档
https://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_stats.htm#i1036461
http://www.cnblogs.com/wbzhao/archive/2012/04/05/2433616.html
analyze table dcwip.wip_activity compute statistics;
这里首先创建用于保存现有统计信息的表
exec dbms_stats.create_stat_table(ownname => 'fwassy',stattab => 'qagatedata_static');
exec dbms_stats.create_stat_table(ownname => 'mesinterface',stattab => 'mesinterface_plan');
exec dbms_stats.create_stat_table(ownname => 'fwassy',stattab => 'fwassy_statics');
这里导出现有执行计划
exec dbms_stats.export_table_stats(ownname=>'fwassy',tabname=>'RPt_scrap',stattab=>'fwassy_plan',statid => 'RPt_scrap');
exec dbms_stats.export_table_stats(ownname=>'fwassy' ,tabname=>'FWCATNS_PACKAGE',stattab=> 'fwassy_statics',statid => 'id_FWCATNS_PACKAGE' );
这里也可以导出整个schema的统计信息
exec dbms_stats.export_schema_stats(ownname => 'fwft',stattab => 'fwft_plan');
这里开始收集统计信息
exec dbms_stats.gather_table_stats(ownname => 'fwass',tabname => 'RPt_scrap' ,estimate_percent=>dbms_stats.auto_sample_size,degree =>3, cascade => true,statown => 'fwft' , stattab => 'fwwip_static')
exec dbms_stats.gather_table_stats(ownname => 'fwassy',tabname => 'FWCATNS_ASSY_ROUTING_EDITION',estimate_percent=>100, method_opt => 'for all columns size AUTO', degree =>3, cascade => true)
exec dbms_stats.gather_table_stats(ownname => 'acs_assy',tabname => 'DOWN_RECORD',estimate_percent=>dbms_stats.auto_sample_size, method_opt => 'for all columns size AUTO', degree =>3, cascade => true)
exec dbms_stats.gather_schema_stats(ownname => 'test',options => 'GATHER AUTO',estimate_percent => dbms_stats.auto_sample_size,method_opt => 'for all indexed columns', degree => 6 );
如果发现执行计划走错,我们需要恢复原来的统计信息
如果原来没有统计信息可以选择删除它
exec dbms_stats.delete_table_stats(ownname => 'TEST',tabname => 'SM_USER') ;
exec dbms_stats.import_index_stats(ownname => 'TEST',indname => 'PK_USER_INDEX',stattab => 'stat_table')
exec dbms_stats.import_table_stats(ownname => 'fwassy', tabname => 'fwcatns_package', stattab => 'fwassy_statics' , statid => 'id_fwcatns_package', no_invalidate => true );
SELECT * FROM DBA_TABLES WHERE TABLE_NAME = 'FWCATNS_PACKAGE';
SELECT * FROM DBA_INDEXES WHERE INDEX_NAME = 'IDX_FWCATNS_PACKAGE';
select * from fwassy.fwassy_statics