Oracle Memory Management

Memory management involves maintaining optimal sizes for the Oracle instance memory structures as demands on the database change. Initialization parameter settings determine how SGA and instance PGA memory is managed.

Automatic Memory Management

In automatic memory management, Oracle Database manages the SGA and instance PGA memory completely automatically. This method is the simplest and is strongly recommended by Oracle.

The only user-specified controls are the target memory size initialization parameter (MEMORY_TARGET) and optional maximum memory size initialization parameter (MEMORY_MAX_TARGET). Oracle Database tunes to the target memory size, redistributing memory as needed between the SGA and the instance PGA.

The SGA can contain the In-Memory Column Store (IM column store) or memoptimize pool, both of which are optional. No matter which memory management method you use, size the IM column store with the INMEMORY_SIZE initialization parameter, and the memoptimize pool with the MEMOPTIMIZE_POOL_SIZE initialization parameter. The IM column store size are memoptimize pool are included in the memory target, but are not managed by the automatic resize algorithm. For example, if you set MEMORY_TARGET to 5 GB and INMEMORY_SIZE to 1 GB, then the overall memory target is 5 GB (not 6 GB), and the INMEMORY_SIZE is always 1 GB.

The following graphic shows a database that sometimes processes jobs submitted by online users and sometimes batch jobs. Using automatic memory management, the database automatically adjusts the size of the large pool and database buffer cache depending on which type of jobs are running. Note that the graphic does not depict the IM column store or memoptimize area.

If you create a database with DBCA and choose the basic installation option, then Oracle Database enables automatic memory management by default.

Shared Memory Management of the SGA

You can control the size of the SGA manually, either by setting the SGA size with automatic shared memory management, or by manually tuning SGA components.

If automatic memory management is not enabled, then the system must use shared memory management of the SGA. Shared memory management is possible in either of the following forms:

• Automatic shared memory management
  This mode enables you to exercise more direct control over the size of the SGA and is the default when automatic memory management is disabled. The database tunes the total SGA to the target size and dynamically tunes the sizes of SGA components. If you are using a server parameter file, then Oracle Database remembers the sizes of the automatically tuned components across database instance shutdowns.
• Manual shared memory management
  In this mode, you set the sizes of several individual SGA components and manually tune individual SGA components on an ongoing basis. You have complete control of individual SGA component sizes. The database defaults to this mode when both automatic memory management and automatic shared memory management are disabled.Note:When automatic memory management is disabled, then in some cases the database can automatically adjust the relative sizes of the shared pool and buffer cache, based on user workload.

Memory Management of the Instance PGA

If automatic memory management is not enabled, then Oracle Database uses either automatic or manual PGA memory management.

The following modes are possible for management of PGA memory:

• Automatic PGA memory managementWhen automatic memory management (MEMORY_TARGET) is disabled and PGA_AGGREGATE_TARGET is set to a nonzero value, the database uses automatic PGA memory management. In this mode, PGA_AGGREGATE_TARGET specifies a “soft” target size for the instance PGA. The target is soft because it only applies to specific types of memory allocations that can choose to use temporary space rather than the PGA. The database tunes the size of the instance PGA to this target and dynamically tunes the sizes of individual PGAs. If you do not explicitly set a target size, then the database automatically configures a reasonable default.The PGA_AGGREGATE_LIMIT initialization parameter dynamically sets an instance-wide hard limit for PGA memory. Because the parameter responds to changing memory conditions, you do not need to set the parameter value explicitly. By default, PGA_AGGREGATE_LIMIT is set to the greater of the following:
  • 2 GB
  • 200% of PGA_AGGREGATE_TARGET initialization parameter setting
  • (Value of PROCESSES initialization parameter setting) * 3 MB
  A background process periodically compares the PGA size to the limit set by PGA_AGGREGATE_LIMIT. If the limit is reached or exceeded, then this process terminates calls for the sessions using the most untunable PGA memory. If these sessions still do not release enough memory, then they are also terminated.
• Manual PGA memory managementWhen automatic memory management is disabled and PGA_AGGREGATE_TARGET is set to 0, the database defaults to manual PGA management. Previous releases of Oracle Database required the DBA to manually specify the maximum work area size for each type of SQL operator (such as a sort or hash join). This technique proved to be very difficult because the workload is always changing. Although Oracle Database supports the manual PGA memory management method, Oracle strongly recommends automatic memory management.

Summary of Memory Management Methods

Memory management is either automatic or manual.

If you do not enable automatic memory management, then you must separately configure one memory management method for the SGA and one for the PGA.

Note:

When automatic memory management is disabled for the database instance as a whole, Oracle Database enables automatic PGA memory management by default.

The following table includes the INMEMORY_SIZE initialization parameter and the MEMOPTIMIZE_POOL_SIZE initialization parameter. Both control memory areas that are optional.

Instance	SGA	PGA	Description	Initialization Parameters
Auto	n/a	n/a	The database tunes the size of the instance based on a single instance target size.	You set:Total memory target size for the database instance (MEMORY_TARGET)Optional maximum memory size for the database instance (MEMORY_MAX_TARGET)Optional size for the IM column store (INMEMORY_SIZE) in the SGA
n/a	Auto	Auto	The database automatically tunes the SGA based on an SGA target.The database automatically tunes the PGA based on a PGA target.	You set:SGA target size (SGA_TARGET)Optional SGA maximum size (SGA_MAX_SIZE)Optional size for the IM column store (INMEMORY_SIZE) in the SGAOptional size for the memoptimize pool in the SGA (MEMOPTIMIZE_POOL_SIZE)PGA aggregate target size (PGA_AGGREGATE_TARGET)Foot 1The database automatically configures the PGA_AGGREGATE_LIMIT initialization parameter. You may set this parameter manually.
n/a	Auto	Manual	The database automatically tunes the SGA based on an SGA target.You control the PGA manually, setting the maximum work area size for each type of SQL operator.	You set:SGA target size (SGA_TARGET)Optional SGA maximum size (SGA_MAX_SIZE)Optional size for the IM column store in the SGA (INMEMORY_SIZE)Optional size for the memoptimize pool in the SGA (MEMOPTIMIZE_POOL_SIZE)PGA work area parameters such as SORT_AREA_SIZE, HASH_AREA_SIZE, and BITMAP_MERGE_AREA_SIZE
n/a	Manual	Auto	You control the SGA manually by setting individual component sizes.The database automatically tunes the PGA based on a PGA target.	You set:Shared pool size (SHARED_POOL_SIZE)Buffer cache size (DB_CACHE_SIZE)Large pool size (LARGE_POOL_SIZE)Java pool size (JAVA_POOL_SIZE)Optional size for the IM column store (INMEMORY_SIZE) in the SGAOptional size for the memoptimize pool in the SGA (MEMOPTIMIZE_POOL_SIZE)PGA aggregate target size (PGA_AGGREGATE_TARGET)Foot 2The database automatically configures the PGA_AGGREGATE_LIMIT initialization parameter. You may set this parameter manually.
n/a	Manual	Manual	You must manually configure SGA component sizes.You control the PGA manually, setting the maximum work area size for each type of SQL operator.	You must manually configure SGA component sizes. You set:Shared pool size (SHARED_POOL_SIZE)Buffer cache size (DB_CACHE_SIZE)Large pool size (LARGE_POOL_SIZE)Java pool size (JAVA_POOL_SIZE)Optional size for the IM column store (INMEMORY_SIZE) in the SGAOptional size for the memoptimize pool in the SGA (MEMOPTIMIZE_POOL_SIZE)PGA work area parameters such as SORT_AREA_SIZE, HASH_AREA_SIZE, and BITMAP_MERGE_AREA_SIZE

credit: https://docs.oracle.com/en/database/oracle/oracle-database/19/cncpt/concepts-for-database-administrators.html