如何處理 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_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}
   -或-
   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 的預設值取決於資料庫執行個體的記憶體組態。如需更多資訊，請參閱 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 欄檢查各個容器的資源使用量。在 Non-CDB 組態中，con_id 欄一律為 0。

   若要降低 PGA 的高記憶體使用量，請使用以下一種或多種方法：

   限制 PGA_AGGREGATE_LIMIT 的值。這可能有助於避免執行個體耗盡記憶體。不過，當程序需要額外的 PGA 記憶體時，這可能導致連線失敗。

   降低 SGA_TARGET 的值。這可讓 PGA_AGGREGATE_TARGET 增加至 PGA_AGGEGATE_LIMIT 的值，而不會造成記憶體爭用問題。
   將執行個體類別大小增加至具有更多記憶體的大型執行個體大小。

5. 在依照前述步驟調整記憶體參數後，請監控資料庫效能並參考資料庫建議檢視表。

   如果記憶體參數限制造成效能下降，請嘗試增加執行個體大小。這可讓資料庫執行個體使用更多記憶體，以有效運作。

   使用 Performance Insights 儀表板來分析資料庫工作負載，並找出記憶體使用量最高的查詢。然後，調整這些記憶體使用量最高的查詢，以減少記憶體使用量。

相關資訊

Oracle 網站上的記憶體架構。