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”
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
It is related with optimizer_adaptive_plans
If the CPU consumption is high then it is not an expected behaviour, So we need to disable the monitoring of MMON_SLAVE.
Disable the following parameter for stop monitoring the database:
Default setting is 60 seconds
alter system set "_report_capture_cycle_time"=0 scope=both;