19C+: FAST/EXCESSIVE PGA GROWTH when USING DBMS_STATS.GATHER_TABLE_STATS
(Doc ID 2680355.1)
Last updated on JULY 08, 2020
Applies to:
Oracle Database - Enterprise Edition - Version 19.6.0.0.0 and laterInformation in this document applies to any platform.
Symptoms
Attempting to view "Optimizer Statistics Gathering" scheduled task for a database instance in OEM Cloud Control 13.4 causes the database instance to crash.
In the alert log:
repeated entries for "Resize operation completed for file# 201..."
Followed by:
PGA_AGGREGATE_LIMIT has been exceeded but some processes using the most PGA
memory are not eligible to receive ORA-4036 interrupts. Further occurrences
of this condition will be written to the trace file of the DBRM process.
Step-by-step to recreate the problem on-demand:
In OEM:
1) Search for target
2) Click database instance
3) From "Administration" menu, select "Oracle Scheduler" and then "Home"
4) Log into database
5) Click "Optimizer Statistics Gathering"
This will set in motion the event(s) that will cause the database instance to crash within 30 minutes with ORA-4036 and ORA-4030 errors.
Cause
To view full details, sign in with your My Oracle Support account. |
|
Don't have a My Oracle Support account? Click to get started! |
In this Document
Symptoms |
Cause |
Solution |
References |