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

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

Some time we face this error when we run batch jobs with very high degree of parallelism.  Oracle is  unable to use more PGA and it kills the session and the batch job fails. This can happen even though you assign very high  PGA_AGGREGATE_LIMIT.

Batch job sessions failed with below errors.

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 2048 MB

To Avoid this error: Select correct degree of parallelism

Selecting degree of parallelism is very much important, number of parallel sessions depends on server CPU core.

Degree of parallelism can be calculated with below equation.

For a single instance, DOP = PARALLEL_THREADS_PER_CPU x CPU_COUNT.
For an Oracle RAC configuration, DOP = PARALLEL_THREADS_PER_CPU x CPU_COUNT x INSTANCE_COUNT

Running more that 16 parallel session in 8 core CPU server is not a good idea.

You may have a look at this oracle document for more details.

https://docs.oracle.com/cd/E11882_01/server.112/e25523/parallel002.htm

Check the degree of parallelism is in recommended value. If yes check the  parameter PGA_AGGREGATE_LIMIT is set for small value. You may increase it according to degree of parallelism.

Still facing the issue and if your database is 12c, you may revert this parameter to pre12c by setting pga_aggregate_limit to 0.

alter system set pga_aggregate_limit=0 scope=both;

Setting the higher value for pga_aggregate_limit

alter system set pga_aggregate_limit=<Value> scope=both;

This parameter change not required instance restart.

Hope this helps You.