dbms_stats 导入导出 表级别 统计信息

           dbms_stats 导入导出 schema 级别统计信息

 

1、创建演示环境

[sql]  view plain copy print ?

1. scott@USBO> select * from v$version where rownum<2;     
2.      
3. BANNER     
4. --------------------------------------------------------------------------------      
5. Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production     
6.   
7. --创建演示表，并插入所有sys用户的表记录  
8. scott@USBO> create table t1 nologging tablespace tbs1 as select * from dba_objects where owner='SYS' and object_type='TABLE';  
9.   
10. --添加所有非sys的记录  
11. scott@USBO> insert into t1 select * from dba_objects where owner <>'SYS';  
12.   
13. 43172 rows created.  
14.   
15. scott@USBO> commit;  
16. scott@USBO> create index i_t1_owner on t1(owner);      --->添加索引              
17.   
18. --收集统计信息  
19. scott@USBO> exec dbms_stats.gather_table_stats('SCOTT','T1',cascade=>true);  
20.   
21. PL/SQL procedure successfully completed.  
22.   
23. --此时表上sys用户的表位1001个  
24. scott@USBO> select owner,count(*) from t1 where owner='SYS' group by owner;  
25.   
26. OWNER                            COUNT(*)  
27. ------------------------------ ----------  
28. SYS                                  1001  
29.   
30. --下面是其执行计划  
31. scott@USBO> set autot trace exp;  
32. scott@USBO> select owner,count(*) from t1 where owner='SYS' group by owner;  
33.   
34. Execution Plan  
35. ----------------------------------------------------------  
36. Plan hash value: 832695366  
37.   
38. -----------------------------------------------------------------------------------  
39. | Id  | Operation            | Name       | Rows  | Bytes | Cost (%CPU)| Time     |  
40. -----------------------------------------------------------------------------------  
41. |   0 | SELECT STATEMENT     |            |     1 |     8 |     4   (0)| 00:00:01 |  
42. |   1 |  SORT GROUP BY NOSORT|            |     1 |     8 |     4   (0)| 00:00:01 |  
43. |*  2 |   INDEX RANGE SCAN   | I_T1_OWNER |  1425 | 11400 |     4   (0)| 00:00:01 |  
44. -----------------------------------------------------------------------------------  
45.   
46. scott@USBO>set autot off;  

2、导出统计信息

[sql]  view plain copy print ?

1. --首先创建用于存放统计信息的舞台表  
2. scott@USBO> exec dbms_stats.create_stat_table (ownname => 'SCOTT', stattab => 'ST_T1', tblspace => 'TBS1');  
3.   
4. PL/SQL procedure successfully completed.  
5.   
6. --下面使用export_table_stats过程导出统计信息，此时statid为A  
7. scott@USBO> exec dbms_stats.export_table_stats(ownname =>'SCOTT',tabname=>'T1',stattab=>'ST_T1',statid => 'A');  
8.   
9. PL/SQL procedure successfully completed.  
10.   
11. --插入新的记录，此时为SYS非表类型的所有对象，有30043条  
12. scott@USBO> insert into t1 nologging select * from dba_objects where owner='SYS' and object_type<>'TABLE';  
13.   
14. 30043 rows created.  
15.   
16. scott@USBO> commit;  
17.   
18. --收集统计信息  
19. scott@USBO> exec dbms_stats.gather_table_stats('SCOTT','T1',cascade=>true);  
20.   
21. --再次导出统计信息，注意，此时的statid为B  
22. scott@USBO> exec dbms_stats.export_table_stats(ownname =>'SCOTT',tabname=>'T1',stattab=>'ST_T1',statid => 'B');  
23.   
24. PL/SQL procedure successfully completed.  
25.   
26. --下面我们分析原SQL的执行计划  
27. scott@USBO> set autot trace exp;  
28. scott@USBO> select owner,count(*) from t1 where owner='SYS' group by owner;  
29.   
30. Execution Plan  
31. ----------------------------------------------------------  
32. Plan hash value: 453826725  
33.   
34. ------------------------------------------------------------------------------------  
35. | Id  | Operation             | Name       | Rows  | Bytes | Cost (%CPU)| Time     |  
36. ------------------------------------------------------------------------------------  
37. |   0 | SELECT STATEMENT      |            |     1 |     6 |    58   (0)| 00:00:01 |  
38. |   1 |  SORT GROUP BY NOSORT |            |     1 |     6 |    58   (0)| 00:00:01 |  
39. |*  2 |   INDEX FAST FULL SCAN| I_T1_OWNER | 31349 |   183K|    58   (0)| 00:00:01 |  
40. ------------------------------------------------------------------------------------  
41.   
42. --上面的执行计划中使用了最新的统计信息，而且预估的行数31349接近于表上的行数  

3、导入过旧的统计信息并对比执行计划

[sql]  view plain copy print ?

1. --下面使用import_table_stats导入之前过旧的统计信息  
2. scott@USBO> exec dbms_stats.import_table_stats(ownname => 'SCOTT', tabname => 'T1', stattab => 'ST_T1', -  
3. > statid => 'A', no_invalidate => true);  
4.   
5. PL/SQL procedure successfully completed.  
6.   
7. --再次查看原SQL的执行计划  
8. scott@USBO> select owner,count(*) from t1 where owner='SYS' group by owner;  
9.   
10. Execution Plan  
11. ----------------------------------------------------------  
12. Plan hash value: 832695366  
13.   
14. -----------------------------------------------------------------------------------  
15. | Id  | Operation            | Name       | Rows  | Bytes | Cost (%CPU)| Time     |  
16. -----------------------------------------------------------------------------------  
17. |   0 | SELECT STATEMENT     |            |     1 |     8 |     4   (0)| 00:00:01 |  
18. |   1 |  SORT GROUP BY NOSORT|            |     1 |     8 |     4   (0)| 00:00:01 |  
19. |*  2 |   INDEX RANGE SCAN   | I_T1_OWNER |  1425 | 11400 |     4   (0)| 00:00:01 |  
20. -----------------------------------------------------------------------------------  
21. --Author : Leshami   Blog : http://blog.csdn.net/leshami  
22. --从上面的执行计划中来看，尽管执行计划与先前的两个执行计划相同，但预估的行数是之前的行数，只有1425条记录  
23. --也即是由于过时的统计信息造成的  

4、批量导出表统计信息

[sql]  view plain copy print ?

1. --下面的匿名pl/sql块可以批量导出统计信息，可以用于SQL语句涉及到多表的情形，可以把相关的表统计信息全部导出  
2. --需要注意的是表的名字不要超过28，因为我这里定义的统计信息备份表以"S_"开头占据了2个字符  
3. --可以根据自己的情形修改其代码，如添加表空间参数等。  
4. --对于披量导入表统计信息的脚本，大家可以参照下面的脚本修改，使用过程import_table_stats  
5. DECLARE  
6.    v_table_name   VARCHAR2 (30);  
7.    v_stat_name    VARCHAR2 (35);  
8.    v_sql_stat     VARCHAR2 (200);  
9.    v_schema       VARCHAR2 (30) := 'SCOTT';  
10.   
11.    --Define your table you want to export stat  
12.    CURSOR cur_tab  
13.    IS  
14.       SELECT table_name  
15.         FROM dba_tables  
16.        WHERE table_name IN ('EMP', 'DEPT', 'BONUS');  
17. BEGIN  
18.    FOR cur_rec IN cur_tab  
19.    LOOP  
20.       v_stat_name := 'S_' || cur_rec.table_name;  
21.   
22.       v_sql_stat := 'BEGIN DBMS_STATS.create_stat_table (''' || v_schema || ''' , ''' || v_stat_name || '''); END;';  
23.   
24.       --            DBMS_OUTPUT.put_line (v_sql_stat);  
25.       EXECUTE IMMEDIATE v_sql_stat;  
26.   
27.       v_sql_stat := 'BEGIN DBMS_STATS.export_table_stats(''' || v_schema || ''',tabname=>''' || cur_rec.table_name || ''',stattab=>''' || v_stat_name || '''); END;';  
28.   
29.       --        DBMS_OUTPUT.put_line (v_sql_stat);  
30.       EXECUTE IMMEDIATE v_sql_stat;  
31.    END LOOP;  
32. END;