New user sign up using AWS Builder ID
New user sign up using AWS Builder ID is currently unavailable on re:Post. To sign up, please use the AWS Management Console instead.
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 Database (DB) instance.
Short description
In Oracle databases, the available memory is mainly used by program global area (PGA) and system global area (SGA). When you configure an Oracle DB instance, be sure that the sum of these two components is less than the total physical memory. You must tune the memory parameters to avoid high swap usage and large amounts of physical memory usage. 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 the 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.
Resolution
To 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.
Complete the following steps:
-
Make sure that your DB instance uses automatic memory management or automatic shared memory management. To check the parameters SGA, PGA, and MEMORY_TARGET, run the following command:
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 is not set, but SGA_TARGET and PGA_AGGREGATE_TARGET are set, then the instance uses automatic shared memory management.For more information, see Enabling automatic memory management the Oracle website.
-
To view the memory usage by PGA and SGA, run the following command:
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;
To view the maximum memory allocated for PGA and SGA, after the last reboot, run the following command:
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';
To view the advisory views for PGA and SGA, run the following command:
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;
Note: In CDB configuration, the V$PGASTAT command shows the PDB specific information you perform in the query.
-
To avoid excessive memory consumption and high swap usage, define memory parameter settings. These settings 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. By default this parameter is set to the following value:
MEMORY_TARGET = MEMORY_MAX_TARGET = {DBInstanceClassMemory*3/4}
If your instance uses automatic shared memory management, then the parameters for PGA_AGGREGATE_TARGET and SGA_TARGET are set to the following default values:
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 adjust the default settings to limit the memory used by the Oracle instance. 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.
ExampleIf 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}ExampleIf your instance uses 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:
SGA_TARGET = {DBInstanceClassMemory*1/2}
-or-
SGA_TARGET = {DBInstanceClassMemory*2/3}To tune the memory usage of the program memory area, set the following value for PGA_AGGREGATE_TARGET:
PGA_AGGREGATE_TARGET = {DBInstanceClassMemory*1/12} -
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 PGA_AGGREGATE_LIMIT on the Oracle website.
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. This causes high swap usage and memory contention issues.
To troubleshoot these use cases, locate which processes or queries use a high amount of PGA. To view the allocation of program area per process, run the following command:
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;
To view the memory usage by PGA for user sessions, excluding background processes, run the following command:
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;
To view the memory usage by PGA per session and the queries that are run by each session, run the following command:
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;
Note: In CDB configuration, check which container resource usage by con_id column. In Non-CDB configuration, con_id column is always 0.
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. 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. -
After tuning the memory parameters as mentioned in the preceding steps, monitor the database performance and consult database advisory views.
If limits to the memory parameters causes performance degradation, then try increasing the instance size. This allows more memory usage for the database instance to work efficiently.
Use the Performance Insights dashboard to analyze the database workload and identify the queries with the highest memory usage. Then, tune these queries with the highest memory usage to use less memory.
Related information
Memory architecture on the Oracle website.

Relevant content
- asked 3 years agolg...
- Accepted Answerasked 2 years agolg...
- AWS OFFICIALUpdated 2 months ago
- AWS OFFICIALUpdated 7 months ago
- AWS OFFICIALUpdated 3 months ago
- AWS OFFICIALUpdated 3 months ago