Init parameterMemory

SHARED_POOL_SIZE

What it controls

Size of the shared pool: parsed SQL, PL/SQL and the dictionary cache. Under SGA_TARGET it's a minimum.

Know this before you change it

Setting a floor stops automatic resizing from shrinking the shared pool too far, a common fix for repeated ORA-04031.

Check and change it

1-- Current value on every instance2SELECT inst_id, name, value, isdefault, ismodified3FROM gv$parameter4WHERE name = 'shared_pool_size';5 6-- Value stored in the spfile7SELECT sid, value FROM v$spparameter WHERE name = 'shared_pool_size' AND isspecified = 'TRUE';8 9-- Dynamic: takes effect now and is kept after a restart10ALTER SYSTEM SET shared_pool_size = 3G SCOPE = BOTH SID = '*';11 12-- Remove it from the spfile to go back to the default at the next restart13ALTER SYSTEM RESET shared_pool_size 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…
• Literal SQL that should use bind variablesGroups statements that are identical apart from literals. Hundreds of copies of the same shape means hard parsing and shared pool churn; show the…

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_TARGETSize of the SGA under Automatic Shared Memory Management. Oracle moves memory between the buffer cache, shared pool and other pools as load changes.
• 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.
• 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.