How do I handle low free memory or high swap usage issues in my Amazon RDS for Oracle database instance?
I want to handle low free memory or high swap usage issues in my Amazon Relational Database Service (Amazon RDS) for Oracle DB instance.
"I want to handle low free memory or high swap usage issues in my Amazon Relational Database Service (Amazon RDS) for Oracle DB instance.Short descriptionIn Oracle databases, the available memory is mainly used by program global area (PGA) and system global area (SGA). When you're configuring an Oracle DB instance, be sure that the sum of these two components is less than the total physical memory to make free memory available for other host processes and functionalities. You must tune the memory parameters to avoid high swap usage and the usage of large amounts of physical memory. For more information about swap usage and the recommended limits, see Why is my Amazon RDS DB instance using swap memory when I have sufficient memory?If your DB instance uses automatic memory management, then the total amount of memory used by the Oracle databases is managed by the parameters MEMORY_TARGET and MEMORY_MAX_TARGET. The value set in MEMORY_TARGET is equal to sum of SGA and PGA. Memory used by SGA is determined by the parameters SGA_MAX_SIZE and SGA_TARGET. Memory used by PGA is managed by the parameters PGA_AGGREGATE_TARGET and PGA_AGGREGATE_LIMIT. The default values for these parameters are set high for performance reasons. However, in certain use cases, these high values can cause memory contention and high swap usage.ResolutionTo troubleshoot this issue, use either of the following methods based on the memory management mode of your instance.If your DB instance uses automatic memory management, then decrease the value of MEMORY_TARGET.If your DB instance uses automatic shared memory management, then decrease the values of PGA and SGA parameters in your instance.Note: Though these parameters are dynamic parameters, you must reboot your instance without failover for these parameters to be modified. For more information, see the Note in Turning on HugePages for an RDS for Oracle instance.Try the following troubleshooting steps:1.    Check if your DB instance uses automatic memory management or automatic shared memory management. You can do so by checking the parameters SGA, PGA, and MEMORY_TARGET:SQL> SHOW PARAMETER SGA;SQL> SHOW PARAMETER PGA;SQL> SHOW PARAMETER MEMORY_TARGET;If MEMORY_TARGET is set, then the instance uses automatic memory management.If MEMORY_TARGET not set, but SGA_TARGET and PGA_AGGREGATE_TARGET are set, then the instance uses automatic shared memory management.For more information on different memory management modes, see Oracle documentation for About memory management.2.    Run the following queries to view the memory usage by PGA and SGA, respectively: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;Run the following queries to view the maximum memory allocated for PGA and SGA, respectively, after the last reboot: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';Run the following queries to view the advisory views for PGA and SGA, respectively: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;3.    Define appropriate settings for memory parameters to avoid excessive memory consumption and high swap usage. These settings can also help you to tune the database for better performance. You can change any of the parameters in these examples using the instance parameter group.If your instance uses automatic memory management, then only the MEMORY_TARGET parameter is set. This parameter is set to the following value, by default:MEMORY_TARGET = MEMORY_MAX_TARGET = {DBInstanceClassMemory*3/4}If your instance uses automatic shared memory management, then the parameters PGA_AGGREGATE_TARGET and SGA_TARGET are set to the following default values, respectively:PGA_AGGREGATE_TARGET= {DBInstanceClassMemory*1/8}SGA_TARGET= SGA_MAX_SIZE = {DBInstanceClassMemory*3/4}If the memory usage with these default settings is high, then you might adjust the default settings to limit the memory used by the Oracle instance. You might use the preceding memory advisory views for adjusting memory parameters. It’s a best practice to tune the memory parameters in a test environment before applying the changes to production databases.For example:If your instance is automatic memory management, then reduce only MEMORY_TARGET and MEMORY_MAX_TARGET values: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}If your instance is using automatic shared memory management, then reduce only the memory parameters of SGA and/or PGA based on the advisory values shown in the preceding step. For example:SGA_TARGET = {DBInstanceClassMemory*1/2}-or-SGA_TARGET = {DBInstanceClassMemory*2/3}You can also tune the memory usage of the program memory area by setting the following value for PGA_AGGREGATE_TARGET:PGA_AGGREGATE_TARGET = {DBInstanceClassMemory*1/12}4.    In certain use cases, the memory usage by the PGA might exceed the PGA_AGGREGATE_TARGET value and reach up to the value set in the PGA_AGGREGATE_LIMIT parameter. The default value of PGA_AGGREGATE_LIMIT depends on the memory configuration of the database instance. For more information, see Oracle documentation for PGA_AGGREGATE_LIMIT. If the default value of PGA_AGGREGATE_LIMIT is very high, then the RDS instance might have very low or no physical memory for the underlying host, causing high swap usage and memory contention issues.To troubleshoot these use cases, locate which processes or queries are using a high amount of PGA. Run the following query to view the allocation of program area per process: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;Run the following query to view the memory usage by PGA for user sessions, excluding background processes: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;Run the following query to view the memory usage by PGA per session and the queries that are run by each session: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;To reduce the high memory usage by PGA, use one or more of the following methods:Limit the value of PGA_AGGREGATE_LIMIT. This might prevent the instance from running out of memory. However, it might cause connections failure when extra PGA memory is required for a process.Reduce the SGA_TARGET value appropriately. This allows PGA_AGGREGATE_TARGET to increase up to the value of PGA_AGGEGATE_LIMIT without causing memory contention issues.Increase the instance class size to a larger instance size with more memory.5.    After tuning the memory parameters as mentioned in the preceding steps, monitor the database performance and consult database advisory views. If limiting the memory parameters causes performance degradation, then try increasing the instance size. This allows more memory usage for the database instance to work efficiently. You might use the Performance Insights dashboard to analyze the database workload and identify the queries with the highest memory usage. Then, you might tune these queries with the highest memory usage to use less memory.Related informationOracle documentation for Memory architectureFollow"