Oracle | Memory Management

Oracle Database Memory Management Version 21c

General Information

Library Note

Morgan's Library Page Header

ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.

Purpose

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
Maximum value to settable for the MEMORY_TARGET	MEMORY_MAX_TARGET	0
Oracle system-wide usable memory	MEMORY_TARGET	0
PGA	PGA_AGGREGATE_LIMIT PGA_AGGREGATE_TARGET	3G (50% of SGA size) 1536M (25% of SGA size)
Recycle Pool	DB_RECYCLE_CACHE_SIZE	0
SGA	SGA_MAX_SIZE SGA_MIN_SIZE SGA_TARGET	6G 0 GG

Dependencies

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

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 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

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