dbms_stats 导入导出表统计信息

郑全

在SQL tuning的过程中，不正确的或者过时的统计信息导致使用不正确的执行计划被采用的情况比比皆是。 当然对于这个情形，我们可以通过收集最新的统计信息来达到优化的目的。而且Oracle之前的统计信息会自动保留。除此之外，我们也可以通过备份的方式来实现导入导出统计信息。本文即使描述的即是该方式，同时并对比了不同统计信息的执行计划，最后给出了一个批量导出统计信息的代码。
1、创建演示环境
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、导出统计信息

--首先创建用于存放统计信息的舞台表  

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、导入过旧的统计信息并对比执行计划

--下面使用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       | 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
|  -----------------------------------------------------------------------------------  

--从上面的执行计划中来看，尽管执行计划与先前的两个执行计划相同，但预估的行数是之前的行数，只有1425条记录  
--也即是由于过时的统计信息造成的  

4、批量导出表统计信息

--下面的匿名pl/sql块可以批量导出统计信息，可以用于SQL语句涉及到多表的情形，可以把相关的表统计信息全部导出
--需要注意的是表的名字不要超过28，因为我这里定义的统计信息备份表以"S_"开头占据了2个字符
--可以根据自己的情形修改其代码，如添加表空间参数等。  
--对于披量导入表统计信息的脚本，大家可以参照下面的脚本修改，使用过程
import_table_stats  

DECLARE     
v_table_name   VARCHAR2 (30);     
v_stat_name    VARCHAR2 (35);     
v_sql_stat     VARCHAR2 (200);     
v_schema       VARCHAR2 (30) := 'SCOTT';      
--Define your table you want to export stat     
CURSOR cur_tab     IS        SELECT table_name         
   FROM dba_tables         WHERE table_name IN ('EMP', 'DEPT', 'BONUS');  

BEGIN     
FOR cur_rec IN cur_tab     LOOP      
   v_stat_name := 'S_' || cur_rec.table_name;         
   v_sql_stat := 'BEGIN DBMS_STATS.create_stat_table (''' || v_schema || ''' , ''' || v_stat_name || ''');
                       END;';         
   --            DBMS_OUTPUT.put_line (v_sql_stat);        
   EXECUTE IMMEDIATE v_sql_stat;         
   v_sql_stat := 'BEGIN DBMS_STATS.export_table_stats(''' || v_schema || ''',tabname=>''' || cur_rec.table_name || ''',stattab=>''' || v_stat_name || '''); END;';         
   --        DBMS_OUTPUT.put_line (v_sql_stat);        
   EXECUTE IMMEDIATE v_sql_stat;     
END LOOP;  
END;