AWR收集缓慢、挂起的几种常见情况分析

AWR （ Automatic Workload Repository ）作为对数据库性能诊断的工具，采集与性能相关的统计数据，根据这些统计数据中的性能指标，以跟踪潜在的问题。若因某些情况导致相关数据无法收集，就会对数据库性能诊断大打折扣。

以下列举 AWR 收集缓慢、挂起或缺失常见的几种情况：

• STATISTICS_LEVEL  参数不为 ALL 或 TYPICAL

• SYSAUX 表空间不足

• 系统资源 I/O 、 CPU 使用率过高

• MMON/MMNL 进程异常

• 相关 FIXED TABLE 统计信息不准确

1、STATISTICS_LEVEL  参数不为 ALL 或 TYPICAL

初始化参数 STATISTICS_LEVEL ， AWR 的采集信息受到参数 STATISTICS_LEVEL 的影响。这个参数有三个值：

BASIC ： AWR 统计信息的关闭，只收集少量的数据库统计信息。

TYPICAL ：默认值，只有部分的统计收集，都是需要监控 oracle 数据库的典型行为。

ALL ：所有可能的统计都被捕捉，并且有操作系统的一些信息，这个级别的捕捉用的较少，比如要更多的 sql 诊断信息。

一般不会随便修改该参数，都使用默认值 TYPICAL ，所以这种情况下导致 AWR 无法收集统计数据的很少的。

2、SYSAUX 表空间不足

AWR 采集的统计数据都以 WRM$_*  和  WRH$_* 的格式命名的表存储在 SYSAUX 表空间上（ M  代表元数据 (metadata) ，而 H  代表历史数据  (historical) ）。可通过 @?/rdbms/admin/awrinfo.sql 或 x$kewrtb 查询相关的表信息。虽然 因 SYSAUX 表空间不足导致 AWR 无法生成是个低级问题 ，但是有一种情况需要注意，因为 BUG 等导致 ASH/AWR 的基表数据无法清理。如：

SQL> select * from dba_hist_wr_control;      DBID SNAP_INTERVAL        RETENTION            TOPNSQL 
---------- -------------------- -------------------- ---------262389084 +00000 01:00:00.0    +00007 00:00:00.0    DEFAULT

正常的每小时产生一个 SNAPSHOT ，保留 7 天。但一些基表如 WRH$_ACTIVE_SESSION_HISTORY 因为某些原因没有根据 sys.wrm$_wr_control 的设定进行清理。 SNAPSHOT 快照的保留就会超过 7 天，这时会导致 SYSAUX 被撑爆，以及收集 AWR 报告很慢的情况。具体解决办法 2 个：

1）alter session set “_swrf_test_action”=72;

2) 手工删除过期无用的快照：

exec dbms_workload_repository.drop_snapshot_range(low_snap_id => xxx, high_snap_id => xxxx, dbid => 262389084);

MOS 文档：

WRH$_ACTIVE_SESSION_HISTORY Does Not Get Purged Based Upon the Retention Policy (Doc ID  387914.1 )

3、 系统资源 I/O 、 CPU 使用率过高

当系统负载很高时，许多用户进程都在争用资源， AWR 报告的收集需要消耗系统主机的性能，当 awr 报告的收集时间超过 15 分钟，若这个时候数据库处于相当繁忙的状态，   数据库为了保证业务的正常运行，就自动把 AWR 的功能关闭，减少系统的开销，这是11g 功能的增强 。这种情况基本如下：

alert.log 中会出现如下告警信息：

Suspending MMON slave action xxx for 82800 seconds

或者 mmon trc 中出现如下的告警信息：

Unable to schedule a MMON slave at: Auto Flush Main 1
  Slave action has been temporarily suspended    - Slave action had prior policy violations.
  Unknown return code: 101

 --可根据https://community.oracle.com/thread/2153562参考：If the system is so over-loaded that it takes over 15 minutes to gather statistics or other MMON tasks, this error is expected.It is a functionality enhancement in 11g, as it prevents MMON from locking 
resources those other processes might be waiting for. In 10g , mmon slaves are allowed to run indefinitely.

从日志看，存在大量的 Slave action has been temporarily suspended ， 这是 11g 功能的增强，当系统处于 overload 状态时， MMON 进程收集统计信息超过 15 分钟，则会终止该任务，  10g 会无限延期。所以系统资源不足也会导致 AWR 统计信息无法正常收集。

为什么是 15 分钟？请参考 MOS 文档：

Troubleshooting ORA-12751 "cpu time or run time policy violation" Errors ( 文档  ID 761298.1)

Troubleshooting: Missing Automatic Workload Repository (AWR) Snapshots and Other Collection Issues ( 文档  ID 1301503.1)

4、MMON/MMNL 进程异常

Memory Monitor(MMON) ： MMON 主要用于 AWR ， ADDM ， MMON 会从 SGA 将统计结果写到系统表中

The Memory Monitor Light (MMNL) ： mmon 进程主要是内存中 sql 信息， ash 信息的收集工作，如果这些信息需要写入到磁盘（即一些数据字典表）中，那么就需要 MMNL 进程负责写入

MMON 、 MMNL 和 Mnnn 这些进程用于填充自动工作负载存储库（ Automatic WorkloadRepository ， AWR ），这是 Oracle 10g 中新增的一个特性。 MMNL 进程会根据调度从 SGA 将统计结果刷新输出至数据库表。 MMON 进程用于“自动检测”数据库性能问题，并实现新增的自调整特性。 Mnnn 进程类似于作业队列的 Jnnn 或 Qnnn 进程； MMON 进程会请求这些从属进程代表它完成工作。 由此可见， MMON 和 MMNL 进程异常是 AWR 不能自动收集的根本原因。

遇到 AWR 无法收集的情况可以根据文档（ ID 1301503.1 ）进行排查，检查 mmon 和 mmnl 进程是否正常。

ps -ef|egrep "mmon|mmnl"  #查看mmon和mmnl进程是否存oracle    32674      1  0 21:22 ?        00:00:01 ora_mmon_<sid>oracle    32676      1  0 21:22 ?        00:00:01 ora_mmnl_<sid>

这两个进程是 oracle 的非核心进程，可以 kill 掉，它们会自动启动进程，并且自动维护。若这两个进程没有问题，可以手动生成 AWR 看是否有效：

exec  dbms_workload_repository.create_snapshot();  然后再进一步诊断问题。

因为这两个进程都非核心进程，所以很多文档都是说可 kill ，重新唤起这个进程，让 AWR 可继续生成。在 11.2.0.4 中可能会存在起不来的情况，原因根据 MOS 文档： AWR Snapshots Are Not Being Created Because MMON Is Not Being Respawned ( 文档  ID 2023652.1) 可知：

5、FIXED TABLE 统计信息不准确

查看 mmon  进程的 trace 文件，出现以下报错：

** KEWROCISTMTEXEC - encountered error: (ORA-12751: cpu time or
 run time policy violation)
  *** SQLSTR: total-len=295, dump-len=240,
      STR={insert into WRH$_SERVICE_STAT   (snap_id, dbid,
      instance_number,    service_name_hash, stat_id, value) select
      :snap_id, :dbid, :instance_number,   stat.service_name_hash,
      stat.stat_id, stat.value from  v$active_services asvc, v$service_st}
 DDE rules only execution for: ORA-12751

查看该 SQL 为何执行缓慢，发现 v$service_stats 视图数量很大。该视图记录的是最小的性能统计数据集，其中有个自动 service_name 来着 v$services ，它显示关于服务的信息。e xpdp 每次备份开始，都会新增一个 service name ，备份结束后会去掉该 service name ，该动作会记录在 alert log 中，这个动作就会导致 v$service_stats  视图出现很多 unknown 的记录。

错误的执行计划：

每次逻辑导出时，会在 v$service_stats 视图中增加 service_name=unknow 的记录，导致 v$service_stats 视图中累积存储了大量 unknow service name 的记录， AWR 快照生成过程中在执行上述 SQL 时，由于 fixed table 统计信息不准确或者尚无统计信息， oracle 选择了效率较低的执行计划， SQL 的执行消耗大量时间，导致 oracle 维护任务 cpu time policy violation ， AWR 快照生成中断。

解决办法是：手动收集 fixed table 的统计信息（在 12 c 之前，固定的统计数据没有自动收集，它是对所有 X$ 基表统计信息的收集，这个收集是要相对比较长时间的，同时评估好收集之后对其它 SQL 语句执行的影响。如 V$SESSION 、 V$PROCESS 、 V$LOCK 等常用视图相关 SQL 语句的执行计划影响）

select table_name,num_rows,last_analyzed from dba_tab_statistics where last_analyzed is not null order by last_analyzed desc;
 
exec dbms_stats.gather_fixed_objects_stats(no_invalidate => false);

fixed table 的统计信息 请参考文档：Fixed Objects Statistics (GATHER_FIXED_OBJECTS_STATS) Considerations (文档 ID 798257.1)