Oracle Database Memory Management Version 184.108.40.206
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.
Memory Sizing Initialization Parameters
Maximum value to settable for the MEMORY_TARGET
Oracle system-wide usable memory
20% of SGA size
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
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
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.
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)
PGA_AGGREGATE_LIMIT (<= CDB level setting)
PGA_AGGREGATE_TARGET (<= CDB level setting)
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
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)';