12c New Feature: Limit the PGA


pgaPre 12c Database there was no hard limit for the PGA (except the OS ressources). The fact that the Program Global Area has no hard limit can lead to unstable system (if the system begins to page out). In a worst case a Real Application Cluster can make a node eviction.

12c Oracle has introduced a new Parameter PGA_AGGREGATE_LIMIT for controlling the maximum amount of PGA. The default limit of this Parameter is set to greatest value of these rules:

  • 2 GB Memory
  • 200% of PGA_AGGREGATE_TARGET
  • 3MB per process (Parameter)

The default value can be found in the alert.log

Thu Jun 27 22:20:35 2013
Using default pga_aggregate_limit of 2048 MB

If we now set the process Parameter to 1000, we can see that also the default value increase

Sat Jun 29 16:02:14 2013
Using default pga_aggregate_limit of 3000 MB

The value for the PGA_AGGREGATE_LIMIT can be set online in the database without a restart.

SQL> show parameter pga_agg

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_limit                  big integer 3000M
pga_aggregate_target                 big integer 0
SQL> alter system set pga_aggregate_limit=2000M scope=both;

System altered.

SQL> show parameter pga_agg

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_limit                  big integer 2000M
pga_aggregate_target                 big integer 0

Also the minimum value for the parameter is calculated based on your database memory Parameters. If you try to set it to low, you will  get a error like this.

SQL> alter system set pga_aggregate_limit=50M scope=both;
alter system set pga_aggregate_limit=50M scope=both
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-00093: pga_aggregate_limit must be between 1694M and 100000G

So what happen, when the limit is  reached? Simple answer. Oracle is aborting the session which has the most untunable PGA, if the PGA is still over the limit then this session are terminated. For demonstration i have created a small test function which is consuming PGA.

pga_sam_1 pga_sam_2The two screenshots above shows the session and the consuming PGA. You can see that the session is killed from the database automatically if the limit is reached and also the alert.log shows the comparable error.

Sat Jun 29 16:24:44 2013
Errors in file /u01/app/oracle/diag/rdbms/db1211/db1211/trace/db1211_ora_19650.trc  (incident=34591):
ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT
Incident details in: /u01/app/oracle/diag/rdbms/db1211/db1211/incident/incdir_34591/db1211_ora_19650_i34591.trc
Sat Jun 29 16:25:13 2013
PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT of 2048 MB

Conclusio:I think this is a importend new feature, because i had several databases in the past which had problem due a PGA overallocation from one session. Be carefull to set this parameter not to low (or to high), because if a important batch job is killed, it would maybe not so nice.

And as always comments are welcome

About these ads

2 Gedanken zu “12c New Feature: Limit the PGA

  1. Pingback: PL/SQL is faster than SQL – Just ask Mitch. | The Anti-Kyte

  2. Pingback: PL/SQL is faster than SQL – Just ask Mitch. - Oracle - Oracle - Toad World

Kommentar verfassen

Trage deine Daten unten ein oder klicke ein Icon um dich einzuloggen:

WordPress.com-Logo

Du kommentierst mit Deinem WordPress.com-Konto. Abmelden / Ändern )

Twitter-Bild

Du kommentierst mit Deinem Twitter-Konto. Abmelden / Ändern )

Facebook-Foto

Du kommentierst mit Deinem Facebook-Konto. Abmelden / Ändern )

Google+ photo

Du kommentierst mit Deinem Google+-Konto. Abmelden / Ändern )

Verbinde mit %s