PGA_AGGREGATE_LIMIT enforces default since Oracle Database 12.2.0.1

The init.ora/spfile parameter PGA_AGGREGATE_LIMIT got introduced in Oracle Database 12.1.0.1.

As per documentation in Oracle Database 12.1 it got defined as:

PGA_AGGREGATE_LIMIT specifies a limit on the aggregate PGA memory consumed by the instance.“.

Furthermore the algorithm for its setting got described as:

By default, PGA_AGGREGATE_LIMIT is set to the greater of 2 GB, 200% of PGA_AGGREGATE_TARGET, and 3 MB times the PROCESSES parameter. It will be set below 200% of PGA_AGGREGATE_TARGET if it is larger than 90% of the physical memory size minus the total SGA size, but not below 100% of PGA_AGGREGATE_TARGET.”

Default Value Change in Oracle Database 12.2.0.1

In Oracle Database 12.2.0.1 the default value gets adjusted a bit as it turned out that restricting PGA_AGGREGATE_LIMIT to a value too low will lead to a significant number of issues.

See the Oracle Database 12.2.0.1 documentation on PGA_AGGREGATE_LIMIT explaining:

“If MEMORY_TARGET is set, then PGA_AGGREGATE_LIMIT defaults to the MEMORY_MAX_TARGET value.
If MEMORY_TARGET is not set, then PGA_AGGREGATE_LIMIT defaults to 200% of PGA_AGGREGATE_TARGET.
If MEMORY_TARGET is not set, and PGA_AGGREGATE_TARGET is explicitly set to 0, then the value of PGA_AGGREGATE_LIMIT is set to 90% of the physical memory size minus the total SGA size.
In all cases, the default PGA_AGGREGATE_LIMIT is at least 2GB and at least 3MB times the PROCESSES parameter.”

And please remember, DO NOT use MEMORY_TARGET as it will lead to issues such as no use of huge pages etc.

What happened in Oracle Database 12.1.0.2 when you set PGA_AGGREGATE_LIMIT too low?

How much is too low? Too low means it has been set lower that 2x the value of PGA_AGGREGATE_TARGET. And then it got adjusted “silently”, i.e. the value of PGA_AGGREGATE_LIMIT got adjusted internally after startup to reflect the minimum. Unfortunately this adjusted value does not get displayed by “show parameter”.

Short example in Oracle Database 12.1.0.2:

SQL> show parameter pga 

NAME			TYPE                      VALUE
----------------------- ------------------------- -----------------------------
pga_aggregate_limit     big integer               2G
pga_aggregate_target    big integer               120M
SQL> alter system set pga_aggregate_limit=1G scope=spfile;
System altered.

SQL> alter system set pga_aggregate_target=1G scope=spfile;
System altered.

After restarting the database:

SQL> show parameter pga

NAME				     TYPE	  VALUE
------------------------------------ ------------ ------------------------------
pga_aggregate_limit		     big integer  1G
pga_aggregate_target		     big integer  1G

Different behavior in Oracle Database 12.2.0.1

Repeating the same test in Oracle Database 12.2.0.1 leads to ORA-93:

SQL> show parameter pga 

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_limit		     big integer 2G
pga_aggregate_target		     big integer 120M
SQL> alter system set pga_aggregate_limit=1G scope=spfile;
System altered.

SQL> alter system set pga_aggregate_target=1G scope=spfile;
System altered.

SQL> create pfile from spfile;
File created.

SQL> startup force
ORA-00093: pga_aggregate_limit must be between 2048M and 100000G
ORA-01078: failure in processing system parameters

After increasing PGA_AGGREGATE_LIMIT=2G I can startup my database again. PGA_AGGREGATE_LIMIT gets enforced as at least 2x the value of PGA_AGGREGATE_TARGET since Oracle Database 12.2.0.1.

–Mike

5 thoughts on “PGA_AGGREGATE_LIMIT enforces default since Oracle Database 12.2.0.1

    • Stefan,

      are you sure? Frits examinations apply to 12.1 – the behavior has changed in 12.2. I’m not saying that it’s better or worse – but feel free to explain a bit more why you see SESSION_PGA_LIMIT is the by far better option 🙂

      Cheers
      Mike

      • Hey Mike,
        i have tested it in 12.2 as well – same behavior with pga_aggregate_limit (just referenced to Frits’s blog post for the test case and explanation of the issue). SESSION_PGA_LIMIT is the by far better option because it really enforces the limit – stops exactly at the set value. 🙂

        Regards
        Stefan

  1. Mike,

    Can you say anything more about “DO NOT use MEMORY_TARGET as it will lead to issues such as no use of huge pages etc.”? We spin up VMs for small departmental applications which don’t get a lot of hands-on configuration or management, and we’re not concerned about the performance penalty from not using huge pages. Is there anything else to be concerned about?

    For example, we have a 12.2 database running on a VM with 2 GB RAM. Using MEMORY_TARGET (AMM), I see the following at startup:
    ALTER DATABASE MOUNT
    2018-05-01T11:32:02.329404-04:00
    WARNING: pga_aggregate_limit value is too high for the
    amount of physical memory on the system
    PGA_AGGREGATE_LIMIT is 2048 MB
    PGA_AGGREGATE_TARGET is 0 MB.
    physical memory size is 1839 MB
    limit based on physical memory and SGA usage is 1655 MB
    MEMORY_MAX_TARGET is 1280 MB
    Using default pga_aggregate_limit of 2048 MB

    Should I be concerned about this? (The value of pga_aggregate_limit is the Oracle default, not something I’ve set.)

    Thanks very much.

    Dave

    • Dave,

      first of all, memory_Target leads also to a different way on how memory is allocated. It makes it much harder for support to diagnose issues such as leaks. And the different mem allocation way is not as good as the “normal” one. I think Frits Hoogland from Enkitec has demonstrated this a while ago – but I may mix up things. Basically I don’t see ZERO benefit in using it but just things which I don’t like. You don’t have to worry – the limit won’t affect you as far as my understanding goes (or only in theory).

      Cheers,
      Mike

Leave a Reply

Your email address will not be published. Required fields are marked *

* Checkbox to comply with GDPR is required

*

I agree

This site uses Akismet to reduce spam. Learn how your comment data is processed.