ORA-04036: PGA memory used by the instance exceeds PGA AGGREGATE LIMIT

Leave a reply

Fix ORA-04036: PGA Memory Limit Errors in Oracle

PGA_AGGREGATE_LIMIT is used from Oracle 9i with automatic management of the PGA. It specify as SOFT LIMIT in Oracle 9i but from Oracle 12c it becomes the HARD LIMIT. In Oracle 9i it provide more memory if needed but it will get error in Oracle 12c if limit exceeds.

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

Check the current value of PGA:

SQL> show parameter pga
NAME                   TYPE         VALUE
---------------------- -----------  ------------------------------
pga_aggregate_limit    big integer  2G
pga_aggregate_target   big integer  512M

Solution

There are two solution for the problem:

First option: Disable the parameter for getting rid of this error ORA-04036

    ALTER SYSTEM SET pga_aggregate_limit=0 SCOPE=BOTH;

    Note: You can use old behavior as used in Oracle 9i by disable it to set the value ZERO with SOFT LIMIT. It will disable the error permanently.

    Second option: Increase the PGA_AGGREGATE_LIMIT

    If you face the error ORA-04036 then you will increase the value of PGA_AGGREGATE_LIMIT parameter. It is dynamic parameter you can increase the value.
    But you cannot decrease the value below the PGA_AGGREGATE_TARGET.

    -- Increase the value to 6G
ALTER SYSTEM SET PGA_AGGREGATE_LIMIT=6G SCOPE=BOTH;

    You can try another solution, For Stop or reduce the PGA Memory usage, you can disable the parameter instead of using upper provided solution and monitor the your database error or its performance also.

    
SQL> alter system set "_optimizer_adaptive_cursor_sharing" = FALSE scope=both;
SQL> alter system set "_optimizer_use_feedback" = FALSE scope=both;
SQL> alter system set "_optimizer_gather_feedback" = FALSE scope=both;

    Leave a Reply