升级到Oracle 12c,有时会看到MMON_SLAVE模块的CPU使用率很高。这些模块与12cR1新特性Automatic Report Capturing(自动报告捕获功能)有关。
Oracle 11g引入了real-time SQL monitoring,用于实时监控SQL在实现中的性能,Oracle 12c进一步扩展了其引入的历史SQL监控功能。该功能类似于通过后台进程MMON_SLAVE定期对存储在数据库表中的SQL监控信息进行ASH信息的历史管理。
作为该功能的一部分,一些监控SQL由MMON_SLAVE执行,以识别资源密集型SQL和为这些SQL自动生成SQL监控报告。这些SQL只消耗很少的CPU,预期行为是一个新功能。这样的监控查询可以从(G)V$SQLSTATS中识别出来。
官方说由于12.1中的监控活动,MMON消耗的CPU很少。然而,如果CPU消耗非常高,那么这不是预期的行为,可能是由于优化器为SQL语句选择了次优计划。
从以下两个MMON_SLAVE执行与监视器相关的查询时,CPU消耗和时间都很高:
1)经常在AWR TOP SQL中出现
WITH MONITOR_DATA AS (SELECT INST_ID, KEY, NVL2(PX_QCSID, NULL, STATUS)STATUS,
FIRST_REFRESH_TIME, LAST_REFRESH_TIME, REFRESH_COUNT, PROCESS_NAME, SID, SQL_ID,
SQL_EXEC_START, SQL_EXEC_ID, DBOP_NAME, DBOP_EXEC_ID, SQL_PLAN_HASH_VALUE,
SESSION_SERIAL#, SQL_TEXT, IS_FULL_SQLTEXT, PX_SERVER#, PX_SERVER_GROUP, PX_SERVER_SET,
PX_QCINST_ID, PX_QCSID, CASE WHEN ELAPSED_TIME < (CPU_TIME+ APPLICATION_WAIT_TIME+ CONCURRENCY_WAIT_TIME+ CL
...;
2)在RAC和NON-RAC环境中,Alert.log中都可能频繁出现以下错误。失败的查询始终针对GV$SQL_MONITOR执行:
ORA-12850: Could not allocate slaves on all specified instances: 3 needed, 2 allocated
TIP: ORA-12850 M002 trace maybe show as the following, Note that the red font text matches the SQL above
ORA-12850: Could not allocate slaves on all specified instances: 2 needed, 0 allocated
Dump of memory from 0xC00000194F9726C8 to 0xC00000194F98840C
C00000194F9726C0 57495448 204D4F4E [WITH MON]
C00000194F9726D0 49544F52 5F444154 41204153 20285345 [ITOR_DATA AS (SE]
C00000194F9726E0 4C454354 20494E53 545F4944 2C204B45 [LECT INST_ID, KE]
C00000194F9726F0 592C204E 564C3228 50585F51 43534944 [Y, NVL2(PX_QCSID]
C00000194F972700 2C204E55 4C4C2C20 53544154 55532920 [, NULL, STATUS) ]
C00000194F972710 53544154 55532C20 46495253 545F5245 [STATUS, FIRST_RE]
C00000194F972720 46524553 485F5449 4D452C20 4C415354 [FRESH_TIME, LAST]
解决方案:
参考下面提供的解决方案:
ORA-12850 is Raised by Mmon Slave Automatic Report Flush Action (文档 ID 2217969.1)
High CPU Usage and/or Frequent Occurrences of ORA-12850 For Monitor Queries by MMON From 12.1 (文档 ID 2102131.1)
bug:24554937 ORA-12850 WHILE MMON SLAVE AUTOMATIC REPORT FLUSH ACTION
禁止这个特性即可:
alter system set "_report_capture_cycle_time"=0; /* Default is 60 seconds */
阅读(3589) | 评论(0) | 转发(0) |