如何分析发生在过去的数据库性能问题

在数据库运行的过程中,我们有时会碰到数据库hung住的问题,在这个时候很多人会选择尽快让它恢复正常而不是找出问题的root cause. 只有在问题被解决后,才意识到需要找到root cause来避免再次碰到相同的问题; 下面就讲讲如何分析发生在过去的数据库性能问题 (这是一篇讲方法论的blog,并没有涉及到具体的案例)

• 首先要申明的是, 对于这样的问题,我们需要有一个正确的期望: 不一定能够找到root cause, 这取决于发生问题时是否收集到了足够的信息.

• 梳理我们可以收集到的信息, 一般的可以先检查下面的日志

• 操作系统日志, 参照文档 note 1349613.1 - How To Gather The OS Logs For Each Specific OS Platform

• 数据库alert log

• 操作系统resource(CPU, memory, IO, swapping)使用的状况, 推荐使用OSWbb (也可以是nmon等第三方工具)

有的时候可以通过上面的日志找到一些蛛丝马迹, 比如有时alert log中会提示当时有过多的swap活动, 或提示生成了 enqueue/ row cache enqueue 等等的trace, 或提示diag后台进程生成了systemstate dump trace, 那么进一步就是要分析这些trace了;又比如OSWbb的ps输出显示当时有很多和数据库无关的进程在消耗过多的CPU等等, 那么这就证明问题和数据库无关了.

• 接下来要收集发生问题时间段的AWR report和ASH report

但是往往发生问题后数据库被重起了,那么很不幸AWR report很可能没有发生问题时间段的信息, 那么这样的AWR对我们分析这个问题就没有意义了.

ASH在大部分的情况下都是可以收集到发生问题时间段的信息, 从中可以查到数据库top的等待事件/session; 然后根据具体的问题,进行进一步的分析

• 如果之前收集到的信息不足以找出问题的原因, 我们还有一个地方可以查,那就是 dba_hist_active_sess_history.
  这个视图是用来生成ASH report的, 但是ASH report并没有充分的利用这个视图的强大之处,我们通过分析这个视图的详细数据,往往可以找到问题发生的原因.
  可以从宏观和微观两个维度来分析这个视图(用11gR2的dba_hist_active_sess_history做例子):
  比如,宏观:
  a)可以按照一段时间内(发生问题的时间段)这些session等待的非空闲等待事件的类型做分类和求和,就可以知道哪种等待事件最严重
  b)可以按照一段时间内(发生问题的时间段)等待最严重事件的这些session所执行的SQL_ID来汇总求和,可以知道哪个SQL跟这个问题相关
  微观:
  a). 对于某一条dba_hist_active_sess_history的记录,我们可以知道这个session的SESSION_STATE是ON CPU还是WAITING, 如果是ON CPU,那么这个session的event就无意义了; 如果是WAITING, 可以进一步看它的等待事件和BLOCKING_SESSION_STATUS, 如果它是被另一个session阻塞, 那么BLOCKING_SESSION_STATUS这一列就会显示为VALID或 GLOBAL. 然后再检查BLOCKING_INST_ID和BLOCKING_SESSION找到阻塞这个session的是哪里实例上的哪个session
  b). 按照SAMPLE_TIME排序,我们可以找到问题发生的具体的时间点 (还是比较精确的)
  c). 对某个session, dba_hist_active_sess_history还能揭示更多有用的信息, 比如这个session当前执行的SQL语句的类型(SQL_OPCODE, SQL_OPNAME), 这个session是否在PARSE(IN_PARSE, IN_HARD_PARSE等), 它是什么客户端(PROGRAM, MODULE, ACTION, CLIENT_ID, MACHINE), 它使用的PGA(PGA_ALLOCATED), 它使用的temp空间大小(TEMP_SPACE_ALLOCATED)等等

善于使用 dba_hist_active_sess_history能极大地帮助我们分析问题.但是也要注意dba_hist_active_sess_history不是万能的: 如果最终阻塞别人的session当时并不是active的或者它并没有被ASH记录到dba_hist_active_sess_history中, 我们还是不能知道它当时处于一种什么状况.

结语: 总之, 分析类似的问题就是充分挖掘已有的trace/日志的过程, 但是因为缺少足够的诊断日志/信息,很多时候还是无法找到问题发生的原因. 如果我们确实有需要找到root cause, 那么在发生问题时就需要收集到足够多的信息. 比如hanganalyze, systemstate dump等