Memory Management

52.1 Memory Management Modes

Automatic Memory Management (AMM) MEMORY_TARGET, MEMORY_MAX_TARGET (incompatible with HugePages)

Automatic Shared Memory Management (ASMM), recommended SGA_TARGET, SGA_MAX_SIZE + PGA_AGGREGATE_TARGET

DB_CACHE_SIZE, SHARED_POOL_SIZE, LARGE_POOL_SIZE, ...

PGA_AGGREGATE_LIMIT (12c) is a hard cap on PGA.

Minimum values for components under ASMM act as floors.

Linux HugePages for large SGAs (USE_LARGE_PAGES = ONLY).

52.2 Monitoring and Advisors

SELECT component, current_size/1024/1024 mb FROM v$sga_dynamic_components;
SELECT * FROM v$sga_target_advice;  SELECT * FROM v$pga_target_advice;
SELECT * FROM v$db_cache_advice WHERE name = 'DEFAULT';
SELECT * FROM v$shared_pool_advice;
SELECT name, value FROM v$pgastat;
SELECT * FROM v$memory_resize_ops;

52.3 Buffer Cache Details

LRU / touch-count algorithm, KEEP and RECYCLE pools, Database Smart Flash Cache, Full Database Caching mode, Automatic Big Table Caching.

Result cache: RESULT_CACHE_MODE, RESULT_CACHE_MAX_SIZE, DBMS_RESULT_CACHE.

True Cache (23ai): a read-only, in-memory, diskless cache instance in front of the primary.