oracle 10231事件,常用的Oracle数据库诊断事件

前几天老白发过一个关于如何使用Oracle数据库诊断事件来进行诊断分析的文章，由于时间关系，当时没有介绍哪些诊断事件可以用于我们日常的运维、优化与诊断分析。今天老白把以前收集的一些常用诊断事件给大家分享一下：SQL及应用分析分析会话中SQL执行情况(10046)分析会话中的主要等待事件(10046)分析优化器的行为(10053)分析执行计划产生的依据(10053)断语分析(10060)HASH JOIN统计信息(10104)远程SQL执行情况(10241)位图索引访问(10710)位图索引操作(10711-10717)PL/SQL的执行情况(10928，10938)锁相关锁(10704)全局锁(10706)段分析分析段中数据的组成(BLOCK DUMP)分析索引的分裂情况(TREE DUMP)索引分裂情况(10224)引起位图索引改变的DML操作(10719)系统分析/会话分析SYSTEMSTATE DUMP/PROCESSSTATE DUMP数据库启动时跟踪事务恢复分析(10013)DUMP UNDO段头(10015)library cache(10049)系统行为分析排序行为分析(10032/10033)FREELISTS管理(10045)HWM活动(10081)跟踪PMON(10246)跟踪共享CURSOR(10270)跟踪PRE-FETCHING机制(10299)跟踪DIRECT PATH机制(10357)跟踪并行查询(10390-10393)跟踪SMON的行为(10500)改变系统行为刷新DB CACHE(FLUSH_CACHE)清理临时段(DROP_SEGMENTS)跳过坏块(10231)

下面我们通过一个案例来看看如何使用诊断事件协助我们分析。用户准备上线的一套系统，有一条执行十分频繁的语句：select ... from ... where rownum<=10，执行速度十分慢，而三天前测试的时候是没问题的。按理说这条语句除了rownun<=10外没有任何条件，就是对这张表的数据块进行扫描，找到10条记录就返回。根据这张表的情况，一个数据块中至少也能放100多条数据。按理说这样的sql的执行时间是十来个毫秒级别的。老白当时正在客户现场协助项目的上线护航，三天72小时的护航工作中，真正用得到我的时间并不多，所以大多数时间里，我也就在现场看看资料睡睡觉。拿到这个任务后，首先执行了以下这条SQL，发现返回10条记录需要59秒钟。于是我马上做了一个autotrace,使用set autotrace on后再执行这条SQL ,发现BUFFER GET高达20多万。此时离系统正式上线只有不到1个小时了，部分前期业务也已经开始运行。必须尽快定位问题并解决问题。于是老白根据SQL执行的原理进行分析，如果执行了20多万BUFFER GET，如果一个GET返回5行数据的话，如果存在数据，2个GET就可以完成这条SQL了。这就说明前面的4万多次GET并没有读到有效的数据。难道前面都是空数据块？

和开发商沟通了这个问题，问他们是不是这张表曾经有很多记录，然后做过数据删除操作。开发商确认了一番后否认存在过这种操作。为了证明开发商所说的是错误的，于是我做了一个block dump，把这张表前面的几十个数据块都DUMP出来了。一分析，发现前面DUMP出来的全部是空块，所有的数据都被标识为已删除。正在和现场工程师沟通这个问题的时候，开发商的信息也反馈回来了，前阵子一个接口出现了问题，导致了这张表的数据积压，于是当时做过一次数据清理，删除了90%以上的数据。

问题很清晰了，于是处置方式也变得简单起来，这张表目前存在的数据不多，于是move一下表，这条SQL执行速度就恢复正常了。