ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT

Products

• iDash Workload Automation

Issue/Introduction

After upgrading the version of iDash from 12.0.01 to 12.0.04, following error message is seen in the info.log file:

17-09-26 06.10.26 [ERROR] idashdb-schd-update-thread-1:com.ca.idash.core.averageruntime.service.AverageRuntimeUpdater: update average runtime failed 

com.ca.idash.core.exception.IdashException: java.sql.SQLException:ORA-00039: error during periodic action 

ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT 

at com.ca.idash.core.db.idashdb.IdashDbAverageRuntimeUtil.updateJobRunsAvgRunTime(IdashDbAverageRuntimeUtil.java:635)

Environment

iDash 12.x using Oracle.

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.

Resolution

Increase PGA_AGGREGIATE_LIMIT

Additional Information

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

show parameter pga;