AWR报告停止收集的解决方法

一、错误信息

数据库的alert日志里面显示了以下报错：

二、出现的问题平台

Oracle Database - Enterprise Edition - Version 11.2.0.3 and later  Information in this document applies to any platform.

该问题在oracle 11.2.0.3和以后的版本中都会出现；

三、症状

• Manual snapshot generation is slow, often running for 30 minute or more.（手工运行awr报告的时候超过30分钟）
• Alert log shows messages similar to:   
  

  'Suspending MMON slave action kewrmrfsa_ for 82800 seconds'

• AWR stops generating snapshots automatically.（awr报告停止自动运行）
• M00x trace file shows that the following SQL is taking a long time.
  

  UPDATE wrh$_datafile dfh SET (snap_id, filename, tsname) = (SELECT /*+ordered use_nl(f) index(f) index(ts) */ :lah_snap_id, v.name name,ts.name tsname FROM v$dbfile v, file$ f, ts$ ts WHERE f.file# = v.file# AND f.status$ = 2 AND f.ts# = ts.ts#   AND f.file# = dfh.file# AND (f.crscnbas + (f.crscnwrp * power(2,32))) = dfh.creation_change#) WHERE (file#, creation_change#) IN (SELECT f.file#, f.crscnbas + (f.crscnwrp * power(2,32))creation_change# FROM file$ f WHERE f.status$ = 2) AND dbid = :dbid AND snap_id < :snap_id

四、原因

The symptoms shown are caused by a particular UPDATE statement exceeding the time quota for the MMON slave task. This is fixed by:

Bug 16041961AWR SNAPSHOTS NOT BEING GENERATED AFTER UPGRADE TO 11.2.0.3

由一个特定的update语句执行的时间过长导致的；

五、解决方法

The preferred option is to apply the patch for Bug 16041961 AWR SNAPSHOTS NOT BEING GENERATED AFTER UPGRADE TO 11.2.0.3

There is also a workaround of skipping the flushing of the problem table. That can be achieved by using the _AWR_DISABLED_FLUSH_TABLES parameter against the ''WRH$_DATAFILE' ' table:

SQL> alter system set "_awr_disabled_flush_tables"='WRH$_DATAFILE' ;

修改隐藏参数；

The impact of using the workaround is that AWR won't show newly added data files (that is, files added after the workaround was enabled), and any data files that were moved or renamed will show their old names. When a permanent fix/patch is applied, one can disable the workaround using the following command:

修改该参数会导致awr报告不能显示新增加的数据文件或者数据文件修改名称后还是显示老的文件名，处理方法是把该隐藏参数恢复默认；

SQL> alter system set "_awr_disabled_flush_tables"='';

At that point, subsequent snapshots will run both the insert and the update of WRH$_DATAFILE.

六、该性能参数的设置

Awr报告的收集需要消耗主机的性能，当awr报告的收集时间超过30分钟后，说明这个时候数据库的性能已经很慢了，数据库处于相当繁忙的状态；

数据库为了保证业务的正常运行，就自动把awr的功能关闭，减少系统的开销；

从一个软件的架构师和设计人员来说这样是合理的。（以上纯属个人猜想如有错误，请指出）