Parâmetro PGA_AGGREGATE_LIMIT: Como definir o limite máximo de memória do PGA no Oracle Database

Limit the PGA

De acordo com a documentação oficial da Oracle, o parâmetro PGA_AGGREGATE_LIMIT é setado como default para o maior valor entre:

• 2 GB (valor default)
• 200% do valor do parâmetro PGA_AGGREGATE_TARGET
• 3 MB multiplicado pelo valor do parâmetro PROCESSES

Este parâmetro deve ser menor que o seguinte cálculo:

• Menor que 1.2 X RAM – SGA

Devido a um problema de interpretação da documentação, alguns DBAs pensavam que o parâmetro pga_aggregate_target era o limite máximo de PGA que o banco iria utilizar, mas isso é incorreto.

Também existe o parâmetro _pga_max_size (parâmetro oculto) que podia ser usado para limitar a memória de um processo. Mas além de não limitar da forma esperada, não deve ser utilizado sem a ajuda do suporte da Oracle.

Antes de existir o parâmetro PGA_AGGREGATE_LIMIT , o banco de dados podia exceder a quantidade real de RAM, causando erros de paginação e até mesmo RAC node eviction.

O parâmetro PGA_AGGREGATE_LIMIT é dinâmico, com isso não é necessário reiniciar o banco de dados ao fazer uma alteração. 

Alguns detalhes importantes:

• A Oracle não recomendar setar esse valor abaixo do default
• Se o valor setado for 0, não existirá limite para utilização de PGA
• O Oracle Database tratará as queries paralelas como uma única unidade

Verificando o valor default:

SQL> show parameter pga

NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_limit             big integer     2G
pga_aggregate_target             big integer     100M

Alterando o valor do parâmetro processes, para verificar como fica o PGA_AGGREGATE_LIMIT: 

SQL> show parameter processes

NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
processes                 integer     300

SQL> alter system set processes=1000 scope=spfile;

System altered.

Após alteração do parâmetro processes é necessário reiniciar o banco de dados.

Verificando o 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

Tentando diminuir o valor do parâmetro PGA_AGGREGATE_LIMIT, para um valor menor que o permitido:

SQL> alter system set pga_aggregate_limit=100m;

alter system set pga_aggregate_limit=100m

*

ERROR at line 1:

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

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

Lembrando que é possível diminuir esse valor diretamente no PFILE ou SPFILE.

Ao executar um código que exceda o valor máximo permitido:

Mon 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

Trecho retirado da documentação:

Oracle Database aborts or terminates the sessions or processes that are consuming the most untunable PGA memory in the following order:

• Calls for sessions that 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 that are consuming the most untunable PGA memory are terminated.

Referências

• http://docs.oracle.com/cd/E16655_01/server.121/e17615/refrn10328.htm#REFRN10328

• http://docs.oracle.com/cd/E16655_01/server.121/e15857/tune_pga.htm#TGDBA95346

Abraço