WITH MONITOR_DATA AS SELECT INST_ID, KEY, NVL2(PX_QCSID query caused Performance issue Oracle 12c

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 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;

Leave a Reply