如何处理 Amazon RDS for Oracle 数据库实例中可用内存低或交换使用率高的问题？

简短描述

在 Oracle 数据库中，可用内存主要供计划全球区 (PGA) 和系统全球区域 (SGA) 使用。配置 Oracle 数据库实例时，请确保这两个组件的总和小于物理内存总量，使可用内存可用于其他主机进程和功能。必须调整内存参数，以避免高交换使用率和大量物理内存的使用。有关交换使用率和建议限制的更多信息，请参阅在内存足够时，为什么我的 Amazon RDS 数据库实例会使用交换内存？

如果数据库实例使用自动内存管理，Oracle 数据库使用的内存总量由参数 MEMORY_TARGET 和 MEMORY_MAX_TARGET 管理。在 MEMORY_TARGET 中设置的值等于 SGA 和 PGA 的总和。SGA 使用的内存由参数 SGA_MAX_SIZE 和 SGA_TARGET 决定。 PGA 使用的内存由参数 PGA_AGGREGATE_TARGET 和 PGA_AGGREGATE_LIMIT管理。出于性能原因，这些参数的默认值设置得较高。但在某些使用案例中，这些较高的值可能会导致内存争用和高交换使用率。

解决方法

要解决这个问题，可根据实例的内存管理模式采用以下其中一种方法。

• 如果数据库实例使用自动内存管理，则降低 MEMORY_TARGET 的值。
• 如果数据库实例使用自动共享内存管理，则降低实例中 PGA 和 SGA 参数的值。

注意：这些参数是动态参数，但您必须在不进行失效转移的情况下重启实例才能修改这些参数。有关更多信息，请参阅为 RDS for Oracle 实例启用 HugePages 中的注释。

请尝试以下问题排查步骤：

1.    检查数据库实例使用自动内存管理还是自动共享内存管理。您可以通过检查参数 SGA、PGA 和 MEMORY_TARGET 来做到这一点：

SQL> SHOW PARAMETER SGA;

SQL> SHOW PARAMETER PGA;

SQL> SHOW PARAMETER MEMORY_TARGET;

• 如果设置了 MEMORY_TARGET，则实例使用自动内存管理。
• 如果未设置 MEMORY_TARGET，但设置了 SGA_TARGET 和 PGA_AGGREGATE_TARGET，则实例使用自动共享内存管理。

有关不同内存管理模式的详细信息，请参阅关于内存管理的 Oracle 文档。

2.    运行以下查询，以分别查看 PGA 和 SGA 的内存使用情况：

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;

运行以下查询，以分别查看上次重启后为 PGA 和 SGA 分配的最大内存：

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';

运行以下查询，以分别查看 PGA 和 SGA 的建议视图：

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.    为内存参数定义适当的设置，以避免过多的内存消耗和高交换使用率。这些设置还可以帮助您调整数据库，以实现更好的性能。您可以使用实例参数组更改这些示例中的任何参数。

如果实例使用自动内存管理，则仅设置 MEMORY_TARGET 参数。默认情况下，此参数设置为以下值：

MEMORY_TARGET = MEMORY_MAX_TARGET = {DBInstanceClassMemory*3/4}

如果实例使用自动共享内存管理，则 PGA_AGGREGATE_TARGET 和 SGA_TARGET 参数将分别设置为以下默认值：

PGA_AGGREGATE_TARGET= {DBInstanceClassMemory*1/8}

SGA_TARGET= SGA_MAX_SIZE = {DBInstanceClassMemory*3/4}

如果使用这些默认设置的内存使用率很高，可以调整默认设置，以限制 Oracle 实例使用的内存。您可以使用上面的内存建议视图来调整内存参数。在将更改应用到生产数据库之前，最佳做法是在测试环境中调整内存参数。

例如：

如果实例使用自动内存管理，只需降低 MEMORY_TARGET 和 MEMORY_MAX_TARGET 的值：

MEMORY_TARGET = MEMORY_MAX_TARGET = {DBInstanceClassMemory*1/2}

– 或 –

MEMORY_TARGET = MEMORY_MAX_TARGET = {DBInstanceClassMemory*3/5}

– 或 –

MEMORY_TARGET = MEMORY_MAX_TARGET = {DBInstanceClassMemory*2/3}

如果实例使用自动共享内存管理，则根据上一步显示的建议值降低 SGA 和/或 PGA 的内存参数。例如：

SGA_TARGET = {DBInstanceClassMemory*1/2}

– 或 –

SGA_TARGET = {DBInstanceClassMemory*2/3}

您还可以通过为 PGA_AGGREGATE_TARGET 设置以下值来调整程序内存区域的内存使用情况：

PGA_AGGREGATE_TARGET = {DBInstanceClassMemory*1/12}

4.    在某些使用案例中，PGA 的内存使用量可能会超过 PGA_AGGREGATE_TARGET 的值，最高可达为 PGA_AGGREGATE_LIMIT 参数设置的值。PGA_AGGREGATE_LIMIT 的默认值取决于数据库实例的内存配置。有关更多信息，请参阅有关 PGA_AGGREGATE_LIMIT 的 Oracle 文档。如果 PGA_AGGREGATE_LIMIT 的默认值非常高，则 RDS 实例底层主机的物理内存可能非常少或没有物理内存，从而导致高交换使用率和内存争用问题。

要对这些使用案例进行故障排查，应找出哪些进程或查询正在使用大量 PGA。运行以下查询，以查看每个进程的程序区分配情况：

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;

运行以下查询，以查看 PGA 关于用户会话的内存使用情况，不包括后台进程：

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;

运行以下查询，以查看 PGA 在每个会话中的内存使用情况以及每个会话运行的查询：

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;

要降低 PGA 的高内存使用率，可采用以下一种或多种方法：

• 限制 PGA_AGGREGATE_LIMIT 的值。这可能可以防止实例内存不足。但是，当进程需要额外的 PGA 内存时，可能会导致连接故障。
• 适当降低 SGA_TARGET 的值。这使 PGA_AGGREGATE_TARGET 能够最多增加到 PGA_AGGEGATE_LIMIT 的值，而不会导致内存争用问题。
• 增加实例类大小，使其拥有更多的内存。

5.    按上述步骤调整内存参数之后，监控数据库性能并参阅数据库建议视图。如果限制内存参数导致性能下降，则尝试增加实例大小。这使数据库实例能够使用更多的内存，从而提高效率。您可以使用性能详情控制面板来分析数据库工作负载并确定内存使用率最高的查询。之后，您可以调整内存使用率最高的查询，以使用更少的内存。

---

相关信息

关于内存架构的 Oracle 文档