DB version:oracle 12c 12.1.0.2.0 

1
2
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

APPLIES TO:

Oracle Database Backup Service - Version N/A and later
Oracle Database Cloud Exadata Service - Version N/A and later
Oracle Database Cloud Service - Version N/A and later
Oracle Database - Enterprise Edition - Version 12.1.0.1 and later
Oracle Database Exadata Express Cloud Service - Version N/A 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:

    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

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

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

下载pdf：Document 2102131.1

原文作者: liups.com

原文链接: http://liups.cn/posts/fce37b9b/

许可协议: 知识共享署名-非商业性使用 4.0 国际许可协议