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).
Unfortunately, the documentation is currently limited and points out only the following key points about this method:
– 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.
Initial Testing Insights:
Let’s perform basic testing on this method parameter. I am performing my testing on a VM with the Oracle Database 23c Free-Developer Release.
I increased the vm memory to 8 GB, shutdown the database, started the database in NOMOUNT mode to change its memory management parameters.
Note: In Oracle database 23c Free-Developer Release provided as an Oracle VirtualBox appliance, the TWO_TASK variable is set in the .bashrc file. To connect to the instance directly as sysdba, you need to reset this variable using the command unset TWO_TASK
# total memory installed in the system:
[oracle@localhost ~]$ grep MemTotal /proc/meminfo
MemTotal: 8124820 kB
# in the database, show the memory management setting:
SQL> show parameter SGA_TARGET
NAME TYPE VALUE
---------- ----------- -----
sga_target big integer 1536M
SQL> show parameter PGA_AGGREGATE_TARGET
NAME TYPE VALUE
-------------------- ----------- -----
pga_aggregate_target big integer 512M
# restart the database in nomount mode
SQL> startup nomount
ORACLE instance started.
Total System Global Area 1608409424 bytes
Fixed Size 10043728 bytes
Variable Size 838860800 bytes
Database Buffers 754974720 bytes
Redo Buffers 4530176 bytes
# take backup of the spfile
SQL> CREATE PFILE='/tmp/mypfile.ora' FROM SPFILE='/opt/oracle/product/23c/dbhomeFree/dbs/spfileFREE.ora';
File created.
# disable the ASMM
ALTER SYSTEM RESET sga_target SCOPE=SPFILE;
ALTER SYSTEM RESET pga_aggregate_target SCOPE=SPFILE;
# enable the UMM
ALTER SYSTEM SET memory_size=4250 SCOPE=SPFILE;
# restart the database instance
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 1608409424 bytes
Fixed Size 10043728 bytes
Variable Size 838860800 bytes
Database Buffers 754974720 bytes
Redo Buffers 4530176 bytes
Database mounted.
Database opened.
# verify that the UMM is enabled:
# observe that not the full 4.2G is assigned to the parameter
SQL> show parameter MEMORY_SIZE
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
inmemory_size big integer 0
memory_size big integer 1536M <---
Setting MEMORY_SIZE to a value over 4GB didn’t consistently result in memory allocation above 4GB. More testing is required to determine the factors influencing this behavior.
Further documentation from Oracle is expected to shed light on UMM’s mechanics and best practices.
Following are the references for this new concept in the documentation:
Database Administrator’s Guide
Managing Memory
https://docs.oracle.com/en/database/oracle/oracle-database/23/admin/managing-memory.html#GUID-04EFED7D-D1F1-43C3-B78F-0FF9AFAC02B0
Database Reference
About MEMORY_SIZE Parameter
https://docs.oracle.com/en/database/oracle/oracle-database/23//refrn/MEMORY_SIZE.html#GUID-26727D61-E5E7-473B-8CF4-E7301D2C2465
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.