DELETE选错执行计划的困境

ゝ一纸荒年。 2023-06-07 10:42 24阅读 0赞

# 这篇文章，是我的同事Albert博客中介绍的一个案例，是个和执行计划相关很隐蔽的问题，如果不是有深入研究的心态，这个问题很容易被忽略，而且最近正巧碰见了一个和这个很相近的问题，首先还是来学习下这个问题背后隐藏的知识。    #

原文链接：http://albertdba.com/?p=833

#     #

# 11.2.0.4下，一个SQL选错索引的案例(New AP rejected (non-guess): favored index only range scan) #

近日，在优化SQL测试时再次碰到了这个奇怪的现象，一个简单的DELETE SQL语句：

执行缓慢，不巧，此类型的SQL语句在公司生产系统中出现过问题，导致某B2C网站交易严重超时，所以再次做了一次模拟实验，分析并回顾一下这个SQL优化过程的来龙去脉。

该SQL的索引情况与选择度概览，PID，CNAME，CTYPE存在组合索引，并且为主键，PID有单独存在的索引TRAVELSKYDBA\_IDX01，选择率如下图：

![640?wx\_fmt=png][640_wx_fmt_png]

![640?wx\_fmt=png][640_wx_fmt_png 1]

构造数据完成，执行SQL，关注执行计划：

![640?wx\_fmt=png][640_wx_fmt_png 2]

CBO默认选择了CTYPE，CNAME，PID三列的组合主键索引，也就是最后再去过滤的PID，可是，随之产生了疑问，为什么不选择PID上的索引先行ACCESS呢？PID上的索引选择度理论上是更好的。

我们从10053中可以看到，使用PID列上的索引TRAVELSKYDBA\_IDX01 CBO计算cost时已经给出答案，使用PID上的索引时COST为3，但是CBO为什么没有选择COST值更低的执行路径呢？

![640?wx\_fmt=png][640_wx_fmt_png 3]

因为“New AP rejected (non-guess): favored index only range scan”这个原因，导致整个索引被拒绝了，没有进行成本的比较。什么是“New AP rejected (non-guess): favored index only range scan”？

当语句是DELETE语句(对SELECT语句不适用)的时候，Oracle将不考虑需要回表(出现table access by index rowid)的索引，即favored index only range scan。他认为能在索引内解决的，应该不需要回表。

我们将DELETE改为SELECT后，观察执行计划：

![640?wx\_fmt=png][640_wx_fmt_png 4]

可以看到SELECT时可以选择正确的索引，也就是说当遇到DELETE时，WHERE条件存在选择性好的字段，该字段存在索引，但索引不包含where其他字段时会触发此问题。

但对于该SQL，使用该索引效率较差(通过组合索引，定位数据，再通过过滤，才可以得到)。如遇到此问题，可以使用SQL\_PROFILE稳定执行计划的方式强制CBO使用TRAVELSKYDBA\_IDX01的索引。

这种DELETE的执行计划的选择，即忽略了应该使用的执行路径，在Oracle看来，可能有他的考虑，但从CBO成本来说，确实不太正确，但是这个错误，在MOS上没找到特别对应的。

对这个案例，从现象(SQL慢)，到表象(错误的执行计划)，再到根源(10053显示执行计划被拒绝)，这种问题探测的路径，以及钻研问题的精神，同样值得我们学习和借鉴，往往看到了表象，不再深入研究，就很可能丢掉一次探寻真正原因、以及得到锻炼的机会，无论我们学什么、做什么，不放过任何细节，保持研究的心态，才可以让我们接近目标，得到成长。

[640_wx_fmt_png]: /images/20230601/dfaf18c145b7428da6a5d72c0349cc22.png
[640_wx_fmt_png 1]: /images/20230601/96e7f37b007e42b2a6dd5bd9d8cd9e0a.png
[640_wx_fmt_png 2]: /images/20230601/276d5ca02d1947e6bd53c73335be5aa8.png
[640_wx_fmt_png 3]: /images/20230601/46f3ef48fd644f3f81a951204729d229.png
[640_wx_fmt_png 4]: /images/20230601/ee05126effa54e4e861e83c9cc235d49.png