Oracle | Memory Management

Oracle Database Memory Management Version 26ai

General Information

Library Note

Morgan's Library Page Header

The best Oracle News for FY2026

Oracle Database 26ai will be available on generic Linux platforms in January and soon on AIX and Windows

Memory Sizing Initialization Parameters

Component	Initialization Parameter	Default or Example
Block Caches	DB_BLOCK_CACHE DB_2K_CACHE_SIZE DB_4K_CACHE_SIZE DB_8K_CACHE_SIZE DB_16K_CACHE_SIZE DB_32K_CACHE_SIZE	0
Flash Cache	DB_FLASH_CACHE_FILE DB_FLASH_CACHE_SIZE	<string> 0
Keep Pool	DB_KEEP_CACHE_SIZE	0
Large Pool	LARGE_POOL_SIZE	N/A
Log Buffer	LOG_BUFFER	N/A
Managed Global Area MGA	???	?
Maximum value to settable for the MEMORY_TARGET	MEMORY_MAX_TARGET	0
Oracle system-wide usable memory	MEMORY_TARGET	0
Program Global Area PGA	PGA_AGGREGATE_LIMIT PGA_AGGREGATE_TARGET	3G (50% of SGA size) 1536M (25% of SGA size)
Recycle Pool	DB_RECYCLE_CACHE_SIZE	0
System Global Area SGA	SGA_MAX_SIZE SGA_MIN_SIZE SGA_TARGET	6G 0 GG

Dictionary Objects

AWR_CDB_BUFFER_POOL_STAT	DBA_HIST_PGASTAT
AWR_CDB_JAVA_POOL_ADVICE	DBA_HIST_PGA_TARGET_ADVICE
AWR_CDB_MEMORY_RESIZE_OPS	DBA_HIST_PROCESS_MEM_SUMMARY
AWR_CDB_MEMORY_TARGET_ADVICE	DBA_HIST_SESS_SGA_STATS
AWR_CDB_MEM_DYNAMIC_COMP	DBA_HIST_SGA
AWR_CDB_PGASTAT	DBA_HIST_SGASTAT
AWR_CDB_PGA_TARGET_ADVICE	DBA_HIST_SGA_TARGET_ADVICE
AWR_CDB_PROCESS_MEM_SUMMARY	DBA_HIST_SHARED_POOL_ADVICE
AWR_PDB_MEMORY_RESIZE_OPS	DBA_HIST_STREAMS_POOL_ADVICE
AWR_CDB_SESS_SGA_STATS	GV_$BUFFER_POOL
AWR_CDB_SGA	GV_$BUFFER_POOL_STATISTICS
AWR_CDB_SGASTAT	GV_$JAVAPOOL
AWR_CDB_SGA_TARGET_ADVICE	GV_$JAVA_POOL_ADVICE
AWR_CDB_SHARED_POOL_ADVICE	GV_$MEMOPTIMIZE_WRITE_AREA
AWR_CDB_STREAMS_POOL_ADVICE	GV_$MEMORY_CURRENT_RESIZE_OPS
AWR_PDB_MEMORY_TARGET_ADVICE	GV_$MEMORY_DYNAMIC_COMPONENTS
AWR_PDB_MEM_DYNAMIC_COMP	GV_$MEMORY_RESIZE_OPS
AWR_PDB_PGASTAT	GV_$MEMORY_TARGET_ADVICE
AWR_PDB_PGA_TARGET_ADVICE	GV_$PGASTAT
AWR_PDB_PROCESS_MEM_SUMMARY	GV_$PGA_TARGET_ADVICE
AWR_PDB_SESS_SGA_STATS	GV_$PGA_TARGET_ADVICE_HISTOGRAM
AWR_PDB_SGA	GV_$PMEM_FILESTORE
AWR_PDB_SGASTAT	GV_$PROCESS_MEMORY
AWR_PDB_SGA_TARGET_ADVICE	GV_$PROCESS_MEMORY_DETAIL
AWR_ROOT_MEMORY_RESIZE_OPS	GV_$PROCESS_MEMORY_DETAIL_PROG
AWR_ROOT_MEMORY_TARGET_ADVICE	GV_$SHARED_POOL_ADVICE
AWR_ROOT_MEM_DYNAMIC_COMP	GV_$SHARED_POOL_RESERVED
AWR_ROOT_PGASTAT	GV_$STREAMS_POOL_ADVICE
AWR_ROOT_PGA_TARGET_ADVICE	GV_$STREAMS_POOL_STATISTICS
AWR_ROOT_PROCESS_MEM_SUMMARY	GV_$SGA
AWR_ROOT_SESS_SGA_STATS	GV_$SGAINFO
AWR_ROOT_SGA	GV_$SGASTAT
AWR_ROOT_SGASTAT	GV_$SGA_CURRENT_RESIZE_OPS
AWR_ROOT_SGA_TARGET_ADVICE	GV_$SGA_DYNAMIC_COMPONENTS
CDB_HIST_BUFFER_POOL_STAT	GV_$SGA_DYNAMIC_FREE_MEMORY
CDB_HIST_JAVA_POOL_ADVICE	GV_$SGA_RESIZE_OPS
CDB_HIST_MEMORY_RESIZE_OPS	GV_$SGA_TARGET_ADVICE
CDB_HIST_MEMORY_TARGET_ADVICE
CDB_HIST_MEM_DYNAMIC_COMP
CDB_HIST_PGASTAT
CDB_HIST_PGA_TARGET_ADVICE
CDB_HIST_PROCESS_MEM_SUMMARY
CDB_HIST_SESS_SGA_STATS
CDB_HIST_SGA
CDB_HIST_SGASTAT
CDB_HIST_SGA_TARGET_ADVICE
CDB_HIST_SHARED_POOL_ADVICE
CDB_HIST_STREAMS_POOL_ADVICE
DBA_HIST_BUFFER_POOL_STAT	V_$SQL_SHARED_MEMORY
DBA_HIST_JAVA_POOL_ADVICE	WRH$_MEMORY_RESIZE_OPS
DBA_HIST_MEMORY_RESIZE_OPS	WRH$_MEMORY_TARGET_ADVICE
DBA_HIST_MEMORY_TARGET_ADVICE	WRH$_MEM_DYNAMIC_COMP
DBA_HIST_MEM_DYNAMIC_COMP	WRH$_PROCESS_MEMORY_SUMMARY

AMM vs ASMM

AMM was a mistake you should make go away if you have implemented it. The 10g ASMM memory paradigm is standard in 12c and above and vastly superior If you have a lot of SGA resize operations taking place move to ASMM.

To Convert AMM to ASMM perform the following then restart the instance ALTER SYSTEM SET memory_max_target=0 SID='*' SCOPE=SPFILE; ALTER SYSTEM SET memory_target=0 SID='*' SCOPE=SPFILE; ALTER SYSTEM SET pga_aggregate_target=<value> SID='*' SCOPE=SPFILE; ALTER SYSTEM SET sga_target=<value> SID='*' SCOPE=SPFILE; -- restart your database

Full Database Caching

Enable Caching
Full Database Caching is intended for OLTP systems and, unlike In-Memory Database, does not preload data into the cache ALTER DATABASE [FORCE] FULL DATABASE CACHING;

conn / as sysdba

      

      SHUTDOWN IMMEDIATE;

      STARTUP MOUNT;

      ALTER DATABASE force full database caching;

      ALTER DATABASE OPEN;

      

      SELECT force_full_db_caching FROM v_$database;

      

      FOR

      ---

      YES

Disable Caching ALTER DATABASE [FORCE] FULL DATABASE CACHING;

conn / as sysdba

      

      SHUTDOWN IMMEDIATE;

      STARTUP MOUNT;

      ALTER DATABASE no full database caching;

      ALTER DATABASE OPEN;

      

      SELECT force_full_db_caching FROM v_$database;

      

      FOR

      ---

      NO

Caveats from Tim Hall's valuable ORACLE-BASE website

The COMPATIBLE parameter must be set to 12.0.0 or higher.
If you are using AMM (MEMORY_TARGET) or ASMM (SGA_TARGET) it is possible the buffer cache size will alter, making the cache too small to hold the entire database. Either size the memory parameters appropriately, or better still set the minimum size of the buffer cache by setting the DB_CACH_SIZE parameter to an appropriately large value.
There is no pre-emptive loading of objects. Instead, objects are cached as they are accessed.
LOBs defined as NOCACHE can be cached when force full database cache mode is enabled. Under normal running they are not.
Enabling force full database cache mode applies to the CDB and all PDBs when using the multitenant option.
If you need to recover your controlfile, you should check that force full database cache mode is still enabled.

PDB Memory

Manage PDB memory To perform PDB level memory management MEMORY_TARGET must be set to zero (0)

DB_CACHE_SIZE (<= 50% of CDB and the sum of all PDBs <= 50% of the CDB level) INMEMORY_SIZE PGA_AGGREGATE_LIMIT (<= CDB level setting) PGA_AGGREGATE_TARGET (<= CDB level setting) SGA_MIN_SIZE SGA_TARGET SHARED_POOL_SIZE (<= 50% of CDB and the sum of all PDBs <= 50% of the CDB level)

-- Must have NONCDB_COMPATIBLE = FALSE (in CDB$ROOT)

DB_CACHE_SIZE + SHARED_POOL_SIZE <= 50% of the CDB level SGA_TARGET

Miscellaneous

NUMA By default almost all servers are configured for NUMA memory allocation. It is critically important that the servers, the operating environment (O/S and VM) and database are configured consistently. Oracle Databases, by default, install with NUMA support disabled except on Oracle's own engineered systems. Follow the link at page bottom for information on how to test and configure for consistent NUMA support.

Sorts will be faster if they occur in memory rather than on disk ... even if you have SSDs. Use the query at right to determine where sorts are taking place. SELECT a.value "Disk Sorts", b.value "Memory Sorts", ROUND((100*b.value)/DECODE((a.value+b.value), 0,1,(a.value+b.value)),2) "Pct Memory Sorts" FROM v_$sysstat a, v_$sysstat b WHERE a.name = 'sorts (disk)' AND b.name = 'sorts (memory)'; Disk Sorts Memory Sorts Pct Memory Sorts ---------- ------------ ---------------- 0 19767893 100

Check for SGA Resize Operations SELECT trunc(start_time) STIME, status, oper_type, oper_mode, parameter FROM v_$sga_resize_ops WHERE initial_size <> final_size; STIME STATUS OPER_TYPE OPER_MODE PARAMETER -------------------- --------- ------------- --------- --------------------- 21-MAR-2021 00:00:00 COMPLETE GROW IMMEDIATE java_pool_size 21-MAR-2021 00:00:00 COMPLETE GROW IMMEDIATE java_pool_size 21-MAR-2021 00:00:00 COMPLETE SHRINK IMMEDIATE db_cache_size 09-JUN-2021 00:00:00 COMPLETE SHRINK MANUAL db_keep_cache_size 09-JUN-2021 00:00:00 COMPLETE GROW MANUAL db_cache_size 09-JUN-2021 00:00:00 COMPLETE GROW MANUAL db_cache_size 09-JUN-2021 00:00:00 COMPLETE SHRINK MANUAL db_recycle_cache_size

MGA Stats Query SELECT ? FROM v_$ WHERE ? ; ? -------------- ?

PGA Stats Query col name format a40 col value format 999999999999999 SELECT * FROM v_$pgastat ORDER BY 1; NAME VALUE UNIT CON_ID --------------------------------------- ------------ -------- ------- aggregate PGA target parameter 1610612736 bytes 0 aggregate PGA auto target 832555008 bytes 0 bytes processed 2358098402304 bytes 0 cache hit percentage 100 percent 0 DGA allocated (under PGA) 14640128 bytes 0 extra bytes read/written 0 bytes 0 global memory bound 161054720 bytes 0 maximum DGA allocated 17207296 bytes 0 maximum MGA allocated 0 bytes 0 maximum PGA allocated 1634915328 bytes 0 maximum PGA used for auto workareas 67152896 bytes 0 maximum PGA used for manual workareas 2119680 bytes 0 max processes count 102 0 MGA allocated (under PGA) 0 bytes 0 over allocation count 0 0 PGA memory freed back to OS 289583923200 bytes 0 process count 88 0 recompute count (total) 4133881 0 total freeable PGA memory 173080576 bytes 0 total PGA allocated 921140224 bytes 0 total PGA inuse 685722624 bytes 0 total PGA used for auto workareas 0 bytes 0 total PGA used for manual workareas 0 bytes 0

SGA Information Query SELECT * FROM v_$sgainfo ORDER BY 1; NAME BYTES RES CON_ID ----------------------------------- ---------- --- ------- Buffer Cache Size 2432696320 Yes 0 Data Transfer Cache Size 0 Yes 0 Fixed SGA Size 9699920 No 0 Free SGA Memory Available 0 0 Granule Size 16777216 No 0 In-Memory Area Size 0 No 0 Java Pool Size 117440512 Yes 0 Large Pool Size 16777216 Yes 0 Maximum SGA Size 6442447440 No 0 Redo Buffers 23851008 No 0 Shared IO Pool Size 134217728 Yes 0 Shared Pool Size 3841982464 Yes 0 Startup overhead in Shared Pool 236688744 No 0 Streams Pool Size 0 Yes 0