High CPU Usage and/or Frequent Occurrences of ORA12850 or ORA12751 For Monitor Queries by MMON From 12.1

High CPU Usage and/or Frequent Occurrences of ORA-12850 or ORA-12751 For Monitor Queries by MMON From 12.1 (Doc ID 2102131.1)

• 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:

  Thu Sep 08 04:00:41 2016
  Errors in file /app/oracle/diag/rdbms/dbname/dbinstance/trace/dbinstance_m002_14490.trc:
  ORA-12850: Could not allocate slaves on all specified instances: 3 needed, 2 allocated

   

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

SOLUTION

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

SQL> alter system set "_report_capture_cycle_time"=0; /* 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.

(However in some newer versions of Oracle Database there have been situations where SQL Monitoring data only had 1 hour of data available at any time with parameter disabled. If this situation happens you can switch this parameter back on.)

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.

 NOTE: _report_capture_cycle_time=0 /* This is system modifiable with immediate */

TIP: There are some known bugs for the problem which are fixed in latest PSU of 12.1.0.2. Hence, it is strongly recommended to apply the latest PSU for 12.1.0.2.

Document 1924126.1 12.1.0.2 Patch Set Updates - List of Fixes in each PSU