Oracle 12.1 m00*进程消耗大量CPU问题

开发反映业务特别慢，几乎不能连上。登录服务器发现确实非常慢，但是数据库能连上。

top发现总CPU消耗不高但部分进程CPU消耗非常高，其中大部分是ora_m00x进程。而在CPU下降后，卡顿现象消失。

 

获取awr报告，发现消耗最高是以下sql

搜索MOS发现与文档High CPU Usage and/or Frequent Occurrences of ORA-12850 For Monitor Queries by MMON From 12.1 (文档 ID 2102131.1)描述相近，怀疑是其所描述的新特性导致，但由于故障已自动恢复且未再复现，没测试过是否真有效。

 

SYMPTOMS

• MMON consumes more CPU due to the monitoring activity in 12.1
• High CPU consumption and time spent while executing monitor-related queries from both MMON_SLAVE like following:

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...;

• The frequent occurrences of ORA-12850 may be present in the Alert.log in both a RAC and NON-RAC environment. The failing queries are consistently executed against GV$SQL_MONITOR.

• Occurrences of ORA-12751 may be seen when parallelism is not used.

 

CAUSE

There is a new feature in 12C called "Automatic Report Capturing Feature". As part of this feature, some monitoring SQLs are executed by MMON_SLAVE to identify the resource-intensive SQLs and generate the SQL Monitoring report automatically for those SQLs. Those SQLs consume little more CPU and it is expected behavior being a new feature. Such monitoring queries can be identified from (G)V$SQLSTATS.

However, If the CPU consumption is significantly high then it is not an expected behavior and could be due to optimizer choosing suboptimal plan for the SQL statements.This can happen due to Adaptive Optimization, a new feature in 12c.

Document 2031605.1 Adaptive Query Optimization

 

SOLUTION

1.  The new feature can be disabled to reduce the CPU consumption:

alter system set "_report_capture_cycle_time"=0; /* This is system modifiable with immediate.Default is 60 seconds */

There is no negative impact in setting the above parameter as it disables only the automatic report capturing feature introduced in 12c. It does not disable the original SQL monitoring framework. SQL monitoring can be used very well without any issues.

Or

2. Kill MMON SLAVE from os.  

The sid and serial number can be obtained from ASH report. If there are multiple MMON slaves, kill all slaves.