Oracle pga狂飙

最近有一套系统，内存被一个监控语句占了好多，大概就是这样的 “ WITH MONITOR_DATA AS ”，查了一下mos，发现一片文章跟我的症结很像，大家也可以参考参考

	High PGA Consumption By Mmon Slaves In The Server (Doc ID 2700130.1)	To BottomTo Bottom	

In this Document
Symptoms
Changes
Cause
Solution
References

APPLIES TO:
Oracle Database - Enterprise Edition - Version 18.7.0.0.0 and later
Information in this document applies to any platform.
SYMPTOMS
On : 18.7.0.0.0 version, RDBMS
 

-- High PGA consumption by Mmon slaves in the server
-- Issue is during (KDILM background EXEcution)
CHANGES
 

CAUSE
Unpublished Bug 31425761 : HIGH PGA CONSUMPTION/MEMORY LEAK BY MMON SLAVE
 

The issue is seen with background cleanup .

There is a new feature in 12C called "Automatic Report Capturing Feature". 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.


 

SOLUTION
The new feature "Automatic Report Capturing Feature" can be disabled to reduce the MEMORY/CPU consumption:


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



NOTE: _report_capture_cycle_time=0 /* This is system modifiable with immediate */

There is no negative impact in setting the above parameter as it disables only the automatic report capturing feature introduced in 12c. It does not disable the original SQL monitoring framework. SQL monitoring can be used very well without any issues. However, it may introduce the symptoms from Note 2352222.1.

 

REFERENCES

NOTE:1594347.1 - RAC and DB Support Tools Bundle

NOTE:2641033.1 - Standard Mmon Worker Process Using High PGA