An Overview in Oracle 23ai Memory Management

 An Overview in Oracle 23ai Memory Management  

Alireza Kamrani (08/10/2024)

About Memory Management

The memory structures that must be managed are the system global area (SGA) and the instance program global area (instance PGA). Oracle Database supports various memory management methods, which are chosen by initialization parameter settings.

Unified Memory Management

Unified Memory configures the database instance memory with a single parameter, MEMORY_SIZE. 

Using Unified Memory Management

You can allow the Oracle Database instance to automatically manage and tune memory for you based on total memory size.

Unified Memory configures the database instance memory with a single parameter, MEMORY_SIZE. 

The database can dynamically use this memory for SGA, PGA, MGA, UGA, and other memory segments. 

The split between different memory segments is based off the memory sizing of the PDBs currently opened in the CDB. 

If huge pages are configured, they can be used for both SGA and PGA. Unified Memory provides an extremely flexible memory configuration.

Automatic Memory Management

Oracle Database can manage the SGA memory and instance PGA memory completely automatically. You designate only the total memory size to be used by the instance, and Oracle Database dynamically exchanges memory between the SGA and the instance PGA as needed to meet processing demands. 

This capability is referred to as automatic memory management. 

With this memory management method, the database also dynamically tunes the sizes of the individual SGA components and the sizes of the individual PGAs. 

Oracle recommends automatic memory management for databases where the total size of the SGA and PGA memory is less than or equal to four gigabytes.

Manual Memory Management

If you prefer to exercise more direct control over the sizes of individual memory components, you can disable automatic memory management and configure the database for manual memory management. There are a few different methods available for manual memory management. Some of these methods retain some degree of automation. The methods therefore vary in the amount of effort and knowledge required by the DBA. These methods are:

• Automatic shared memory management - for the SGA
• Manual shared memory management - for the SGA
• Automatic PGA memory management - for the instance PGA
• Manual PGA memory management - for the instance PGA

These memory management methods are described later in this chapter.

If you create your database with Database Configuration Assistant (DBCA) and choose the basic installation option, automatic memory management is enabled when system memory is less than or equal to 4 gigabytes. 

When system memory is greater than 4 gigabytes, automatic memory management is disabled, and automatic shared memory management is enabled. 

If you choose advanced installation, then DBCA enables you to select automatic memory management or automatic shared memory management.

Oracle recommends automatic shared memory management when the total size of the SGA and PGA memory is four gigabytes or larger.

MEMORY_SIZE in 23c:

MEMORY_SIZE specifies the size of instance-wide usable memory.

Property	Description
Parameter type	Big integer
Syntax	MEMORY_SIZE = integer [K | M | G]
Default value	0
Modifiable	You can use ALTER SYSTEM to increase or decrease the value of this parameter. However, the value of this parameter must always be greater than or equal to its value at instance startup.
Modifiable in a PDB	No
Range of values	1536 MB to MEMORY_MAX_SIZE
Basic	No
Oracle RAC	Different instances can use different values.

In a text-based initialization parameter file, if you omit MEMORY_MAX_SIZE and include a value for MEMORY_SIZE, then the database automatically sets MEMORY_MAX_SIZE to the value of MEMORY_SIZE. 

♦️If you omit the line for MEMORY_SIZE and include a value for MEMORY_MAX_SIZE, the MEMORY_SIZE parameter defaults to 0. 

After instance startup, you can dynamically change MEMORY_SIZE to a nonzero value, provided that it does not exceed the value of MEMORY_MAX_SIZE.

♦️Based on the value of MEMORY_SIZE, the values for the SGA and PGA parameters SGA_TARGET and PGA_AGGREGATE_LIMIT will be calculated internally for the instance. The sizing will also depend on the large page settings.

♦️When MEMORY_SIZE is set to a nonzero value, the CDB-level values for the following SGA and PGA parameters will be ignored by the database: SGA_MAX_SIZE, PGA_AGGREGATE_LIMIT, and SGA_TARGET. If you assign a value to any of these parameters at the time of instance startup, the database will not report an error, but the parameter value will be ignored. 

If you dynamically modify any of these parameters, an error will be reported.

When MEMORY_SIZE is set to a nonzero value, the MEMORY_MAX_TARGET and MEMORY_TARGET parameters should be set to 0. 

If you attempt to set either of these parameters to a nonzero value, the request will be ignored, and a warning will be written to the alert log at startup. 

Note:This parameter is available starting with Oracle Database 23ai

♦️Unified Memory Management (UMM): 

A New Approach in Oracle Database 23c♦️

Prior to Oracle Database 23c, managing memory involved multiple methods:

– Automatic memory management (AMM)
– Automatic shared memory management (ASMM)
– And Manual memory management 

With the introduction of Oracle Database 23c, a new contender joins the arena: 

Unified Memory Management (UMM).

– Oracle Database 23c introduces UMM, a new memory management method that simplifies and potentially enhances performance.

– UMM builds upon Automatic Memory Management (AMM), offering more flexibility and the ability to leverage huge pages.

– It’s controlled by the new parameter MEMORY_SIZE, which supersedes the AMM parameter MEMORY_TARGET.

With UMM, similar to AMM, you set a total memory size, and Oracle Database dynamically allocates memory between the System Global Area (SGA) and Program Global Area (PGA) as needed.

Huge Page Support: UMM can potentially utilize huge pages, which can boost performance and reduce memory overhead. This suggests it may not be constrained by the 4GB limit like AMM.

Conclusion

UMM holds promise for simplifying memory management in Oracle Database 23c. While initial tests reveal intriguing behavior, comprehensive understanding and best practices await further documentation and experimentation.

Alireza Kamrani 

08/10/2024