ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 索引失效系列——统计量过期引起执行计划错误

索引失效系列——统计量过期引起执行计划错误

原创 Linux操作系统 作者:realkid4 时间:2011-04-27 23:12:01 0 删除 编辑

 

索引是我们进行优化的一种重要方式。实际工作中,一个简单的索引,可能就会大大提升提高关键业务作业效率,最终提升用户满意度。在CBO时代,DBA和开发人员经常为索引为什么不出现在执行计划中而困惑。

 

 

问题提出

 

 

进入CBO时代之后,Oracle优化器变得越来越智能、灵活,生成的SQL执行计划越来越高效。这一切都是依赖两个因素:优化器参数公式和强大的统计量收集能力。其中,统计量是我们最经常打交道的部分。

 

所谓统计量,就是针对当前的数据库对象实际情况,采用一定的抽样比例,应用一定的统计方法,生成数据的描述信息。并且最终将其记录在数据字典中,供下一次生成执行计划是调用使用。

 

 

越精确的统计量,也就意味越优质高效的SQL执行计划。反过来说,过时、错误的统计量,也可能会生成错误的执行计划。下面我们来看这个实验。

 

 

环境准备

 

先准备数据表T和对应索引。

 

SQL> create table t as select * from dba_objects;

Table created

 

SQL> create index idx_t_owner on t(owner);

Index created

 

//收集统计量

SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true);

PL/SQL procedure successfully completed

 

 

 

正常实验

 

此时,我们通过dbms_stats包进行统计量收集。此时,可以说数据表T相关对象的数据字典上统计信息是正确的。

 

 

 

SQL> explain plan for select * from t where wner='SYS';

Explained

 

SQL> select * from table(dbms_xplan.display);

 

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Plan hash value: 1601196873

--------------------------------------------------------------------------

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |      | 43153 |  3877K|   160   (3)| 00:00:02 |

|*  1 |  TABLE ACCESS FULL| T    | 43153 |  3877K|   160   (3)| 00:00:02 |

--------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter("OWNER"='SYS')

 

13 rows selected

 

SQL> rollback;

 

Rollback complete

 

SQL> alter system flush shared_pool;

 

System altered

 

SQL> explain plan for select * from t where wner='SCOTT';

 

Explained

 

SQL> select * from table(dbms_xplan.display);

 

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Plan hash value: 1516787156

--------------------------------------------------------------------------------

| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)|

--------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |             |    54 |  4968 |     2   (0)|

|   1 |  TABLE ACCESS BY INDEX ROWID| T           |    54 |  4968 |     2   (0)|

|*  2 |   INDEX RANGE SCAN        | IDX_T_OWNER |    54 |       |     1   (0)|

--------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - access("OWNER"='SCOTT')

 

14 rows selected

 

 

上述实验证明了两个有代表性的场景。Owner=’SYS’取值较多、偏移度高,这样进行全表扫描的成本较低。而owner=’SCOTT’的选择性好,进行索引路径成本较低。

 

 

统计量失效实验

 

我们使用dbms_stats包进行统计量收集。如果我们此时修改更新数据,改变了数据原有的分布结构和状态,而不去进行手工的统计量更新。那么,生成的执行计划是怎么样呢?

 

//清理shared_pool,避免执行计划重用;

SQL> alter system flush shared_pool;

System altered

 

SQL> update t set wner='SCOTT' where wner='SYS';

43037 rows updated

 

SQL> commit;

 

Commit complete

 

 

此时,owner=’SCOTT’,占到了超过原有’SYS’的比例。如果正确的执行计划,应该是进行全表扫描。事实又是如何呢?

 

 

SQL> explain plan for select * from t where wner='SCOTT';

Explained

 

SQL> select * from table(dbms_xplan.display);

 

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Plan hash value: 1516787156

--------------------------------------------------------------------------------

| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)|

--------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |             |    54 |  4968 |     2   (0)|

|   1 |  TABLE ACCESS BY INDEX ROWID| T           |    54 |  4968 |     2   (0)|

|*  2 |   INDEX RANGE SCAN     | IDX_T_OWNER |    54 |       |     1   (0)|

--------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - access("OWNER"='SCOTT')

14 rows selected

 

 

在没有重新收集统计量,使用原有旧的统计量的情况下,Oracle生成了索引路径执行计划。当数据发生大面积的更新,修改了原有的数据分布,统计量是会与实际数据分布不一致,从而引起执行计划的生成错误。

 

 

那么,修复的方法也很简单,我们重新收集一下统计量就可以了。

 

//手工的收集统计量

SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true);

PL/SQL procedure successfully completed

 

SQL> alter system flush shared_pool;

System altered

 

SQL> explain plan for select * from t where wner='SCOTT';

 

Explained

 

SQL> select * from table(dbms_xplan.display);

 

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Plan hash value: 1601196873

--------------------------------------------------------------------------

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |      | 43405 |  3984K|   160   (3)| 00:00:02 |

|*  1 |  TABLE ACCESS FULL| T    | 43405 |  3984K|   160   (3)| 00:00:02 |

--------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter("OWNER"='SCOTT')

 

13 rows selected

 

 

 

此时,我们已经获取到了本篇的结论:当统计量和实际数据情况不一致,CBO生成的执行计划的确会有问题,生成的路径的确可能不是最优的。

 

 

进一步的思考

 

我们了解了结构,那么如何避免这种情况发生呢?

 

回答这个问题,首先我们需要从Oracle统计量收集的方式谈起。在RBO时代,优化器工作是不需要统计量的。所以虽然比较教条,但也是不需要担心统计量过时的问题。在CBO时代,我们不得不考虑统计量收集的方式。

 

Oracle10g开始,Oracle开始提供自动化统计量收集的作业。当使用默认安装方式之后,每天晚上10点到凌晨,Oracle都会运行作业,来进行统计量收集。此时收集的范围定义为发生数据变量达到一定比例的数据表。Oracle希望使用这个机制来保证尽可能有更新的统计量使用。

 

 

这种作业机制可以实现自动的统计量收集,但是也会给其他方面带来困扰。最大的一方面就是影响到系统夜间作业的执行。我们的应用系统中经常会有一些如大运算、报表、数据汇集等大作业。为了避免和白天的业务窗口时间冲突,我们常常将它们排在夜间完成,没想到会与Oracle统计量自动收集冲突。这也就是为什么有DBA抱怨这些大作业夜间做的速度甚至没有白天快的原因。

 

 

上述问题的解决其实也比较容易,就是禁止夜间进行统计量收集(具体方法请查阅网络资源)。手工组织统计量作业调度计划。这样可以灵活的避免负载冲突的情况。

 

 

下面说说发生统计量与实际数据分布不一致出现的场景,从网络上各同行反映的信息看,大都有如下情况:

 

ü        没有定期收集统计量的方案,使得统计量过旧;

ü        系统升级移植,因为版本不一致使得导入的对应统计量错误;

ü        阴差阳错,在夜间刚刚收集完统计量之后调用大作业,导入海量数据直接改变了原有的数据分布情况。第二天直接生成错误的执行计划;

 

解决的方法相对容易,发现统计量错误的对象,手工收集下就可以了。 

 

 

最后,笔者想谈一下统计量收集的频度问题。一个数据表,是不是每次都需要收集统计量?是不是只要发生变化就要收集统计量?笔者认为这不是绝对的。

 

 

一张数据表,如果数据分布是有规律的、取值是有范围的,那么一份统计量完全可以解决所有的问题,不需要每时每刻进行收集。

 

 

CBO是一次时代的飞跃,但是伴随飞跃必然存在新问题的引入。意识到问题,正视问题和解决问题,是我们正确对待统计量过时问题的根本出发点。

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/17203031/viewspace-693944/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
求道~

注册时间:2010-11-30

  • 博文量
    545
  • 访问量
    7753424