Oracle Database Memory Management
Version 21c

General Information
Library Note Morgan's Library Page Header
Which has the higher priority in your organization: Deploying a new database or securing the ones you already have? Looking for a website, and resources, dedicated solely to securing Oracle databases? Check out DBSecWorx.
Be sure to view the full listing of monographs in Morgan's Library
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

Related Topics
Built-in Functions
Built-in Packages
DBMS_MEMOPTIMIZE
NUMA
Persistent Memory
Startup Parameters
What's New In 19c
What's New In 20c-21c

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2019 Daniel A. Morgan All Rights Reserved
  DBSecWorx