外键(或者reference字段上)缺失索引导致主表删除时子表出现不必要的全表扫描,这个问题是十分经典的。处置该问题的时候如果用户问题描述的十分清晰,而且处置人员在这方面有经验,是可以很快的定位的。如果用户描述不清,而且你又没有在现场,如何通过常规的分析流程去快速发现这个问题呢?前阵子老白遇到的一个案例就很有代表性。
前几天晚上,老白接到一个电话,说是核心系统删除一条记录的时候,业务受到影响了,似乎是被阻塞了。由于客户的DBA也是在家里远程处置问题,所以也无法把问题描述的很清楚。好在不是关键的操作,而且据说虽然阻塞了几分钟,后来操作也完成了。于是我让客户先采集下ASH报告的数据,然后明天再分析问题的原因。
早上一上班就收到了ASH报告,晚上是客户跑批的时间,系统里面其实比较干净,没啥特殊的东西。
Top User Events DB/Inst: ORCL/orcl1 (Mar 13 21:45 to 21:50)
Avg Active
Event Event Class % Event Sessions
----------------------------------- --------------- ---------- ----------
CPU + Wait for CPU CPU 37.93 1.17
db file scattered read User I/O 26.94 0.83
db file sequential read User I/O 10.13 0.31
log file sync Commit 9.16 0.28
-------------------------------------------------------------
不过db file scattered read还是有点惹眼的。于是往下看:
Sampled #
SQL ID Planhash of Executions % Activity
----------------------- -------------------- -------------------- --------------
Event % Event Top Row Source % RwSrc
------------------------------ ------- --------------------------------- -------
9uj9udbxz6x59 956421471 1 30.82
db file scattered read 26.94 ** Row Source Not Available ** 26.94
delete user_info u where u.user_no='xxxxxxxxxxxxxxxxxxxxxxxxxxx'
这条语句就是出问题的这条,db file scattered read就是这条语句产生的,第一个反应就是缺索引?于是问客户是否这个字段有索引,客户告知这是主键,做了一个awrsqrpt报告,发现这条语句只有一个执行计划,而且是走索引的。这时候就开始怀疑外键的问题了。问了客户,客户马上和开发商确认了,开发商发誓赌咒绝对没有外键。这种情况下,ash分析就发挥作用了。我让用户把故障期间几分钟的几百条ash数据导出来发给我。从ash数据里,很快就发现了问题:
首先在EXECL中用这条SQLID做过滤,把和这条SQLID相关的记录过滤出来,发现全是一个会话的记录。然后我们查看SQL_EXEC_START字段,看到这条语句从21点45分13秒开始到21点50分结束,一条主键删除的语句居然执行了5分多钟。再看后面的等待事件确实都是db file scattered read,从参数上看,扫描了多个文件,确定是全表/全索引扫描无疑了。在往后看
从current_object上看到,都是一个对象,查一下这个对象,发现是另外一张表,再检查一下这张表的外键关系,发现有一个reference指向这张表的user_no。问题完全定位了,下一步开发商去解决了。
10g后的ash数据对于事后定位问题十分有帮助,很多问题都是通过ash数据可以回顾的。这个方法是一个十分通用的问题分析方法,不仅仅适用于分析外键问题,其他的一些问题,也可以用类似的方法来解决,具有一定编程能力的人可以写一些脚本来分析与过滤ASH数据,自动发现可能存在的问题。在老白开发的D-SMART运维知识自动化平台中就有一个问题分析工具,当出现各种预警的时候,该工具是一个通用分析工具,你可以直接点击“问题分析”,系统帮你检查当时的ASH数据,找出其中可能存在的问题。
当系统出问题的时候,只需要点击“问题分析”按钮就可以自动对这个时间段内的ASH数据以及D-SMART采集的其他数据进行综合分析,发现其中可能存在的问题,如下图:
从上面的案例大家可以看出,ASH是十分有价值的数据,对于历史问题分析来说.ASH是一个宝库。因此每个ORACLE DBA都应该熟悉ASH的基表与基表结构,并且可以编写一些小工具,对ASH进行一些专项分析。
作者:白鳝