Oracle PGA limit - PGA_AGGREGATE_LIMIT

book

Article ID: 211593

calendar_today

Updated On:

Products

CA Automic One Automation

Issue/Introduction

After upgrade to 12.3.x the PGA memory increases until the defined limit, after that, parts of the PGA memory is been allocated, as soon as the PGA memory is on its limit, tasks within workflows will stay in status "generating" also releasing memory in PGA doesn't change the fact, the tasks remain in status "generating" and have to be cancelled manually and restarted.

The exceeding of the PGA limit leads to error messages in the WP logs like 

U00003590 UCUDB - DB error: 'OCIStmtExecute', 'ERROR   ', '', 'ORA-00039: error during periodic action
ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT'
...

U00003536 UCUDB: FATAL DATA BASE ERROR: Re-connection will be attempted in 10 seconds.
U00003538 UCUDB: Re-connection to database successful. Processing will continue.

 

Cause

In Oracle Database 12c, a new parameter called PGA_AGGREGATE_LIMIT sets a hard limit on the amount of PGA taken up by an instance.

When the overall PGA occupied by all the sessions in that instance exceed the limit, Oracle kills the session holding the most untunable PGA memory, releasing all PGA memory held by that session.

That killed session gets this message.

Environment

Release : 12.x 

Oracle 12c

Resolution

Resolution:  Increase the limit or remove the limit

Additional Information

PGA memory is fully managed by the Oracle DB Server Process.

You can use following SQL statement to find actual value of PGA_AGGREGATE_LIMIT :

show parameter pga;