Oracle数据库卡死或变慢怎么办？基于AWR/ASH的排查思路解析

如何调查数据库“缓慢”或者“挂起”的问题

这篇文章概述了在遇到DB性能缓慢问题时应采取的基本步骤。
数据库性能问题的诱因多种多样，但典型的主要症状是响应迟缓，或者在极端情况下数据库“挂起”。

情况1：数据库缓慢

1）该性能问题是持续发生还是在特定时间段发生？

如果是持续发生

1）收集 ADDM 报告，确认给出的“发现”，并查看相应的“建议”。
2）收集问题发生时间段的 AWR 与 ASH 报告（通常一个小时的就可以了）。通过 ASH 可以定位使用高 CPU 的会话和 SQL 语句。
3）收集一份数据库性能正常时间段的 AWR 报告。
为了确保比较更有价值，收集一天中同一时间或同一工作负载时间段、且持续时间相同的 AWR 报告就够了，但对于大多数性能问题来说，或许需要一份 10-30 分钟的 AWR 报告。

注 1:- 通常收集一个小时的AWR报告就可以了，但对于大多数性能问题来说，或许需要一份10-30分钟的AWR报告
注 2:- 通常使用 ADDM 报告定位主要问题是明智的。由于 ADDM 报告会列出主要的问题点，诊断问题的最初阶段使用 ADDM 报告要比理解 AWR 报告节省很多时间。参考: ADDM
注 3:- 如果怀疑 SQL 性能是造成数据库缓慢的原因，那么收集同一时间段的 ASH 报告。如果怀疑某个特定的 SQL 的导致数据库缓慢，那么以该 SQLID 收集一份ASH报告，同时考虑是否使用sqlhc 来诊断该 SQL 语句，用SQLT也行，只不过SQLT东西太多了，我觉得SQLHC足够了。

如果是特定时间段

收集一份包含问题发生时间段的 ADDM 报告（例如：如果问题发生在 12 点至 3 点之间，那么收集的报告确保涵盖这个时间段或者其中一部分）。确认给出的“发现”，执行相应的“建议”，然后再次检查数据库性能。
收集与上述收集的 ADDM 报告相同时间段（即问题发生时间段）的 AWR 与 ASH 报告。通过 ASH 可以定位使用高 CPU 的会话和 SQL 语句。
收集一份数据库性能正常时间段的 AWR 报告。为了确保比较更有价值，收集一天中同一时间或同一工作负载时间段，并确保报告的持续时间是相同的。

2）当数据库缓慢时，一个或者多个会话的 CPU 使用率很高

收集CPU使用率比预期高的进程的 errorstacks。（如果无法收集 errorstacks，那么收集 pstack 或类似的报告）

SELECT p.pid, p.SPID,s.SID
FROM v$process p, v$session s
WHERE s.paddr = p.addr
AND s.SID = &SID;
connect / as sysdba
ALTER SESSION SET tracefile_identifier = 'STACK_10046';
oradebug setospid 9834
oradebug unlimit

oradebug event 10046 trace name context forever,level 12

oradebug dump errorstack 3
oradebug dump errorstack 3
oradebug dump errorstack 3
oradebug tracefile_name

参考文档：How to Collect Errorstacks for use in Diagnosing Performance Issues. (Doc ID 1364257.1)    

收集CPU使用率高的时间段的 AWR 与 ASH 报告。通过 ASH 可以定位使用高 CPU 的会话和 SQL 语句。
收集一份数据库性能正常时间段的 AWR 报告。为了确保比较更有价值，收集一天中同一时间或同一工作负载时间段，并确保报告的持续时间是相同的。

注 1: 通常收集一个小时的AWR报告就可以了，但对于大多数性能问题来说，或许需要一份10-30分钟的AWR报告
注 2: 通常使用 ADDM 报告定位主要问题是明智的。由于 ADDM 报告会列出主要的问题点，诊断问题的最初阶段使用 ADDM 报告要比理解 AWR 报告节省很多时间。参考: ADDM
注 3: 如果怀疑 SQL 性能是造成数据库缓慢的原因，那么收集同一时间段的 ASH 报告。如果怀疑某个特定的 SQL 的导致数据库缓慢，那么以该 SQLID 收集一份ASH报告，同时考虑是否使用sqlhc 来诊断该 SQL 语句，用SQLT也行，只不过SQLT东西太多了，我觉得SQLHC足够了。

诊断信息收集完成之后，官方文档如下：

Document 352648.1 How to Diagnose high CPU usage problems

3）一个，多个还是所有会话发生了问题？

一个会话: 收集这个会话的 10046 跟踪日志。
其他收集10046的方式，自己搜一下，网上很多。

通过oradebug方式 (建议)
conn / as sysdba
oradebug setmypid
oradebug event 10046 trace name context forever,level 12
oradebug tracefile_name

多个会话: 收集其中的一个或者两个有问题会话的 10046 跟踪日志。

如果是所有会话

收集 ADDM 报告，确认给出的“发现”，执行相应的“建议”，然后再次检查数据库性能。
收集问题发生时间段的 AWR 与 ASH 报告（通常一个小时的就可以了）。通过 ASH 可以定位使用高 CPU 的会话和 SQL 语句。
收集一份数据库性能正常时间段的 AWR 报告。为了确保比较更有价值，收集一天中同一时间或同一工作负载时间段，并确保报告的持续时间是相同的。需要注意的是，对于大多数性能问题来说，或许需要一份 10-30 分钟的 AWR 报告。

注 1: 通常收集一个小时的AWR报告就可以了，但对于大多数性能问题来说，或许需要一份10-30分钟的AWR报告
注 2: 通常使用 ADDM 报告定位主要问题是明智的。由于 ADDM 报告会列出主要的问题点，诊断问题的最初阶段使用 ADDM 报告要比理解 AWR 报告节省很多时间。
注 3: 如果怀疑 SQL 性能是造成数据库缓慢的原因，那么收集同一时间段的 ASH 报告。如果怀疑某个特定的 SQL 的导致数据库缓慢，那么以该 SQLID 收集一份ASH报告，同时考虑是否使用sqlhc 来诊断该 SQL 语句，用SQLT也行，只不过SQLT东西太多了，我觉得SQLHC足够了。

情况2：数据库挂起

一个特定的会话出现挂起，还是几个会话或者所有会话挂起？根据具体的场景收集诊断信息：

1）一个会话出现挂起

收集这个会话的 10046 跟踪日志。
收集这个会话的 errorstacks。

sqlplus / as sysdba
@?/rdbms/admin/addmrpt.sql

收集这个会话出现挂起时间段的 AWR 与 ASH 报告。通过 ASH 可以定位使用高 CPU 的会话和 SQL 语句。
收集一份数据库性能正常时间段的 AWR 报告。为了确保比较更有价值，收集一天中同一时间或同一工作负载时间段，并确保报告的持续时间是相同的。

2）一个 SQL 语句出现挂起

收集这个会话的 10046 跟踪日志。
收集CPU使用率比预期高的进程的 errorstacks。（如果无法收集 errorstacks，那么收集 pstack 或类似的报告）
收集CPU使用率高的时间段的 AWR 与 ASH 报告。通过 ASH 可以定位使用高 CPU 的会话和 SQL 语句。
收集一份数据库性能正常时间段的 AWR 报告。为了确保比较更有价值，收集一天中同一时间或同一工作负载时间段，并确保报告的持续时间是相同的。

参考文档：

Document 1916479.1 How to Investigate Hanging SQL Statements

3）一个以上的会话出现挂起

收集这些会话的 10046 跟踪日志。
收集CPU使用率比预期高的进程的 errorstacks。（如果无法收集 errorstacks，那么收集 pstack 或类似的报告）。
收集CPU使用率高的时间段的 AWR 与 ASH 报告。通过 ASH 可以定位使用高 CPU 的会话和 SQL 语句。
收集一份数据库性能正常时间段的 AWR 报告。为了确保比较更有价值，收集一天中同一时间或同一工作负载时间段，并确保报告的持续时间是相同的。

4）数据库“出现”挂起？

如果数据库出现挂起现象，请参考下面 Hang 文档收集诊断信息。

Document 2293066.1 SRDC – 对于数据库 Hang 的问题如何收集诊断信息
Document 452358.1 Database Hangs: What to collect for support.

有时，数据库实际上没有挂起，但可能发生了与之非常类似的现象，即在CPU上发生了“spinning”。可以使用下面的 Spin 文章来帮助判断遇到的现象是否为真正的挂起，并解决这个问题：

Document 68738.1 No Response from the Server, Does it Hang or Spin?

如果数据库实际上只是缓慢，请参考 数据库缓慢 章节。
每个版本做 hanganalyze 的步骤差不多，供参考：

sqlplus '/ as sysdba'
oradebug setmypid
oradebug unlimit
oradebug hanganalyze 3
-- 等一会，10秒左右
oradebug hanganalyze 3
oradebug tracefile_name
exit

5）大多数会话出现挂起

与数据库挂起处理方式相同。