夜维执行慢的原因探究

今天做键上线，得空总结一下。

前两天和同事一块看一个夜维程序执行异常慢的问题，是一个比较典型的问题，同时也是一个比较头疼的问题。

背景描述：

1. 当天上线了一个夜维程序，逻辑很简单，就是执行类似delete from table where rownum<=10000 and r_date>='2015-06-01' and r_date<='2015-06-02';，每次删除10000条某天的过期数据，table表数据量大约2000万，每天删除量大约是几十万，r_date字段类型是VARCHAR2，但含义是日期，实际这用的是字符串比较，代替了日期的比较。(11g的库)

2. 现象是程序开始执行后，大约15分钟，都没有结束，上线人员担心有问题，就人工终止了改程序。

3. 临时改了下程序，另外一个k_date字段代替r_date，同样的SQL，很快就执行完了。(从逻辑上，可用k_date代替r_date)

问题排查：

1. 从现象看，15分钟，一个带where条件的delete语句没有执行完，换了一个字段后，很快就执行完成，说明很有可能前者没有使用或者用了错误的索引，导致执行时间超长，究竟是不是，需要证据来论证。

2. 首先通过user_indexes和user_ind_columns视图可以得到索引及对应的字段列信息，这里看到：

(1) 包含r_date有两个索引

索引1：r_date, a, b, c，这里是4个字段组成的一个复合主键，默认包含一个复合索引，r_date是前导列。

索引2：orgid，r_date，复合索引，r_date是第二列。

(2) 包含k_date有一个索引

索引3：k_date的单键值B*Tree索引。

3. 查看执行计划(这里用PLSQL Developer的F5键)

执行快的SQL：delete from table where rownum<=10000 and k_date>='2015-06-01' and k_date<='2015-06-02'，用的是“索引3”的INDEX RANGE SCAN。

执行慢的SQL：delete from table where rownum<=10000 and r_date>='2015-06-01' and r_date<='2015-06-02'，用的是“索引2”的INDEX SKIP SCAN。

单从这看就已经可以定位到原因了，执行快的SQL用了INDEX RANGE SCAN，从索引的选择率(selectivity)看，算是比较高效(几十万/2000万)。执行慢的用了INDEX SKIP SCAN，我们知道索引跳跃扫描(9i以上)的使用是有前提条件的，这种扫描方式是为了让查询条件不是复合索引前导列的情况下，依旧能使用复合索引，但不是任何时候都是高效的，只有当这个复合索引的前导列distinct值较小的前提下，使用这种扫描方式才会相对有效，因为他的检索方式相当于在索引(B*Tree)中遍历所有前导列值的二叉树，再定位非前导列的条件字段，因此如果前导列distinct值较大，那么其实花费的成本也会很大，可能只能认为INDEX SKIP SCAN的性能略好于FULL TABLE SCAN，但依旧可能是非常低效。

4. 这里实际还有个问题，运行DBA的同事从缓存中幸运的找到了慢SQL的SQLID，查看他的执行计划是“索引2”的INDEX FULL SCAN，虽然这样的结果和3的结果有些出入，但都可以一定程度说明索引选择的不正确是造成SQL慢的原因。

之所以SQLID找到的执行计划和F5得到的执行计划不同，根本原因是F5得到的执行计划实际是封装了EXPLAIN PLAN命令，其未真正执行这条SQL，而SQLID是真正执行的SQL在缓存中的ID，因此是真正执行了的SQL，执行计划是否准确，就看是不是真正执行了这条SQL，正如@dbsnake所说，EXPLAIN PLAN方式的执行计划有可能不准，因为其未真正执行这条SQL。

5. 无论是3还是4对于慢SQL的执行计划，SQL慢的原因基本确定是由于选择了“索引2”的INDEX SKIP SCAN或INDEX FULL SCAN，没有选择r_date作为前导列的主键索引，但为什么CBO这样选择的呢？

CBO是基于COST的优化，根据表的信息等统计信息综合SQL各种执行路径的成本，选择出来的成本最低的一个执行路径，作为SQL的执行计划，可以用10053看到SQL各种执行计划的成本计算。

运行DBA同事发现这张表从4月份，统计信息就没有被更新过，虽然Oracle每晚都有固定的夜维窗口，执行统计信息的自动采集，但他的采集也是有条件的，当表的数据量变化未达到一定的条件时，是不会触发自动采集。

因此统计信息不准，可以作为一个怀疑的理由。

6. 针对这种问题，可能有三种解决方法：

(1) 就用开发人员使用的k_date代替r_date，因为已经验证可以使用正确的k_date索引，前提是逻辑上相同就行，相当于从业务上对SQL进行了改写，针对此场景可用，并不通用。

(2) 仍旧使用r_date，首先要确定“索引1”的成本肯定要低于“索引2”，可以使用等价的select r_date from table where rownm<=10000 and r_date>='2015-06-01' and r_date<='2015-06-02'来在生产环境中执行，通过SQLID查找对应的执行计划，以确定最优的执行计划是什么，如果确定是“索引1”，可以使用HINT强制SQL使用“索引1”，只是这张表的数据量并不会有一个显著的变化，因此才可以将HINT作为一种方法，使用HINT的副作用，就是无论环境有何变化，都会使用HINT中明确的索引，一旦环境的变化导致最优执行计划有变，那么HINT就比较危险了，而且HINT是需要程序修改的，因此这种方法是下下策。

(3) 手工采集统计信息，更新该表的统计信息，以让CBO可以使用正确的统计信息选择正确的执行计划，这是根本解决之道，且不需要程序修改，当然最好提前看下应用程序中是否使用了HINT等，避免因更新统计信息，造成错误的影响。

7. 运行DBA同学手工收集了统计信息，确认SQL使用“索引1”的INDEX RANGE SCAN，执行时间也恢复了正常，这个问题基本已解。

总结：

1. EXPLAIN PLAN得到的执行计划有可能不准，执行计划是否准确主要看是否真正执行了SQL语句。

2. 要明白INDEX SKIP SCAN的适用条件，不是什么时候带有INDEX的执行计划都是最好的，需要看场景。

3. 如果长期未自动采集统计信息，手工采集前，至少我认为应该确认下应用自身没有加HINT等可能因统计信息变更产生影响的情况。

4. 有时候使用业务逻辑的替换，也可以实现想要的目的，这要根据实际具体看了。

5. 之所以开头说这是一个头疼的问题，其实这问题是有一定代表性的，测试的时候没发现，主要原因还是因为测试环境和生产环境的差异性，有些问题测试中怎么都没事，一到生产就有问题，对于这种性能问题，如何在上线前发现，有些时候是不容易的，是否我们可以引入生产的数据量、统计信息？

6. 要对问题的排查过程有一个清晰的认识，根据什么信息，推断可能是什么原因，用什么方法论证推论，根据现象找到根本原因，再根据若干解决方案作比对，选择最优的方案。说起来容易做起来难，只能靠积累、思考，慢慢熟练起来。