dbms_stats 导入导出表统计信息

在SQL tuning的过程中，不正确的或者过时的统计信息导致使用不正确的执行计划被采用的情况比比皆是。 当然对于这个情形，我们可以通过收集最新的统计信息来达到优化的目的。而且Oracle之前的统计信息会自动保留。除此之外，我们也可以通过备份的方式来实现导入导出统计信息。本文即使描述的即是该方式，同时并对比了不同统计信息的执行计划，最后给出了一个批量导出统计信息的代码。
有关导入导出统计信息的具体步骤及使用情形可参考：
dbms_stats 导入导出 schema 级别统计信息

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 |
-----------------------------------------------------------------------------------
--Author : Leshami   Blog : http://blog.csdn.net/leshami
--从上面的执行计划中来看，尽管执行计划与先前的两个执行计划相同，但预估的行数是之前的行数，只有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;

更多参考

DMLError Logging 特性

PL/SQL --> 游标

PL/SQL --> 隐式游标(SQL%FOUND)

批量SQL之 FORALL 语句

批量SQL之 BULK COLLECT 子句

PL/SQL 集合的初始化与赋值

PL/SQL 联合数组与嵌套表
PL/SQL 变长数组
PL/SQL --> PL/SQL记录

SQL tuning 步骤

高效SQL语句必杀技

父游标、子游标及共享游标

绑定变量及其优缺点

dbms_xplan之display_cursor函数的使用

dbms_xplan之display函数的使用

执行计划中各字段各模块描述

使用 EXPLAIN PLAN 获取SQL语句执行计划