在数据库运行的过程中,我们有时会碰到数据库hung住的问题,在这个时候很多人会选择尽快让它恢复正常而不是找出问题的root cause. 只有在问题被解决后,才意识到需要找到root cause来避免再次碰到相同的问题; 下面就讲讲如何分析发生在过去的数据库性能问题 (这是一篇讲方法论的blog,并没有涉及到具体的案例,稍后会有更多具体案例的Blog)
1. 首先要申明的是,对于这样的问题,我们需要有一个正确的期望: 不一定能够找到root cause,这取决于发生问题时是否收集到了足够的信息.
2. 梳理我们可以收集到的信息,一般的可以先检查下面的日志
a) 操作系统日志,参照文档 note 1349613.1 - How To Gather The OS Logs For Each Specific OS Platform
b) 数据库alert log
c) 操作系统resource(cpu,memory,IO,swapping)使用的状况,推荐使用OSWbb (也可以是nmon等第三方工具)
有的时候可以通过上面的日志找到一些蛛丝马迹,比如有时alert log中会提示当时有过多的swap活动,或提示生成了 enqueue/ row cache enqueue 等等的trace,或提示diag后台进程生成了systemstate dump trace,那么进一步就是要分析这些trace了;又比如OSWbb的ps输出显示当时有很多和数据库无关的进程在消耗过多的cpu等等,那么这就证明问题和数据库无关了.
3. 接下来要收集发生问题时间段的AWR report和ASH report
但是往往发生问题后数据库被重起了,那么很不幸AWR report很可能没有发生问题时间段的信息,那么这样的AWR对我们分析这个问题就没有意义了.
ASH在大部分的情况下都是可以收集到发生问题时间段的信息,从中可以查到数据库top的等待事件/session; 然后根据具体的问题,进行进一步的分析
4. 如果之前收集到的信息不足以找出问题的原因,我们还有一个地方可以查,那就是 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等
参与此主题的后续讨论，可以访问我们的中文社区，跟帖 "如何分析发生在过去的数据库性能问题"。
