Oracle Initialization Parameters - Complete Reference with Tuning Guidance

Oracle Initialization Parameters

Oracle initialization parameters (init.ora / SPFILE) control every aspect of database behavior — from memory allocation and session limits to optimizer strategy and recovery configuration. Proper parameter tuning is the foundation of Oracle performance management.

Memory Parameters

Memory parameters control how Oracle allocates SGA and PGA memory. These are the most impactful parameters for database performance.

Parameter	Description
SGA_TARGET	Automatic SGA Memory Management — Oracle auto-tunes SGA components
SGA_MAX_SIZE	Upper limit for SGA memory allocation
PGA_AGGREGATE_TARGET	Automatic PGA memory management for sorts, hash joins, and bitmaps
PGA_AGGREGATE_LIMIT	Hard ceiling on total PGA consumption (12c+)
MEMORY_TARGET	Automatic Memory Management — manages SGA + PGA together
MEMORY_MAX_TARGET	Upper limit for AMM total memory
SHARED_POOL_SIZE	Minimum shared pool size (library cache, dictionary cache)
DB_CACHE_SIZE	Minimum buffer cache size for data blocks

Memory Management Strategies

AMM (Automatic Memory Management)
├── MEMORY_TARGET controls total memory
├── Oracle auto-tunes SGA_TARGET + PGA_AGGREGATE_TARGET
└── Best for: smaller databases, simplified management

ASMM (Automatic Shared Memory Management)
├── SGA_TARGET controls SGA components
├── PGA_AGGREGATE_TARGET set separately
└── Best for: production databases, more control

Manual Memory Management
├── Individual pools sized explicitly (SHARED_POOL_SIZE, DB_CACHE_SIZE, etc.)
├── PGA_AGGREGATE_TARGET or WORKAREA_SIZE_POLICY=MANUAL
└── Best for: specific tuning requirements, legacy compatibility

Sessions & Processes Parameters

Control connection capacity and cursor management.

Parameter	Description
PROCESSES	Maximum OS processes (connections) — static, requires restart
SESSIONS	Maximum concurrent sessions — derived from PROCESSES
OPEN_CURSORS	Maximum open cursors per session — prevents ORA-01000
SESSION_CACHED_CURSORS	Cached cursors per session for reduced parse overhead

Quick Capacity Check

SELECT resource_name, current_utilization, max_utilization,
       initial_allocation, limit_value
FROM v$resource_limit
WHERE resource_name IN ('processes', 'sessions');

Optimizer Parameters

Control query optimization behavior and plan stability.

Parameter	Description
OPTIMIZER_MODE	Optimizer goal — ALL_ROWS vs FIRST_ROWS_n
OPTIMIZER_FEATURES_ENABLE	Lock optimizer to a specific version — critical for upgrades
CURSOR_SHARING	Auto-replace literals with binds to reduce hard parsing

Undo & Recovery Parameters

Control transaction undo management and recovery area sizing.

Parameter	Description
UNDO_TABLESPACE	Active undo tablespace for transaction management
UNDO_RETENTION	Minimum undo retention — prevents ORA-01555
DB_RECOVERY_FILE_DEST_SIZE	Flash Recovery Area size for backups and archive logs

General Parameters

Foundational parameters set at database creation or upgrade time.

Parameter	Description
DB_BLOCK_SIZE	Block size — set once at creation, cannot change
COMPATIBLE	Version compatibility — the point of no return for upgrades

Quick Reference: Check All Non-Default Parameters

SELECT name, value, isdefault, ismodified, description
FROM v$parameter
WHERE isdefault = 'FALSE'
ORDER BY name;

Coming Soon

Additional parameter categories being documented:

• I/O Parameters — DB_FILE_MULTIBLOCK_READ_COUNT, DISK_ASYNCH_IO, FILESYSTEMIO_OPTIONS
• RAC Parameters — CLUSTER_DATABASE, INSTANCE_NUMBER, THREAD
• Security Parameters — AUDIT_TRAIL, SEC_CASE_SENSITIVE_LOGON
• Multitenant Parameters — ENABLE_PLUGGABLE_DATABASE, MAX_PDBS
• Network Parameters — LOCAL_LISTENER, REMOTE_LISTENER, DISPATCHERS