Init parameterMemory

SGA_TARGET

What it controls

Size of the SGA under Automatic Shared Memory Management. Oracle moves memory between the buffer cache, shared pool and other pools as load changes.

Know this before you change it

Can be raised only up to SGA_MAX_SIZE without a restart. Any value set for SHARED_POOL_SIZE or DB_CACHE_SIZE becomes a floor that the automatic resizing won't go below.

Check and change it

1-- Current value on every instance2SELECT inst_id, name, value, isdefault, ismodified3FROM gv$parameter4WHERE name = 'sga_target';5 6-- Value stored in the spfile7SELECT sid, value FROM v$spparameter WHERE name = 'sga_target' AND isspecified = 'TRUE';8 9-- Dynamic: takes effect now and is kept after a restart10ALTER SYSTEM SET sga_target = 16G SCOPE = BOTH SID = '*';11 12-- Remove it from the spfile to go back to the default at the next restart13ALTER SYSTEM RESET sga_target SCOPE = SPFILE SID = '*';

Related scripts

• SGA components and PGA usageCurrent size of each SGA pool, then PGA target against what's actually allocated. PGA allocated well above target points at sort- or hash-heavy…

Open in the parameter referenceOracle's reference

More in Memory

• MEMORY_TARGETTotal memory for SGA and PGA together under Automatic Memory Management.
• MEMORY_MAX_TARGETUpper limit for MEMORY_TARGET.
• SGA_MAX_SIZEThe most memory the SGA can grow to without a restart.
• PGA_AGGREGATE_TARGETTarget for the total private memory of all server processes, used for sorts, hash joins and bitmap operations.
• PGA_AGGREGATE_LIMITHard limit on total PGA (12c and later). Above it, Oracle ends the calls or sessions using the most PGA with ORA-04036.
• SHARED_POOL_SIZESize of the shared pool: parsed SQL, PL/SQL and the dictionary cache. Under SGA_TARGET it's a minimum.
• DB_CACHE_SIZESize of the default buffer cache. Under SGA_TARGET it's a minimum.
• USE_LARGE_PAGESWhether the SGA is placed in Linux HugePages.
• INMEMORY_SIZESize of the In-Memory column store. On 12.2 and later it can be increased while the instance is running.