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

最新推荐文章于 2023-11-06 02:51:08 发布

ciqu9915

最新推荐文章于 2023-11-06 02:51:08 发布

阅读量194

点赞数

文章标签：数据库

索引是我们进行优化的一种重要方式。实际工作中，一个简单的索引，可能就会大大提升提高关键业务作业效率，最终提升用户满意度。在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

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

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

| 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

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

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

| 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

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

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

| 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

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

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

| 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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/17203031/viewspace-693944/