One of our 12c database hitting this message at alertlog

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

PGA_AGGREGATE_LIMIT parameter comes with 12c, it sets a hard limit on the amount of PGA taken up by an instance.

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.

If you are hitting this error message you have 2 options

Option 1:

Set the value of pga_aggregate_limit to 0 and the parameter will not have any impact.It behave like pre12c database

Option 2:

Set this value to a much higher value if you have enough physical memory on your system by using below command the server

alter system set pga_aggregate_limit=2048M scope=both;  << no need bounce db

Reklam