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)

Last updated on JULY 01, 2024

Applies to:

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:

  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.
  
   

  
  
  

Changes

Upgrading the database to 12.1.

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.