Oracle Database Memory Management
Version 18.3.0.1

General Information
Library Note Morgan's Library Page Header
Coming to OpenWorld 2018? Be sure to visit the TidalScale booth in Moscone South and learn how to solve performance problems and lower costs with Software Defined Servers. Before you visit the booth, or if you can't make it this year, check out TidalScale at www.tidalscale.com. Be sure to click on the Solutions link and look through the Oracle resources.
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
 
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)';

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-2017 Daniel A. Morgan All Rights Reserved