12c - Limit the PGA

According to the official Oracle documentation, the parameter PGA_AGGREGATE_LIMIT is set by default to the greater of:

• 2 GB (default value)
• 200% of the value of the parameter PGA_AGGREGATE_TARGET
• 3 MB multiplied by the value of the parameter PROCESSES

This parameter must be less than the following calculation:

• Less than 1.2 X RAM - EMS

Some important details:

• Oracle does not recommend set this value below the default
• If the settled value is 0, there is no limit for use of PGA
• Oracle Database will handle parallel queries as a single unit

Checking the default value:

SQL> show parameter pga

NAME                  TYPE       VALUE
------------------------------------ ------ 

pga_aggregate_limit  big integer   2G
pga_aggregate_target big integer  100M

Changing the parameter value processes , to see how is the PGA_AGGREGATE_LIMIT : 

SQL> show parameter processes

NAME TYPE VALUE 

------------------------------ -----
processes integer 300


SQL> alter system set processes scope = spfile = 1000;
System altered.

After changing the parameter processes to restart the database.

Checking alert.log :

Mon Jan 27 16:25:17 2014
Using default pga_aggregate_limit of 3000 MB

SQL> show parameter pga_aggregate_limit

NAME TYPE VALUE
------------------------------------ ------ ------------------------------ -----
pga_aggregate_limit big integer 3G

Trying to decrease the parameter value PGA_AGGREGATE_LIMIT , to one less than the allowed value:

SQL> alter system set pga_aggregate_limit = 100m;

alter system set pga_aggregate_limit = 100m

*

ERROR at line 1:

ORA-02097: parameter can not be modified because specified value is invalid

ORA-00093: pga_aggregate_limit must be between 2048M and 100000G

Remembering that it is possible to reduce this value directly in the PFILE or SPFILE .

When running code that exceeds the maximum allowed value:

Thu Jan 27 16:25:17 2014
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_23340.trc (= incident 12901):
ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT

taken from the documentation excerpt:

Oracle Database aborts or terminates the sessions or processes are consuming the most que untunable PGA memory in The Following order:

• Calls for que sessions are consuming the most untunable PGA memory are aborted.
• If PGA memory usage is still over the PGA_AGGREGATE_LIMIT , Then the sessions and processes are consuming the most That untunable PGA memory are terminated.