如何使用Oracle诊断事件

昨天我发了一篇诊断事件的文章，建议国产数据库参考一下Oracle的诊断事件，能够为用户提供一些常用的诊断事件。随后有朋友问我，Oracle都有哪些诊断事件，能不能写篇文章归类分析一下，他们也好参考。今天我就简单介绍一下Oracle等待事件的总体情况，特别重点介绍一些与数据库优化相关的诊断事件。

Oracle的诊断事件主要用于四个方面，1）根据需要DUMP数据用于分析；2）当某个ORA错误发生时产生DUMP；3）修改数据库运行特性；4）在数据库运行的时候获取额外的TRACE信息。

从trace的分类上也分为immediate dump、ON-ERROR DUMP、变更运行特性、附加输出性trace等几种。

Immediate dump主要是用于做一些当前数据、内存的DUMP，用于分析问题。比如：alter session set events 'immediate  trace name controlf level 10';用于将控制文件DUMP出来，可以用于分析控制文件里的一些信息，并用于定位ORACLE的BUG，或者用于分析数据库出现问题时的内在原因。

ON-ERROR DUMP是为了获得数据库错误的更为详细的信息，从而用于分析数据库的某个错误的更深层次的原因。比如event = "60 trace name errorstack level 1"可以在数据库出现死锁时生成LEVEL 1级的TRACE。某些系统如果频繁出现死锁，动不动把TRACE写满了，那么也可以用这个事件关闭ORA-60产生的TRACE。

变更运行特性的事件一般是用于修复某些BUG或者让数据库针对某种业务场景做一些运行调整，从而满足用户的需求。

第四类TRACE是要求数据库输出更多的调试信息，从而分析某些问题。昨天说的10046、10053等TRACE都是此类TRACE。

TRACE可以在系统级设置，也可以在会话级设置，上面这张图说明了系统级TRACE和会话级TRACE之间的关系。会话会继承系统级TRACE，也可以覆盖系统级TRACE的设置。某些TRACE也可以单独在会话级设置。

不管TRACE的能力有多强，对于运维人员来说，TRACE的主要作用是两个：故障诊断和性能优化。常见的可用TRACE进行分析的故障报考实例或者进程crash （Internal errors /ora-600/ORA-7445、OS与RDBMS之间的兼容性引发的问题、Segmentation violations, UNIX/linux的bus errors 、windows的Access violations等）、可能由于等待某个事件而 hang 住数据库或者某些会话、进程陷入非正常的循环（loop）、系统变慢等等。

如果没有出现HANG或者LOOP现象，那么很可能是数据库系统出现了性能问题，性能优化可以从硬件、DB、应用等层面进行，此时除了TRACE外，应该同时使用AWR/ADDM进行基础性能分析，EVENT 10046和TKPROF是很好的应用性能分析工具，也是十分常用的性能诊断工具。

当数据库出现HANG或者LOOPING的时候，各级STATE DUMP（SYSTEM STATE DUMP/PROCESS STATE DUMP）都是十分重要的分析工具。此外HANGANALYZE也是一个十分有效的TRACE工具。此外V$SESSION_WAIT, V$LOCK, V$LATCH, V$LATCHHOLDER这些系统视图也是很好的辅助分析工具。

比如某个数据库HANG住，被迫重启，需要了解为什么会HANG住。一般情况下我们可以查找diag的TRACE，在Oracle 10g之后，当数据库出现HANG或者十分缓慢的时候，DIAG会自动产生一个SYSTEM STATE DUMP或者HANGANALYZE，这个可以作为事后分析问题的十分重要的素材。

此时的分析还是要从分析故障的起点ALERT LOG中去查找答案。这是很多缺乏经验的DBA经常犯的错误，那就是当问题出现的时候没有第一时间去查看ALERT LOG，而是盲目的去做各种分析。在这个案例中，ALERT LOG里无明显线索，只有一个SYSTEM STATE DUMP可供分析，那么使用ass.awk分析SYSTEM STATE DUMP可能可以找到一些供下一步分析的蛛丝马迹，并了解当时系统的总体情况。

图片

上面是ass分析的结果可以看出以下的信息：1）BLOCKER未知，latch c0000000c2df3b70可能是分析的关键；2）存在一个PR锁，说明有进程正在启动，PR锁的持有者是41号进程，

latch c0000000c2df3b70的持有者也是41号进程。因此41号进程是下一步分析的要点。

通过在SYSTEM STATE DUMP中搜索发现41号进程是MMON进程，正在等待某个子进程启动完毕。因此可以得到信息，下一步分析的要点是查看MMON正在启动哪个进程。

图片

OSP REQ HOLDER对象中，我们看到了MMON在启动m000的时候，进程启动状态是“DEAD”。我们获得了一个十分重要的信息。mmon是否持有了某个资源，hang住了本系统，大量会话等待log file switch(checkpoint incomplete)，需要查看ckpt的PROCESS STATE DUMP。

图片

从上面的信息可以看出，阻塞CKPT的会话是fbf5e4278，就是mmon本身。至此，monn启动m000失败的原因还需要进一步排查，不过从上面的分析我们可以获得足够的信息了。故障原因是mmon启动m000失败导致了mmon HANG住，mmon持有的pr锁阻塞了ckpt

ckpt阻塞了log file switch。这个问题在宕机4小时前故障就发生了，同时xit中出现了大量的ROW CACHE LOCK WAIT TOO LONG的告警。如果我们能够及时发现这个告警，杀掉mmon或者调整statistics_level可解决问题，避免宕机出现。