My Oracle Support Banner

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 later
Information 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


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.