ORACLE 12c 12.1.0.2.0 ORA-12850: Could not allocate slaves on all specified instances: 2 needed, 0 allocated
[toc]
DB version:oracle 12c 12.1.0.2.0
Errors in file /u01/app/oracle/diag/rdbms/tos/LOCDB1/trace/LOCDB1_m001_28575.trc: ORA-12850: Could not allocate slaves on all specified instances: 2 needed, 0 allocated Tue Jan 16 06:12:07 2018
High CPU Usage and/or Frequent Occurrences of ORA-12850 For Monitor Queries by MMON From 12.1 (Doc ID 2102131.1) In this Document Symptoms Changes Cause Solution References APPLIES TO: Oracle Database - Enterprise Edition - Version 12.1.0.1 and later Information in this document applies to any platform. 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:
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: CHANGES Upgrading the database to 12.1. CAUSE
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 …;
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 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: Or 2. Kill MMON SLAVE from os. The sid and serial number can be obtained from ASH report. Document 2031605.1 Adaptive Query Optimization
SQL> alter system set “_report_capture_cycle_time”=0; /* Default is 60 seconds */
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 REFERENCES
下载pdf：Document 2102131.1