WITH MONITOR_DATA AS SELECT INST_ID Performance issue in oracle 12c
The following statement causing lot of execution and consuming lot of CPU in my AWR report.
In Oracle 12c, new feature is introduced called as “Automatic Report Capturing Feature”It is related with optimizer_adaptive_plans
WITH MONITOR_DATA AS (SELECT INST_ID, KEY, NVL2(PX_QCSID, NULL, STATUS) STATUS, FIRST_REFRESH_TIME, LAST_REFRESH_TIME, REFRESH_COUNT
.....
DBMS_XPLAN.BUILD_PLAN_XML( TABLE_NAME=>'gv$sql_plan', PLAN_TAG=>'plan', FILTER_PREDS=>:B35 , FORMAT=>'-PROJECTION +ALIAS +ADAPTIVE')
ELSE NULL END XPLAN_XML FROM DUAL) V1) CONST_VIEW
If the CPU consumption is high then it is not an expected behavior, So we need to disable the monitoring of MMON_SLAVE.
Solution:
Disable the following parameter for stop monitoring the database:
Default setting is 60 seconds
alter system set "_report_capture_cycle_time"=0 scope=both;