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;

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏乐沙弥的世界

Oracle 分区表

随着表的不断增大,对于新纪录的增加、查找、删除等(DML)的维护也更加困难。对于数据库中的超大型表,可通过把它的数据分成若干个小表,从而简化数据库的管理活动。...

19220
来自专栏乐沙弥的世界

Buffer cache 的调整与优化(二)

Buffer cache 实际上细分为多个不同的Buffer cache,如keep pool,recycle pool,default pool,下面描述不...

8120
来自专栏文渊之博

SQL Server 2014聚集列存储索引

 转发请注明引用和原文博客(https://cloud.tencent.com/developer/user/1217611/activities) 简介   ...

24390
来自专栏维C果糖

史上最简单的 MySQL 教程(二十三)「数据的高级操作 之 查询(上)」

字段别名,即当数据进行查询的时候,有时候字段的名字并不一定满足需求(特别地,在多表查询的时候,很可能会有同名字段),这时就需要对字段进行重命名、取别名。

36660
来自专栏.NET技术

经典SQL语句大全之提升

1、说明:复制表(只复制结构,源表名:a 新表名:b) (Access可用) 法一:select * into b from a where 1<>1(仅用于S...

24130
来自专栏杨建荣的学习笔记

sqlldr批量导入导出数据测试(86天)

sqlldr是在处理大数据量的操作中建议采用的方式,它有许多性能想关的开关,能最大程度的减少redo,undo的生成,控制数据的处理方式(insert,appe...

47590
来自专栏乐沙弥的世界

Oracle AWR 阙值影响历史执行计划

      最近有网友提到为什么在dba_hist_sql_plan中无法查看到sql语句的历史执行计划,对于这个问题是由于缺省情况下,Oracle 设定的阙值...

8420
来自专栏乐沙弥的世界

Oracle RAC 环境下的 v$log v$logfile

      通常情况下,在Oracle RAC 环境中,v$视图可查询到你所连接实例的相关信息,而gv$视图则包含所有实例的信息。然而在RAC环境中,当我们查询...

23920
来自专栏数据库

按图索骥:SQL中数据倾斜问题的处理思路与方法

数据倾斜即表中某个字段的值分布不均匀,比如有100万条记录,其中字段A中有90万都是相同的值。这种情况下,字段A作为过滤条件时,可能会引起一些性能问题。 本文通...

21990
来自专栏文渊之博

SQL Server 2014聚集列存储索引

  之前已经写过两篇介绍列存储索引的文章,但是只有非聚集列存储索引,今天再来简单介绍一下聚集的列存储索引,也就是可更新列存储索引。在SQL Server 201...

13040

扫码关注云+社区

领取腾讯云代金券