Oracle Database Memory Management
Version 18.3.0.1

General Information
Library Note Morgan's Library Page Header
For how many years have you been working with physical servers that are starving your database of the memory necessary to deploy important new performance features such as the Result Cache, Memoptimize Pool, In-Memory Aggregation, In-Memory Column Store, and Full Database Caching? Too long? Contact me to learn how to improve all queries ... not just some queries.
Purpose  
Memory Sizing Initialization Parameters
Component Initialization Parameter Default
Block Caches DB_nK_CACHE_SIZE 0
Flash Cache DB_FLASH_CACHE_SIZE 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
20% of SGA size
1G
Recycle Pool DB_RECYCLE_CACHE_SIZE 0
SGA SGA_MAX_SIZE
SGA_MIN_SIZE
SGA_TARGET
3G
0
3G
Dependencies
gv$memory_current_resize_ops gv$sga gv$sga_dynamic_free_memory
gv$memory_dynamic_components gv$sgainfo gv$sga_resize_ops
gv$memory_resize_ops gv$sgastat gv$sga_target_advice
gv$memory_target_advice gv$sga_current_resize_ops gv$shared_pool_advice
gv$pgastat gv$sga_dynamic_components gv$shared_pool_reserved
gv$process_memory    
 
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.
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;
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 sga_target=<value> SID='*' SCOPE=SPFILE;
ALTER SYSTEM SET pga_aggregate_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)';
SGA Resize Operations Query SQL> 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
-------------------- --------- ------------- --------- --------------------
14-JUN-2018 00:00:00 COMPLETE  STATIC                  shared_pool_size
14-JUN-2018 00:00:00 COMPLETE  STATIC                  java_pool_size
14-JUN-2018 00:00:00 COMPLETE  STATIC                  db_cache_size
14-JUN-2018 00:00:00 COMPLETE  STATIC                  large_pool_size
14-JUN-2018 00:00:00 COMPLETE  SHRINK        DEFERRED  large_pool_size
14-JUN-2018 00:00:00 COMPLETE  GROW          DEFERRED  db_cache_size
14-JUN-2018 00:00:00 COMPLETE  GROW          IMMEDIATE _shared_io_pool_size
14-JUN-2018 00:00:00 COMPLETE  SHRINK        IMMEDIATE db_cache_size
30-JUN-2018 00:00:00 COMPLETE  SHRINK        DEFERRED  db_cache_size
30-JUN-2018 00:00:00 COMPLETE  GROW          DEFERRED  shared_pool_size
28-JUL-2018 00:00:00 COMPLETE  SHRINK        DEFERRED  db_cache_size
28-JUL-2018 00:00:00 COMPLETE  GROW          DEFERRED  shared_pool_size
PGA Stats Query SQL> col name format a40
SQL> col value format 999999999999999

SQL> SELECT * FROM v$pgastat;

NAME VALUE UNIT CON_ID
---------------------------------------- ----------- ------------ ----------
aggregate PGA target parameter 3695582208 bytes 0
aggregate PGA auto target 2077885440 bytes 0
global memory bound 369551360 bytes 0
total PGA inuse 1387017216 bytes 0
total PGA allocated 1822961664 bytes 0
maximum PGA allocated 2078908416 bytes 0
total freeable PGA memory 382009344 bytes 0
MGA allocated (under PGA) 0 bytes 0
maximum MGA allocated 0 bytes 0
process count 68 0
max processes count 104 0
PGA memory freed back to OS 117686206464 bytes 0
total PGA used for auto workareas 0 bytes 0
maximum PGA used for auto workareas 164128768 bytes 0
total PGA used for manual workareas 0 bytes 0
maximum PGA used for manual workareas 1357824 bytes 0
over allocation count 0 0
bytes processed 1128435675136 bytes 0
extra bytes read/written 0 bytes 0
cache hit percentage 100 percent 0
recompute count (total) 2160256 0
SGA Information Query SQL> SELECT * FROM v$sgainfo;

NAME                                  BYTES RES  CON_ID
-------------------------------- ---------- --- -------
Fixed SGA Size                      8907792 No        0
Redo Buffers                       74977280 No        0
Buffer Cache Size                4429185024 Yes       0
In-Memory Area Size                       0 No        0
Shared Pool Size                  989855744 Yes       0
Large Pool Size                    33554432 Yes       0
Java Pool Size                     16777216 Yes       0
Streams Pool Size                         0 Yes       0
Shared IO Pool Size               285212672 Yes       0
Data Transfer Cache Size                  0 Yes       0
Granule Size                       16777216 No        0
Maximum SGA Size                 5553257488 No        0
Startup overhead in Shared Pool   236515760 No        0
Free SGA Memory Available                 0           0

Related Topics
Built-in Functions
Built-in Packages
DBMS_MEMOPTIMIZE
NUMA
Startup Parameters
What's New In 12cR2
What's New In 18cR3

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