如何处理我的 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 网站上的 Enabling automatic memory management（启用自动内存管理）。

2. 要查看 PGA 和 SGA 的内存使用情况，请运行以下命令：

   SQL> SELECT (value/1024/1024) CURRENT_PGA_ALLOCATED_IN_MB FROM V$PGASTAT WHERE NAME ='total PGA allocated';SQL> SELECT SUM (bytes/1024/1024) CURRENT_SGA_SIZE_IN_MB,con_id FROM V$SGASTAT group by con_id;

   要查看上次重启后为 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 FROMV$PGA_TARGET_ADVICE ORDER BY PGA_TARGET_FACTOR;
   SQL> SELECT SGA_SIZE,SGA_SIZE_FACTOR, ESTD_DB_TIME_FACTOR, ESTD_PHYSICAL_READS FROMV$SGA_TARGET_ADVICE ORDER BY SGA_SIZE ASC;

   **注意：**在 CDB 配置中，V$PGASTAT 命令显示您在查询中执行的 PDB 特定信息。

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}
   -or-
   MEMORY_TARGET = MEMORY_MAX_TARGET = {DBInstanceClassMemory*3/5}
   -or-
   MEMORY_TARGET = MEMORY_MAX_TARGET = {DBInstanceClassMemory*2/3}

   示例：如果您的实例使用的是自动共享内存管理，则根据上述步骤中显示的建议值仅减少 SGA 和/或 PGA 的内存参数：

   SGA_TARGET = {DBInstanceClassMemory*1/2}
   -or-
   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 的默认值取决于数据库实例的内存配置。有关更多信息，请参阅 Oracle 网站上的 PGA_AGGREGATE_LIMIT。

   如果 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,con_id FROM V$PROCESS ORDER BY 4;

   要查看用户会话（不包括后台进程）的 PGA 内存使用情况，请运行以下命令：

   SQL> SELECT sum(p.pga_alloc_mem)/1024/1024 "PGA Allocated MEMORY OF USER SESSION (MB)",p.con_id FROM v$process p, v$session s WHERE P.ADDR = S.paddr and p.background IS NULL group by p.con_id;

   要查看每个会话的 PGA 内存使用情况以及每个会话运行的查询，请运行以下命令：

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

   **注意：**在 CDB 配置中，通过 con_id 列检查每个容器资源的使用情况。在非 CDB 配置中，con_id 列始终为 0。

   要减少 PGA 的高内存使用量，请使用以下一种或多种方法：

   限制 PGA_AGGREGATE_LIMIT 的值。这可以防止实例耗尽内存。但是，当进程需要额外的 PGA 内存时，可能会导致连接失败。

   减少 SGA_TARGET 的值。这允许 PGA_AGGREGATE_TARGET 增加到 PGA_AGGEGATE_LIMIT 的值，而不会造成内存争用问题。
   将实例类大小增加到具有更多内存的更大实例。

5. 按照上述步骤调整内存参数后，监控数据库性能并查阅数据库建议视图。

   如果内存参数的限制导致性能下降，请尝试增加实例大小。这允许数据库实例使用更多内存以高效运行。

   使用性能详情控制面板分析数据库工作负载，并识别内存使用量最高的查询。然后，调优这些内存使用量最高的查询，以便减少内存使用。

相关信息

Oracle 网站上的 Memory architecture（内存架构）。