dbms_stats 导入导出表统计信息
在SQL tuning的过程中，不正确的或者过时的统计信息导致使用不正确的执行计划被采用的情况比比皆是。 当然对于这个情形，我们可以通过收集最新的统计信息来达到优化的目的。而且Oracle之前的统计信息会自动保留。除此之外，我们也可以通过备份的方式来实现导入导出统计信息。本文即使描述的即是该方式，同时并对比了不同统计信息的执行计划，最后给出了一个批量导出统计信息的代码。
1、创建演示环境
[sql]
scott@USBO> select * from v$version where rownum<2;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
--创建演示表，并插入所有sys用户的表记录
scott@USBO> create table t1 nologging tablespace tbs1 as select * from dba_objects where owner='SYS' and object_type='TABLE';
--添加所有非sys的记录
scott@USBO> insert into t1 select * from dba_objects where owner <>'SYS';
43172 rows created.
scott@USBO> commit;
scott@USBO> create index i_t1_owner on t1(owner); --->添加索引
--收集统计信息
scott@USBO> exec dbms_stats.gather_table_stats('SCOTT','T1',cascade=>true);
PL/SQL procedure successfully completed.
--此时表上sys用户的表位1001个
scott@USBO> select owner,count(*) from t1 where owner='SYS' group by owner;
OWNER COUNT(*)
------------------------------ ----------
SYS 1001
--下面是其执行计划
scott@USBO> set autot trace exp;
scott@USBO> select owner,count(*) from t1 where owner='SYS' group by owner;
Execution Plan
----------------------------------------------------------
Plan hash value: 832695366
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 8 | 4 (0)| 00:00:01 |
| 1 | SORT GROUP BY NOSORT| | 1 | 8 | 4 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | I_T1_OWNER | 1425 | 11400 | 4 (0)| 00:00:01 |
-----------------------------------------------------------------------------------
scott@USBO>set autot off;
2、导出统计信息
[sql]
--首先创建用于存放统计信息的舞台表
scott@USBO> exec dbms_stats.create_stat_table (ownname => 'SCOTT', stattab => 'ST_T1', tblspace => 'TBS1');
PL/SQL procedure successfully completed.
--下面使用export_table_stats过程导出统计信息，此时statid为A
scott@USBO> exec dbms_stats.export_table_stats(ownname =>'SCOTT',tabname=>'T1',stattab=>'ST_T1',statid => 'A');
PL/SQL procedure successfully completed.
--插入新的记录，此时为SYS非表类型的所有对象，有30043条
scott@USBO> insert into t1 nologging select * from dba_objects where owner='SYS' and object_type<>'TABLE';
30043 rows created.
scott@USBO> commit;
--收集统计信息
scott@USBO> exec dbms_stats.gather_table_stats('SCOTT','T1',cascade=>true);
--再次导出统计信息，注意，此时的statid为B
scott@USBO> exec dbms_stats.export_table_stats(ownname =>'SCOTT',tabname=>'T1',stattab=>'ST_T1',statid => 'B');
PL/SQL procedure successfully completed.
--下面我们分析原SQL的执行计划
scott@USBO> set autot trace exp;
scott@USBO> select owner,count(*) from t1 where owner='SYS' group by owner;
Execution Plan
----------------------------------------------------------
Plan hash value: 453826725
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 | 58 (0)| 00:00:01 |
| 1 | SORT GROUP BY NOSORT | | 1 | 6 | 58 (0)| 00:00:01 |
|* 2 | INDEX FAST FULL SCAN| I_T1_OWNER | 31349 | 183K| 58 (0)| 00:00:01 |
------------------------------------------------------------------------------------
--上面的执行计划中使用了最新的统计信息，而且预估的行数31349接近于表上的行数
3、导入过旧的统计信息并对比执行计划
[sql]
--下面使用import_table_stats导入之前过旧的统计信息
scott@USBO> exec dbms_stats.import_table_stats(ownname => 'SCOTT', tabname => 'T1', stattab => 'ST_T1', -
> statid => 'A', no_invalidate => true);
PL/SQL procedure successfully completed.
--再次查看原SQL的执行计划
scott@USBO> select owner,count(*) from t1 where owner='SYS' group by owner;
Execution Plan
----------------------------------------------------------
Plan hash value: 832695366
-----------------------------------------------------------------------------------
| Id | Operation | Name &nbs
