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

 

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

  • STATISTICS_LEVEL 参数不为ALLTYPICAL

  • SYSAUX表空间不足

  • 系统资源I/OCPU使用率过高

  • MMON/MMNL进程异常

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

 

1、STATISTICS_LEVEL 参数不为ALLTYPICAL

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

BASICAWR统计信息的关闭,只收集少量的数据库统计信息。

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

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

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

 

2、SYSAUX表空间不足

AWR采集的统计数据都以WRM$_* WRH$_*的格式命名的表存储在SYSAUX表空间上(M 代表元数据(metadata),而H 代表历史数据 (historical))。可通过@?/rdbms/admin/awrinfo.sqlx$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/OCPU使用率过高

当系统负载很高时,许多用户进程都在争用资源,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主要用于AWRADDMMMON会从SGA将统计结果写到系统表中

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

 

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


遇到AWR无法收集的情况可以根据文档(ID 1301503.1)进行排查,检查mmonmmnl进程是否正常。

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)可知:

111.png

 

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,它显示关于服务的信息。expdp每次备份开始,都会新增一个service name,备份结束后会去掉该service name,该动作会记录在alert log中,这个动作就会导致v$service_stats 视图出现很多unknown的记录。

 

错误的执行计划:

222.png

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

 

解决办法是:手动收集fixed table的统计信息(在12 c之前,固定的统计数据没有自动收集,它是对所有X$基表统计信息的收集,这个收集是要相对比较长时间的,同时评估好收集之后对其它SQL语句执行的影响。如V$SESSIONV$PROCESSV$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)