Things To Know About PGA_AGGREGATE_LIMIT

This week I got a great surprise from one of my readers, Kaley. When I got my mail yesterday, there was a package with my name on it, and inside the package was his book:

Why is this so special? Well, first of all, this book is out of print, it is limited edition, and both of us thought it got lost in the mail. It actually took 4 months to reach the destination from USA to Canada…but you know what? It arrived! Thank you Kaley, I am looking forward to reading it! If you ever wonder what Kaley is up to, you can check out his blog: https://blog.tuningsql.com/

Now, let’s get back to the PGA_AGGREGATE_LIMIT. If you are running 12c and up, or you just upgraded your database recently, you will notice a new parameter and possibly some new warnings in the alert log, similar to this: ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT. You would receive these alerts if the PGA_AGGREGATE_LIMIT exceeds the set value.

So what is PGA_AGGREGATE_LIMIT?

Starting with 9i, Oracle introduced the automatic management of the PGA, and with it, it introduced the PGA_AGGREGATE_TARGET parameter.
The PGA_AGGREGATE_TARGET provided a soft limit on how much PGA the instance can use, however if more PGA was needed, Oracle was able to allocate more memory.

PGA_AGGREGATE_TARGET = soft limit => more memory can be allocated

Starting with 12c, Oracle introduced a new parameter PGA_AGGREGATE_LIMIT. This parameter provides a hard limit, meaning it cannot be exceeded. So, if PGA_AGGREGATE_LIMIT is set to 2GB,
and the 2GB is used up, then, no more memory can be used for the PGA, and the ORA-04036 error will be recorded in the alert log.

PGA_AGGREGATE_LIMIT = hard limit => no more memory can be allocated.

PGA_AGGREGATE_LIMIT is set by default, by Oracle. The default value is the greatest value of the following:

2GB or 3MBx PROCESSES parameter (or 5MBx PROCESSES parameter for RAC) or 2xPGA_AGGREGATE_TARGET

This parameter is dynamic, and it should not be set to a lower value than PGA_AGGREGATE_TARGET parameter. If you set it to 0, then no hard limit will be set for the PGA, meaning the functionality will work as pre 12c.

When you receive the ORA-04036 error often, it is a good sign that the PGA_AGGREGATE_LIMIT should be increased to a higher value. If you are wondering why pre-12c you did not see similar errors in the alert log, exceeding the PGA, this is because there was no hard limit on the PGA. More memory was just allocated to the PGA, as needed.

Here’s an exercise for you: PGA_AGGREGATE_TARGET =1GB, PROCESSES=1000. How much is by default the PGA_AGGREGATE_LIMIT in a non-RAC database? Let me know in the comments section, I read every comment!

If you enjoyed this article, and would like to learn more about databases, please sign up to my weekly email, and you will receive my FREE guide: 7 Questions to Ask When Troubleshooting Database Performance Problems!

---

If you are interested in improving your Oracle Tuning skills, check out my course theultimatesqltuningformula.com. Follow the link to get the September Back To School Special, only $16.99 CAD!