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

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

目前创新互联建站已为1000多家的企业提供了网站建设、域名、网络空间、网站运营、企业网站设计、七星关区网站维护等服务,公司将坚持客户导向、应用为本的策略,正道将秉承"和谐、参与、激情"的文化,与客户和合作伙伴齐心协力一起成长,共同发展。

 

以下列举 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_oracle    32676      1  0 21:22 ?        00:00:01 ora_mmnl_

这两个进程是 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) 可知:

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

 

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 的记录。

 

错误的执行计划:

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

每次逻辑导出时,会在 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)



本文标题:AWR收集缓慢、挂起的几种常见情况分析
文章分享:http://scyanting.com/article/gisphc.html