We have recently upgraded our database from 11g to 12c. And when one of the developer ran some job, the job got killed and got the below error in log. These jobs were running fine in 11g without any issues.

ORA-00028: your session has been killed
ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT
PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT of 6000 MB

CAUSE:

Program Global Area (PGA) is an area in memory that is used by sessions for session related activities like sorting. in 11g PGA can grow without any restriction if the session demands. Usually untunable processes, consume lot of PGA, which can impact the overall memory usage of the database.
So in 12c, pga_aggregate_limit was introduced, which puts a cap on the pga growth.

As per Oracle documentation

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.

SOLUTION:

So first thing, you need to find , why the job is using lot of PGA. If that job can’be tuned, Then follow any of the below.

1. Set PGA_AGGREGATE_LIMIT to 0 , which removes the limit on PGA usage per session( like 11g) 

alter system set pga_aggregate_limit=0 scope=both;

2. Else increase the size of PGA_AGGREGATE_LIMIT to a higher value.

SEE ALSO: