人在江湖漂，哪能不挨刀之ORACLE分区表(上)

作者：韩涛
文章来自微信公众号：平台人生

金庸说：有人的地方就有江湖；我说：有ORACLE的地方才是我们的江湖。与ORACLE斗争的故事历来有之，不少前辈也是以一人之力，挽狂澜于既倒，扶大厦之将倾，一战成名。作为我们团队中的一名DBA，深知这个行当的确是危机四伏，每天都必须做好和ORACLE进行生死对决的准备。挑战总是转瞬及至，我们总是不能放松的，今天我就来分享一次我们的团队遇到的一则生产案例，看看到底鹿死谁手！

一、 接受挑战

早上才到工位，就看到手机上收到了一条DB主机CPU资源利用率高的告警信息，看来ORACLE又发起挑战了，马上投入战斗。战斗的第一步就是找到对手，登陆数据库发现库中大部分活动会话都在执行一条查询语句，数量居然高达几十个。很明显，问题就是出在这个查询语句上。能够快速定位到问题算是先胜一筹了。有了目标，接下来就是向对手发起精确打击，首先分析SQL语句及执行计划。

图一 SQL执行计划

逻辑非常简单的一条语句，为何会突然堵塞呢？从执行计中可以看到对T_PART进行了某几个分区的全分区扫描，这点很可疑，马上去查看T_PART表大小，发现T_PART表的每个分区大约10G左右，要扫描若干个如此大的分区，难怪会造成阻塞了，同时可以推测这条SQL的执行计划应该在近期改变过，否则问题肯定早就暴露了，为了验证这个猜测，马上查看了该SQL的历史执行情况和历史的执行计划、该表上的索引情况，果然在早上9:00-9:30之间，执行计划由通过IND_LOCAL索引检索，变为了当前的全分区扫描；逻辑读，物理读等也由个位数暴涨到一千左右。

图二 SQL历史执行情况

图三 SQL历史执行计划

图四 T_PART索引情况

二、 层层深入，定位原因

现在问题的发展相对清晰了，由于某种原因导致了SQL执行计划从走索引改变为全分区扫描，SQL执行时间变长，最后造成了业务阻塞。那么会影响SQL执行计划改变的原因都有哪些方面呢？

1. 统计信息失真
   查看表和索引统计信息，last_analyzed时间和num_rows均为前一天收集，且无大的变化。
   
   图五 T_PART统计信息情况
2. 查询结果集变化较大
   虽然表T_PART较大，但查看几个分区中object_id的数据分布后，发现并没有严重的倾斜，基本可以排除这种可能。
   
   图六object_id分布引情况
3. 子查询返回结果较多
   测试发现子查询(select id*1 from t2 where id2=:1)每次都会返回唯一值，且查看绑定变量后发现均为888，查询结果均命中在T_PART的T_RANGE_P1分区中。
   
   图七 T2表内容
4. 相关对象状态异常
   从走索引变到了走全表，如果索引状态异常了，自然就可以说的通了。马上去查看索引状态，果然发现T_RANGE_P3分区上面的本地索引是UNUSABLE的。
   
   图八 IND_LOCAL状态

通过列举有可能产生问题的原因，仅发现分区索引方面有异常，那么问题来了，既然IND_LOCAL是本地索引，理论上来说每个分区之间应该是互不影响的。不管那么多，在没有其他思路之前，还是第一时间把问题索引重建了。然后新上的SQL执行计划就正常了！！！看来造成执行计划改变的原因的确是T_RANGE_P3分区上的索引失效；难道ORACLE之前鼓吹的分区表的各种好处都是逗我的吗……为了揭开事实的真相，看来我们要真刀真枪的大干一场了。

三、 大胆假设，小心求证

1. 向本地索引原理发起挑战
   既然之前ORACLE宣传了本地索引那么多好处，那就用实验来炼一炼这枚真金吧。从最普通的方式开始，object_id为10005的记录均命中在T_PART的T_RANGE_P1分区中，在T_RANGE_P3分区索引依旧为UNUSABLE状态下，是否能够正常走上索引呢？结果发现人家还的确是能够走上索引的。看来ORACLE的确没有忽悠咱。
   
   
   图九语句执行计划
2. 我们打开的方式不对？
   既然带入具体值没有问题，那就只能怀疑是子查询的问题了。难道是因为分区键的值是通过一个子查询结果来获取的原因吗？我们依旧来个实验测试一下，看看10053报告中是怎么说的。
   可以看到，同样在本地索引中某个分区失效的情况下，如果分区键的值是直接给出的方式，优化器可以正常计算出对象的各种指标，和ORACLE的预期一致；如果分区键的值是通过一个子查询结果来获取的，优化器计算对象相关指标是通过综合其余分区统计信息来得到的，对于本地索引IND_LOCAL，直接标记为UNUSABLE，所以才导致在命中正常分区时仍然不能使用本地索引。
   
   
   图十未使用子查询10053
   
   
   图十一使用子查询10053

四、 补充秘籍

通过分析，问题的原因和触发场景基本已经清楚了。虽然最终还是解决了问题，但还是花费了较多的时间。对于分区表的本地索引，虽然在原理上某一个分区索引失效不会影响其他分区，但对于一些特殊情况，比如这次的事件中，分区键的值是通过一个子查询结果来获取的，导致优化器不能确定分区扫描的范围，在本地索引中某个分区失效的极端情况下，优化器将直接认为相关本地索引为不可用状态，造成执行计划改变的严重后果。看来我们不光要多多学习ORACLE的新技术，对已有的成熟技术也是不能放松的，本期分享就到这里了，下期我们就来深入挖掘一下索引失效的问题，看看强壮的本地索引到底存在着哪些软肋呢？