19C+: FAST/EXCESSIVE PGA GROWTH when USING DBMS_STATS.GATHER_TABLE_STATS (Doc ID 2680355.1)

Last updated on JULY 08, 2020

Applies to:

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

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