[TOC] 对于Oracle CBO 来说,SQL语句的执行计划完全依靠于准确的统计信息 除了自动的统计信息分析外,我们可以手动进行分析 下面主要是举例来说,具体命令的参数可参考官方文档 [https://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_stats.htm#i1036461](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](http://www.cnblogs.com/wbzhao/archive/2012/04/05/2433616.html) ##1. 使用analyze分析 ```python analyze table dcwip.wip_activity compute statistics; ``` ## 2. 使用Oracle包分析 ###2.1 备份现有统计信息: 这里首先创建用于保存现有统计信息的表 ```python 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'); ``` ###2.2 导出表的统计信息 这里导出现有执行计划 ```python 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' ); ``` ###2.3 导出schema的统计信息 这里也可以导出整个schema的统计信息 ```python exec dbms_stats.export_schema_stats(ownname => 'fwft',stattab => 'fwft_plan'); ``` ###2.4 收集表和索引的统计信息 这里开始收集统计信息 ```python 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) ``` ###2.5 分析Schema: ```python 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 ); ``` ##3. 恢复统计信息 如果发现执行计划走错,我们需要恢复原来的统计信息 ###3.1 删除表的统计信息 如果原来没有统计信息可以选择删除它 ```python exec dbms_stats.delete_table_stats(ownname => 'TEST',tabname => 'SM_USER') ; ``` ###3.2 导入统计信息 ```python 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 ); ``` ##4. 查看表的统计信息 ###4.1 查看表或索引是否已分析 ```python SELECT * FROM DBA_TABLES WHERE TABLE_NAME = 'FWCATNS_PACKAGE'; SELECT * FROM DBA_INDEXES WHERE INDEX_NAME = 'IDX_FWCATNS_PACKAGE'; ``` ###4.2 查看保存统计信息的表 ``` select * from fwassy.fwassy_statics ```