ANBOB™

if you upgrading to Oracle 12c, sometimes seen high CPU use in the MMON_SLAVE modules.These are associated with Oracle new Automatic Report Capturing functionality.

Oracle 11g is introduced real-time SQL monitoring , for real-time monitoring of the performance of SQL in the implementation;
Oracle 12c to further expand its introduction of the historical SQL monitoring function. This function is similar to the history management of the ASH information through the background process MMON_SLAVE on a regular basis to SQL monitoring information stored in the database table.

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.

MMON consumes little more CPU due to the monitoring activity in 12.1, 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.

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 occurrence of the following error 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

TIP: ORA-12850 M002 trace maybe show as the following, Note that the red font text matches the SQL above

*** 2017-03-05 06:26:48.818
*** SESSION ID:(1201.57619) 2017-03-05 06:26:48.818
*** CLIENT ID:() 2017-03-05 06:26:48.818
*** SERVICE NAME:(SYS$BACKGROUND) 2017-03-05 06:26:48.818
*** MODULE NAME:(MMON_SLAVE) 2017-03-05 06:26:48.818
*** CLIENT DRIVER:() 2017-03-05 06:26:48.818
*** ACTION NAME:(Automatic Report Flush) 2017-03-05 06:26:48.818
*** CONTAINER ID:(1) 2017-03-05 06:26:48.818
 
ORA-12850: Could not allocate slaves on all specified instances: 2 needed, 0 allocated
Dump of memory from 0xC00000194F9726C8 to 0xC00000194F98840C
C00000194F9726C0                   57495448 204D4F4E          [WITH MON]
C00000194F9726D0 49544F52 5F444154 41204153 20285345  [ITOR_DATA AS (SE]
C00000194F9726E0 4C454354 20494E53 545F4944 2C204B45  [LECT INST_ID, KE]
C00000194F9726F0 592C204E 564C3228 50585F51 43534944  [Y, NVL2(PX_QCSID]
C00000194F972700 2C204E55 4C4C2C20 53544154 55532920  [, NULL, STATUS) ]
C00000194F972710 53544154 55532C20 46495253 545F5245  [STATUS, FIRST_RE]
C00000194F972720 46524553 485F5449 4D452C20 4C415354  [FRESH_TIME, LAST]
C00000194F972730 5F524546 52455348 5F54494D 452C2052  [_REFRESH_TIME, R]
C00000194F972740 45465245 53485F43 4F554E54 2C205052  [EFRESH_COUNT, PR]
C00000194F972750 4F434553 535F4E41 4D452C20 5349442C  [OCESS_NAME, SID,]
C00000194F972760 2053514C 5F49442C 2053514C 5F455845  [ SQL_ID, SQL_EXE]
C00000194F972770 435F5354 4152542C 2053514C 5F455845  [C_START, SQL_EXE]
C00000194F972780 435F4944 2C204442 4F505F4E 414D452C  [C_ID, DBOP_NAME,]
C00000194F972790 2044424F 505F4558 45435F49 442C2053  [ DBOP_EXEC_ID, S]
C00000194F9727A0 514C5F50 4C414E5F 48415348 5F56414C  [QL_PLAN_HASH_VAL]
C00000194F9727B0 55452C20 53514C5F 46554C4C 5F504C41  [UE, SQL_FULL_PLA]
C00000194F9727C0 4E5F4841 53485F56 414C5545 2C205345  [N_HASH_VALUE, SE]
C00000194F9727D0 5353494F 4E5F5345 5249414C 232C2053  [SSION_SERIAL#, S]
C00000194F9727E0 514C5F54 4558542C 2049535F 46554C4C  [QL_TEXT, IS_FULL]
C00000194F9727F0 5F53514C 54455854 2C205058 5F534552  [_SQLTEXT, PX_SER]
C00000194F972800 56455223 2C205058 5F534552 5645525F  [VER#, PX_SERVER_]
C00000194F972810 47524F55 502C2050 585F5345 52564552  [GROUP, PX_SERVER]
C00000194F972820 5F534554 2C205058 5F514349 4E53545F  [_SET, PX_QCINST_]
C00000194F972830 49442C20 50585F51 43534944 2C204341  [ID, PX_QCSID, CA]
C00000194F972840 53452057 48454E20 454C4150 5345445F  [SE WHEN ELAPSED_]
C00000194F972850 54494D45 203C2028 4350555F 54494D45  [TIME < (CPU_TIME]
C00000194F972860 2B204150 504C4943 4154494F 4E5F5741  [+ APPLICATION_WA]
C00000194F972870 49545F54 494D452B 20434F4E 43555252  [IT_TIME+ CONCURR]
C00000194F972880 454E4359 5F574149 545F5449 4D452B20  [ENCY_WAIT_TIME+ ]

# to enable trace the MMON_SLAVE function behavior,

begin
dbms_monitor.serv_mod_act_trace_enable
(service_name=>'SYS$BACKGROUND',
module_name=>'MMON_SLAVE',
action_name=>'Automatic Report Flush',
waits => true,
binds => true);
end;
/

TIP:
The service_name in the argument corresponds to the service_name of the v$ session view,
the module_name corresponds to the module of the v$session view,
and the action_name corresponds to the action of the v$session view. The query is as follows:

SELECT sid, serial#,
client_identifier, service_name, action, module
FROM V$SESSION

# to verify the diag can be query dba_enabled_traces after enabe trace above;

select primary_id    as service_name,
qualifier_id1 as module_name,
qualifier_id2  as action_name,
waits,
binds
from dba_enabled_traces
where trace_type = 'SERVICE_MODULE_ACTION'

# trace file will show below sql

SELECT sql_id,
sql_exec_id,
dbop_name,
dbop_exec_id,
To_char(sql_exec_start, 'mm:dd:yyyy hh24:mi:ss'),
To_char(first_refresh_time, 'mm:dd:yyyy    hh24:mi:ss'),
To_char(last_refresh_time, 'mm:dd:yyyy hh24:mi:ss'),
elapsed_time,
px_servers_allocated,
sid,
session_serial#,
KEY,
con_id
FROM   v$sql_monitor
WHERE  report_id = 0
AND status != 'EXECUTING'
AND status != 'QUEUED'
AND px_qcsid IS NULL
AND last_refresh_time >
(SELECT Nvl(last_cycle_time, sysdate - ( 5 / 1440 ))
FROM v$sys_report_stats);

#Also show call sys.dbms_auto_report_internal.i_save_report to save sql monitor to table , Later you can query the relevant view  DBA_HIST_REPORTS and DBA_HIST_REPORTS_DETAILS

# To disable MMON slave tracing:

begin
dbms_monitor.serv_mod_act_trace_disable(service_name=>'SYS$BACKGROUND',
module_name=>'MMON_SLAVE',
action_name=>'Automatic Report Flush');
end;
/

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

SQL> alter system set "_report_capture_cycle_time"=0; /* Default is 60 seconds */

references  MOS note 2102131.1  and mojijs’s note

打赏

微信扫一扫，打赏作者吧～