oracle sql 执行计划分析_一次奇怪的SQL执行计划走偏问题分析

1. 问题描述

客户的业务人员反馈一个存储过程执行越来越慢，希望分析一下是否有优化空间。

2. 分析过程

针对存储过程执行慢的问题，首先要从ASH中统计出各SQL的采样次数，采样越多证明执行耗时越久，然后再针对SQL来进行分析。我这里抓到是如下这条SQL耗时最久。

这是一条简单的统计表的总记录数的SQL，我们首先看下该SQL的执行计划，可以看到走的是INDEX FULL SCAN。

INDEX FULL SCAN是单块读，那么很容易想到，这个SQL在没有过滤条件、没有排序的情况下，为什么不走INDEX FAST FULL SCAN呢？INDEX FAST FULL SCAN是多块读，理论上是肯定会比INDEX FULL SCAN快很多，这里选择INDEX FULL SCAN比选择TABLE FULL SCAN更令人费解。

接下来我尝试加HINT强制SQL走INDEX FFS，发现能成功，通过测试SQL执行效率也快了很多。

首先解决问题，既然是存储过程，那么直接修改存储过程，加上HINT后问题可以快速解决。

我们再回头看看这两个执行计划的Cost值，INDEX FFS的Cost为98737，比INDEX FULL SCAN的Cost值35653还高，难怪优化器没有选择INDEX FFS了。

那么优化为什么会出现这么明显的“错误”评估？这是当前值得探究的问题。这时候一般采用10053跟踪的方式来分析。

我们直接看如下的关键部分，优化器在3种执行计划之间，评估INDEX FULL SCAN的Cost最低，且Cost值与我们前面直接看执行计划时显示的一致。

最奇怪的地方是，INDEX FULL SCAN的Cost_io为1172000，INDEX FFS的为87752，相差10几倍的情况下，怎么会计算出INDEX FULL SCAN的Cost更低？

10053 trace里有计算Cost的所有统计数据，接下来，我想尝试自己套用公式来计算，看看差异到底在哪里。

这里我把3个可能的执行路径的Cost都计算出来，计算的时候，我们还会用到10053 trace中的系统统计信息：

(1)FULL TABLE SCAN

计算公式：

FTS Cost = I/O Cost + CPU Cost

I/O Cost = 1 + CEIL(#MRds * (mreadtim / sreadtim))

#MRds = #Blks / MBRC

CPU Cost = ROUND(#CPUCycles / cpuspeed / 1000 / sreadtim)

计算结果：

I/O Cost = 1 + CEIL(5281685/34 * (4.998 / 1.963)) = 395522

CPU Cost = ROUND(70090737826 / 1521 / 1000 / 1.963) = 23475

FTS Cost = 395522 + 23475.3 = 418997

(2)INDEX FFS (计算公式与FULL TABLE SCAN一致)

结算公式：

INDEX FFS是多块读，可以认为是一种特殊的FULL TABLE SCAN，只是扫描的对象是索引段，二者的计算公式一样

计算结果：

I/O Cost = 1 + CEIL(1171800/34 * (4.998 / 1.963)) = 87752

CPU Cost = ROUND(32796931392 / 1521 / 1000 / 1.963) = 16445

INDEX FFS = 87752 + 16445 = 98737

(3)INDEX FULL SCAN

IFS Cost = I/O Cost + CPU Cost

I/O Cost = Index Access I/O Cost + Table Access I/O Cost

Index Access I/O Cost = LVLS + CEIL(#LB * ix_sel)

Table Access I/O Cost = CEIL(CLUF * ix_sel_with_filters)

CPU Cost = ROUND(#CPUCycles / cpuspeed / 1000 / sreadtim)

计算如下：

I/O Cost = Index Access I/O Cost + Table Access I/O Cost

Index Access I/O Cost = 2 + CEIL(1171800 * 1) = 1171802

Table Access I/O Cost = 0 --未回表计0

CPU Cost = ROUND(49099707680 / 1521 / 1000 / 1.963) = 16445

IFS Cost = 1171802 + 16445 = 1188247

可以看到，除了INDEX FULL SCAN外，另外两种访问路径计算出的结果与优化器评估的Cost一致。那么现在的问题就集中在，为什么计算出来的Cost为1188247，而优化器最终评估的Cost却为35653.34？

两个值是否有什么关联呢？通过计算找规律，在忽略细微误差的情况下，可以认为35653.34=1188247*3%，也就是Cost取值为真实计算结果的3%。

这时候，我联想到optimizer_index_cost_adj参数。该参数表示索引扫描和全表扫描成本的比较，默认值为100，表示索引扫描成本等价转换与全表扫描成本。如果设置为50，那么在走索引时，计算出的Cost会按optimizer_index_cost_adj参数设置的比例进行换算，这会让优化器更倾向于走索引，而该库optimizer_index_cost_adj也的确设置为3：

到这里，针对该SQL的问题，基本可以确认是optimizer_index_cost_adj参数调整过小，优化器在计算成本的时候，按照optimizer_index_cost_adj设置的比例进行换算后，导致INDEX FULL SCAN的成本更低导致。同时我们还能得出结论，optimizer_index_cost_adj参数对INDEX FAST FULL SCAN无效。

通过下面的实验，我们能够更加直观的看到optimizer_index_cost_adj参数对Cost的影响。

3. 总结

(1)OPTIMIZER_INDEX_COST_ADJ参数表示一个百分比，取值范围在1-10000之间，默认值为100，表示索引扫描成本等价转换与全表扫描成本。

(2)该参数对CBO的成本计算具有重大影响，对于一个运行稳定的生产系统，一般不能轻易修改该参数的值。

(3)对于大多数OLTP系统，OPTIMIZER_INDEX_COST_ADJ可以设置在10到50之间。

(4)该参数对INDEX FAST FULL SCAN无效。