极限优化：从75到2000,由技能到性能提升岂止80倍

崔华，网名 dbsnake

Oracle ACE Director，ACOUG 核心专家

编辑手记：感谢崔华授权我们独家转载其精品文章，也欢迎大家向“Oracle”社区投稿。

崔华手记：这篇文章的标题颇有些标题党的味道，但这不是我的初衷。其实我想表达的是，基于RDBMS的应用系统的性能问题绝大多数都是和SQL直接相关，而基于Oracle数据库的SQL优化能否奏效、效果的好与坏归根结底比拼的还是对CBO和执行计划的理解程度，这也正是我在《基于Oracle的SQL优化》一书中提出来的SQL优化方法论的第一点——Oracle里SQL优化的本质是基于对CBO和执行计划的深刻理解。

刚才我们只调整了SQL ID为“73qyztauub2a1”的目标SQL，现在我们再来调整剩下的那个SQL ID为“74hnt3vjjm85a”的目标SQL。

SQL ID为“74hnt3vjjm85a”的SQL文本为如下所示：

SELECT s.LOCATOR_ID, s.segment_ID, n.en_name

FROM ANA_SEG_CLASS S, ana_segment a, ana_name n

where A.SEGMENT_ID = S.SEGMENT_ID

and s.locator_id = n.locator_id

and n.cn_name = :1

and ((a.orgn_city = :2 and a.start_time > :3 and a.start_time < :4) or

(a.dstn_city = :5 and a.end_time > :6 and a.end_time < :7))

and n.person_id_count > 0

从对应采样时间段的AWR SQL Report中我们可以看出，上述SQL对应有三个Child Cursor：

这三个Child Cursor，真正被Oracle在采样时间段使用的有两个执行计划（分别是Plan Hash Value为2064935335和105883452所对应的执行计划），这两个执行计划所对应的资源消耗情况和执行计划的具体内容为如下所示：

Plan 1(PHV: 2064935335)

Execution Plan

Plan 2(PHV: 105883452)

Execution Plan

显然，上述SQL的这两个执行计划均不是最优解，它们分别对表ANA_SEGMENT和ANA_NAME做了不必要的全表扫描。

注意到上述SQL和之前我们处理的第一条SQL在SQL文本上的区别仅仅是SQL文本中的“and n.en_name = :1”和“and n.cn_name = :1”的不同，其它部分都是一样的。

从表ANA_NAME的统计信息显示结果中我们可以看到，列EN_NAME和CN_NAME的可选择性都非常好，在列EN_NAME上存在一个名为IDX_ANA_NAME的单键值B树索引，但在列CN_NAME上并不存在任何索引：

所以这里我们只需要在列CN_NAME上创建一个单键值的B树索引，就可以让该SQL走出和之前第一个SQL那样一模一样的执行计划：

SQL> create index idx_ana_cname on ana_name(cn_name);

Index created

SQL> explain plan for SELECT s.LOCATOR_ID, s.segment_ID, n.en_name FROM ANA_SEG_CLASS S, ana_segment a , ana_name n where A.SEGMENT_ID = S.SEGMENT_ID and s.locator_id = n.locator_id and n.cn_name = :1 and ( ( a.orgn_city = :2 and a.start_time > :3 and a.start_time < :4 ) or ( a.dstn_city=:5 and a.end_time> :6 and a.end_time < :7 ) ) and n.person_id_count > 0;

Explained

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

从上述显示内容中我们可以看到，我们的目的已经实现了。

是不是这就完事了？——事情没有这么简单。

根据同事的反馈，当我们在列CN_NAME上创建一个单键值的B树索引IDX_ANA_CNAME后，现在每分钟能跑的Job的数量反而从之前的2000下降到了现在的800。

为什么会这样？

此时的AWR SQL Report显示，Oracle此时同时启用了两个执行计划，这两个执行计划所对应的资源消耗情况和执行计划的具体内容为如下所示：

Plan 1(PHV: 2268138873)

Execution Plan

Plan 2(PHV: 2110666080)

Execution Plan

这里为什么每分钟能跑的Job的数量反而下降的原因是显而易见的——因为对于上述SQL而言，Oracle此时一共执行了1208次，其中只有25次走的是我们想让Oracle走的理想执行计划；剩下的1183次Oracle选择的是走哈希连接，分别对表ANA_SEGMENT、ANA_SEG_CLASS和ANA_NAME做了全表扫描，这样单次的平均执行时间一下子递增为5.012秒，所以相当于是执行时间更长、更慢了，于是并发执行的Job数量就降了下来。

现在的关键问题是——上述SQL明明使用了绑定变量且绑定变量窥探在默认情况下已经被开启，那为什么这里Oracle还会同时启用两个执行计划？

我们再来看一下表ANA_NAME的统计信息：

注意到此时列CN_NAME是有直方图统计信息的（显然是Height Balanced类型的直方图），再结合这个数据库的版本（Oracle 11.2.0.1），我们之前的疑惑就有了答案。

在Oracle 11g之前，绑定变量窥探的副作用就在于使用了绑定变量的目标SQL就只会沿用之前硬解析时所产生的解析树和执行计划，即使这种沿用完全不适合于当前的情形。

在Oracle 10g及其后续的版本中，Oracle会自动收集直方图统计信息，这意味着和之前的版本相比，在Oracle 10g及其后续的版本中Oracle有更大的概率会知道目标列实际数据的分布情况，这意味着绑定变量窥探的副作用将会更加明显。

例如：如果表T1的列COL1没有直方图统计信息，

1. 同时在列COL1上又存在一个单键值的B树索引IDX_T1，
2. 则不管列COL1的实际数据分布情况是否均衡，
3. Oracle在执行目标SQL“select count(*) from t1 where col1 = ”时都会走固定的执行计划（很可能是走对索引IDX_T1的索引范围扫描），这个时候绑定变量窥探实际上是没有副作用的；

但假如列COL1上有了直方图统计信息，

1. 同时列COL1的实际数据分布又是极度不均衡的，
2. 那么这个时候当Oracle执行绑定变量窥探操作时绑定变量x所对应的输入值是否具有代表性就至关重要了（这里“代表性”的含义是指该输入值所对应的执行计划和该SQL在大多数情况下的执行计划相同）；
3. 因为这会直接决定上述SQL在硬解析时所选择的执行计划，进而会决定后续以软解析/软软解析重复执行时所沿用的执行计划。

为什么会发生这样的变化？

是因为一旦列COL1上有了直方图统计信息，Oracle就知道了列COL1的实际数据分布情况，这样在列COL1的实际数据分布又是极度不均衡的前提条件下，Oracle就能随着上述SQL中对列COL1的输入值的不同而调整上述SQL的执行计划。

这种现象的本质是因为当列COL1没有直方图统计信息时，CBO在计算上述谓词条件的可选择率时会根据列COL1的distinct值的数量来计算，这意味着这种情况下该谓词条件的可选择率是固定的；而一旦列COL1上有了直方图统计信息，CBO就会根据列COL1的直方图统计信息来计算上述谓词条件的可选择率，这就决定了该谓词条件的可选择率是不固定的，并且可能会随着对列COL1的输入值的变化而变化，而这正好给了绑定变量窥探发挥其副作用的机会！

为了解决上述绑定变量窥探的副作用，Oracle在11g中引入了自适应游标共享（Adaptive Cursor Sharing）。

自适应游标共享可以让使用了绑定变量的目标SQL在启用了绑定变量窥探的前提条件下不再只沿用之前硬解析时所产生的解析树和执行计划，也就是说自适应游标共享可以在启用了绑定变量窥探的前提条件下让目标SQL在其可能的多个执行计划之间“自适应”的做出选择，而不再像之前那样必须得僵化的沿用该SQL硬解析时所产生的解析树和执行计划。

所以这里为什么Oracle在执行上述含绑定变量且开启了绑定变量窥探的目标SQL时依然会同时启用两个执行计划的原因就是因为自适应游标共享的副作用，而这里自适应游标之所以会被Oracle启用的本质原因又是因为上述SQL的等值查询条件“and n.cn_name = :1”中的列cn_name上有直方图统计信息（关于绑定变量窥探、自适应游标共享和直方图统计信息对可选择率的影响，可以参考《基于Oracle的SQL优化》中详细描述）。

分析清楚了根本原因，解决方法就非常简单了，我们来只需要删除直方图统计信息，不给自适应游标共享发挥副作用的机会就好了。

无论是用Oracle自带的自动统计信息收集作业还是用我们自己写的shell脚本来收集统计信息，对于直方图统计信息的收集而言，我们都建议采用如下的方式：只对已经存在直方图统计信息的列重复收集直方图统计信息，而目标列的初次直方图统计信息的收集则是由了解系统的相关DBA手工来做。具体来说就是这样：

1、 设置METHOD_OPT的值为‘FOR ALL COLUMNS SIZE 1’后先收集一次统计信息，这意味着删除了所有列上的直方图；

2、 在已经删除了所有列上的直方图后，设置METHOD_OPT的值为‘FOR ALL COLUMNS SIZE REPEAT’，这意味着今后将只对已经存在直方图统计信息的列重复收集直方图统计信息；

3、 在系统使用或调优的过程中，目标列的初次直方图统计信息的收集是由了解系统的相关DBA手工来做。

按照上述原则，我们重新来处理一下上述对象所涉及到的直方图统计信息：

SQL> EXEC DBMS_STATS.GATHER_SCHEMA_STATS(OWNNAME => ‘GALT’,ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE,CASCADE => TRUE,METHOD_OPT => ‘FOR ALL COLUMNS SIZE 1’,no_invalidate => false);

PL/SQL procedure successfully completed

SQL> select dbms_stats.get_prefs(pname => ‘METHOD_OPT’) from dual;

DBMS_STATS.GET_PREFS(PNAME=>’M

——————————————————————————–

FOR ALL COLUMNS SIZE AUTO

SQL> exec dbms_stats.set_global_prefs(‘METHOD_OPT’,’FOR ALL COLUMNS SIZE REPEAT’);

PL/SQL procedure successfully completed

SQL> select dbms_stats.get_prefs(pname => ‘METHOD_OPT’) from dual;

DBMS_STATS.GET_PREFS(PNAME=>’M

——————————————————————————–

FOR ALL COLUMNS SIZE REPEAT

至此，我们就完成了第二阶段的调整。

这个做完没多久，就收到了同事的邮件：

崔老师，这次能每分钟完成近6000个任务了，基本上满足了我们业务的基本需求，太感激了。。。

一会您回来传授下经验！

太感谢了！

从随后产生的AWR SQL Report中我们可以看到，当我们删除直方图统计信息后，在随后的执行过程中，对于上述SQL而言，Oracle确实只启用了一个执行计划，且启用的执行计划就是我们想要的：

Plan 1(PHV: 2110666080)

Execution Plan

并且此时整个数据库的Cache Buffers Chains的等待次数已经从最开始的894797次降到了现在的11488次：

Top 5 Timed Foreground Events

至此，上述测试系统的性能问题就被我们成功解决了。

总结：从整个解决过程我们可以看出，虽然最后用的解决方法很简单，但整个解决过程实际上倚赖的还是我们对CBO（包括统计信息、自动收集统计信息作业、直方图统计信息、绑定变量窥探、自适应游标共享等知识点）和执行计划的深刻理解。