Como faço para lidar com problemas de pouca memória livre ou uso intenso de swap em minha instância de banco de dados do Amazon RDS para Oracle?

Descrição breve

Nos bancos de dados Oracle, a memória disponível é usada principalmente pela área global do programa (PGA) e pela área global do sistema (SGA). Ao configurar uma instância de banco de dados Oracle, certifique-se de que a soma desses dois componentes seja menor do que a memória física total para disponibilizar memória livre para outros processos e funcionalidades do host. Você deve ajustar os parâmetros de memória para evitar o uso elevado de swap e o uso de grandes quantidades de memória física. Para obter mais informações sobre o uso de swap e os limites recomendados, consulte Por que minha instância de banco de dados do Amazon RDS está usando memória de swap quando tenho memória suficiente?

Se sua instância de banco de dados usar o gerenciamento automático de memória, a quantidade total de memória usada pelos bancos de dados Oracle será gerenciada pelos parâmetros MEMORY_TARGET e MEMORY_MAX_TARGET. O valor definido em MEMORY_TARGET é igual à soma de SGA e PGA. A memória usada pela SGA é determinada pelos parâmetros SGA_MAX_SIZE e SGA_TARGET. A memória usada pela PGA é gerenciada pelos parâmetros PGA_AGGREGATE_TARGET e PGA_AGGREGATE_LIMIT. Os valores padrão para esses parâmetros são definidos como altos por motivos de performance. No entanto, em certos casos de uso, esses valores altos podem causar contenção de memória e alto uso de swap.

Resolução

Para solucionar esse problema, use um dos métodos a seguir com base no modo de gerenciamento de memória da instância.

• Se sua instância de banco de dados usar o gerenciamento automático de memória, diminua o valor de MEMORY_TARGET.
• Se sua instância de banco de dados usar o gerenciamento automático de memória compartilhada, diminua os valores dos parâmetros PGA e SGA na instância.

Observação: embora esses parâmetros sejam parâmetros dinâmicos, você deve reinicializar a instância sem failover para que esses parâmetros sejam modificados. Para obter mais informações, consulte a Nota em Como ativar o HugePages para uma instância do RDS para Oracle.

Experimente as seguintes etapas de solução de problemas:

1.    Verifique se sua instância de banco de dados usa gerenciamento automático de memória ou gerenciamento automático de memória compartilhada. Você pode fazer isso verificando os parâmetros SGA, PGA e MEMORY_TARGET:

SQL> SHOW PARAMETER SGA;

SQL> SHOW PARAMETER PGA;

SQL> SHOW PARAMETER MEMORY_TARGET;

• Se MEMORY_TARGET estiver definido, a instância usará o gerenciamento automático de memória.
• Se MEMORY_TARGET não estiver definido, mas SGA_TARGET e PGA_AGGREGATE_TARGET estiverem, a instância usará o gerenciamento automático de memória compartilhada.

Para obter mais informações sobre diferentes modos de gerenciamento de memória, consulte a documentação da Oracle sobre o gerenciamento de memória.

2.    Execute as seguintes consultas para visualizar o uso de memória por PGA e SGA, respectivamente:

SQL> SELECT (value/1024/1024) CURRENT_PGA_IN_USE_IN_MB FROM V$PGASTAT WHERE NAME ='total PGA inuse';

SQL> SELECT SUM (bytes/1024/1024) CURRENT_SGA_SIZE_IN_MB FROM V$SGASTAT;

Execute as seguintes consultas para visualizar o máximo de memória alocado para PGA e SGA, respectivamente, após a última reinicialização:

SQL> SELECT (value/1024/1024) MAX_PGA_ALLOCATED_IN_MB FROM V$PGASTAT WHERE NAME = 'maximum PGA allocated';

SQL> SELECT SUM (bytes/1024/1024) MAX_SGA_SIZE_IN_MB FROM V$SGAINFO WHERE NAME='Maximum SGA Size';

Execute as seguintes consultas para visualizar as exibições de consultoria para PGA e SGA, respectivamente:

SQL> SELECT PGA_TARGET_FOR_ESTIMATE,
PGA_TARGET_FACTOR, ADVICE_STATUS, ESTD_TIME, ESTD_PGA_CACHE_HIT_PERCENTAGE FROM
V$PGA_TARGET_ADVICE ORDER BY PGA_TARGET_FACTOR;

SQL> SELECT SGA_SIZE,
SGA_SIZE_FACTOR, ESTD_DB_TIME_FACTOR, ESTD_PHYSICAL_READS FROM
V$SGA_TARGET_ADVICE ORDER BY SGA_SIZE ASC;

3.    Defina as configurações apropriadas para os parâmetros de memória para evitar o consumo excessivo de memória e o alto uso de swap. Essas configurações também podem ajudá-lo a ajustar o banco de dados para uma melhor performance. Você pode alterar qualquer um dos parâmetros nesses exemplos usando o grupo de parâmetros da instância.

Se a instância usar o gerenciamento automático de memória, somente o parâmetro MEMORY_TARGET será definido. Esse parâmetro é definido com o seguinte valor, por padrão:

MEMORY_TARGET = MEMORY_MAX_TARGET = {DBInstanceClassMemory*3/4}

Se a instância usar o gerenciamento automático de memória compartilhada, os parâmetros PGA_AGGREGATE_TARGET e SGA_TARGET serão definidos com os seguintes valores padrão, respectivamente:

PGA_AGGREGATE_TARGET= {DBInstanceClassMemory*1/8}

SGA_TARGET= SGA_MAX_SIZE = {DBInstanceClassMemory*3/4}

Se o uso de memória com essas configurações padrão for alto, você poderá ajustar as configurações padrão para limitar a memória usada pela instância do Oracle. Você pode usar as exibições consultivas de memória anteriores para ajustar os parâmetros de memória. É uma prática recomendada ajustar os parâmetros de memória em um ambiente de teste antes de aplicar as alterações aos bancos de dados de produção.

Por exemplo:

Se a instância for de gerenciamento automático de memória, reduza apenas os valores MEMORY_TARGET e MEMORY_MAX_TARGET:

MEMORY_TARGET = MEMORY_MAX_TARGET = {DBInstanceClassMemory*1/2}

-ou-

MEMORY_TARGET = MEMORY_MAX_TARGET = {DBInstanceClassMemory*3/5}

-ou-

MEMORY_TARGET = MEMORY_MAX_TARGET = {DBInstanceClassMemory*2/3}

Se a instância estiver usando o gerenciamento automático de memória compartilhada, reduza apenas os parâmetros de memória de SGA e/ou PGA com base nos valores de recomendação mostrados na etapa anterior. Por exemplo:

SGA_TARGET = {DBInstanceClassMemory*1/2}

-ou-

SGA_TARGET = {DBInstanceClassMemory*2/3}

Você também pode ajustar o uso de memória da área de memória do programa definindo o seguinte valor para PGA_AGGREGATE_TARGET:

PGA_AGGREGATE_TARGET = {DBInstanceClassMemory*1/12}

4.    Em determinados casos de uso, o uso de memória pela PGA pode exceder o valor PGA_AGGREGATE_TARGET e atingir o valor definido no parâmetro PGA_AGGREGATE_LIMIT. O valor padrão de PGA_AGGREGATE_LIMIT depende da configuração de memória da instância do banco de dados. Para obter mais informações, consulte a documentação da Oracle para PGA_AGGREGATE_LIMIT. Se o valor padrão de PGA_AGGREGATE_LIMIT for muito alto, a instância do RDS pode ter a memória física muito baixa ou nenhuma memória para o host subjacente, causando um alto índice de uso de swap e problemas de contenção de memória.

Para solucionar esses casos de uso, localize quais processos ou consultas estão usando uma grande quantidade de PGA. Execute a seguinte consulta para exibir a alocação da área do programa por processo:

SQL> SELECT spid, program, round(pga_max_mem/1024/1024) max_MB, round(pga_alloc_mem/1024/1024)alloc_MB, round(pga_used_mem/1024/1024) used_MB, round(pga_freeable_mem/1024/1024) free_MB FROM V$PROCESS ORDER BY 3;

Execute a seguinte consulta para exibir o uso de memória pela PGA para sessões de usuário, excluindo processos em segundo plano:

SQL> SELECT sum(p.pga_max_mem)/1024/1024 "PGA MAX MEMORY OF USER SESSION (MB)" FROM v$process p, v$session s WHERE P.ADDR = S.paddr and s.username IS NOT NULL;

Execute a seguinte consulta para exibir o uso de memória por PGA por sessão e as consultas que são executadas por cada sessão:

SQL> SELECT s.username, p.pga_max_mem/1024/1024 "PGA MEMORY OF USER SESSION (MB)", q.sql_text FROM v$process p, v$session s, v$SQL q WHERE P.ADDR = S.paddr AND s.username IS NOT NULL AND s.sql_id = q.sql_id ORDER BY 2;

Para reduzir o alto uso de memória pela PGA, use um ou mais dos seguintes métodos:

• Limite o valor de PGA_AGGREGATE_LIMIT. Isso pode impedir que a instância fique sem memória. No entanto, isso pode causar falha nas conexões quando for necessária memória PGA extra para um processo.
• Reduza o valor SGA_TARGET adequadamente. Isso permite que PGA_AGGREGATE_TARGET aumente até o valor de PGA_AGGEGATE_LIMIT sem causar problemas de contenção de memória.
• Aumente o tamanho da classe da instância para um tamanho de instância maior com mais memória.

5.    Depois de ajustar os parâmetros de memória, conforme mencionado nas etapas anteriores, monitore a performance do banco de dados e consulte as exibições de consultoria do banco de dados. Se limitar os parâmetros de memória causar degradação na performance, tente aumentar o tamanho da instância. Isso permite mais uso de memória para que a instância do banco de dados funcione com eficiência. Você pode usar o painel Performance Insights para analisar a workload do banco de dados e identificar as consultas com o maior uso de memória. Em seguida, você pode ajustar essas consultas com o maior uso de memória para usar menos memória.

---

Informações relacionadas

Documentação da Oracle para arquitetura de memória