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;

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.