作者:韩涛
文章来自微信公众号:平台人生
金庸说:有人的地方就有江湖;我说:有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执行计划改变的原因都有哪些方面呢?
统计信息失真
查看表和索引统计信息,last_analyzed时间和num_rows均为前一天收集,且无大的变化。
图五 T_PART统计信息情况
查询结果集变化较大
虽然表T_PART较大,但查看几个分区中object_id的数据分布后,发现并没有严重的倾斜,基本可以排除这种可能。
图六object_id分布引情况
子查询返回结果较多
测试发现子查询(select id*1 from t2 where id2=:1)每次都会返回唯一值,且查看绑定变量后发现均为888,查询结果均命中在T_PART的T_RANGE_P1分区中。
图七 T2表内容
相关对象状态异常
从走索引变到了走全表,如果索引状态异常了,自然就可以说的通了。马上去查看索引状态,果然发现T_RANGE_P3分区上面的本地索引是UNUSABLE的。
图八 IND_LOCAL状态
通过列举有可能产生问题的原因,仅发现分区索引方面有异常,那么问题来了,既然IND_LOCAL是本地索引,理论上来说每个分区之间应该是互不影响的。不管那么多,在没有其他思路之前,还是第一时间把问题索引重建了。然后新上的SQL执行计划就正常了!!!看来造成执行计划改变的原因的确是T_RANGE_P3分区上的索引失效;难道ORACLE之前鼓吹的分区表的各种好处都是逗我的吗……为了揭开事实的真相,看来我们要真刀真枪的大干一场了。
三、 大胆假设,小心求证
向本地索引原理发起挑战
既然之前ORACLE宣传了本地索引那么多好处,那就用实验来炼一炼这枚真金吧。从最普通的方式开始,object_id为10005的记录均命中在T_PART的T_RANGE_P1分区中,在T_RANGE_P3分区索引依旧为UNUSABLE状态下,是否能够正常走上索引呢?结果发现人家还的确是能够走上索引的。看来ORACLE的确没有忽悠咱。
图九语句执行计划
我们打开的方式不对?
既然带入具体值没有问题,那就只能怀疑是子查询的问题了。难道是因为分区键的值是通过一个子查询结果来获取的原因吗?我们依旧来个实验测试一下,看看10053报告中是怎么说的。
可以看到,同样在本地索引中某个分区失效的情况下,如果分区键的值是直接给出的方式,优化器可以正常计算出对象的各种指标,和ORACLE的预期一致;如果分区键的值是通过一个子查询结果来获取的,优化器计算对象相关指标是通过综合其余分区统计信息来得到的,对于本地索引IND_LOCAL,直接标记为UNUSABLE,所以才导致在命中正常分区时仍然不能使用本地索引。
图十未使用子查询10053
图十一使用子查询10053
四、 补充秘籍
通过分析,问题的原因和触发场景基本已经清楚了。虽然最终还是解决了问题,但还是花费了较多的时间。对于分区表的本地索引,虽然在原理上某一个分区索引失效不会影响其他分区,但对于一些特殊情况,比如这次的事件中,分区键的值是通过一个子查询结果来获取的,导致优化器不能确定分区扫描的范围,在本地索引中某个分区失效的极端情况下,优化器将直接认为相关本地索引为不可用状态,造成执行计划改变的严重后果。看来我们不光要多多学习ORACLE的新技术,对已有的成熟技术也是不能放松的,本期分享就到这里了,下期我们就来深入挖掘一下索引失效的问题,看看强壮的本地索引到底存在着哪些软肋呢?