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;
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.