Master Oracle Memory Tuning: Key SGA & PGA Parameters Explained

Overview

Oracle memory is divided into shared (SGA – System Global Area) and private (PGA – Process Global Area) regions. On UNIX the SGA resides in shared memory segments because Oracle runs as multiple processes; on Windows it runs as a single‑process multithreaded instance, so no shared segment is required. When using the shared server (MTS) mode, part of the PGA (UGA) is placed in large_pool_size .

SGA Components and Typical Values

Running select * from v$sga; returns a breakdown such as:

NAME               VALUE
----------         --------------------
Fixed Size         454032
Variable Size      109051904
Database Buffers   385875968
Redo Buffers       667648

Fixed Size : Platform‑specific constant that holds component sizes for the SGA.

Variable Size : Holds shared_pool_size, java_pool_size, large_pool_size, etc.

Database Buffers : The buffer cache. In 8i it consists of db_block_buffer*db_block_size, buffer_pool_keep, buffer_pool_recycle. In 9i it expands to db_cache_size, db_keep_cache_size, db_recycle_cache_size, db_nk_cache_size.

Redo Buffers : The log buffer ( log_buffer). Values from v$parameter reflect the initialization file, while v$sgastat shows the actual allocated size, which includes protection pages (≈11 KB).

Key Memory Parameters and Recommended Settings

log_buffer : Generally set to 3–5 MB for most production systems; increase if the redo‑log‑buffer hit ratio exceeds 1 %.

large_pool_size : 20–30 MB is sufficient when MTS is not used; larger if parallel queries or RMAN backups need it.

java_pool_size : 10–20 MB is safe when Java is not used; can be as low as 32 KB depending on installed components.

shared_pool_size : Keep below 300 MB unless heavy PL/SQL, packages, or ERP workloads demand more (500 MB+). Typical guidelines: 100 MB for 1 GB RAM, 150 MB for 2 GB, 200–300 MB for 8 GB.

SGA_MAX_SIZE : Caps the total virtual memory that SGA can grow to; prevents excessive paging and potential crashes.

PRE_PAGE_SGA : When TRUE, forces the entire SGA to be allocated in physical memory at instance startup, improving peak performance at the cost of longer startup time.

LOCK_SGA : When TRUE, locks the whole SGA in RAM to avoid paging; ineffective on systems that do not support memory locking.

SGA_TARGET : Introduced in Oracle 10g for Automatic Shared Memory Management (ASMM). Setting this value lets Oracle automatically resize the individual SGA components while keeping their sum within the target.

Performance‑Related Checks and Tuning Steps

Library Cache Hit Ratio (target > 99 %):

select 1-(sum(reloads)/sum(pins)) "Library cache Hit Ratio" from v$librarycache;

If below 99 %, increase shared_pool_size and run:

alter system flush shared_pool;

alter system set shared_pool_size=<value> scope=spfile;

Data Buffer Cache Hit Ratio (target > 90 %):

select 1 - (phy.value / (cur.value + con.value)) "HIT RATIO"
from v$sysstat cur, v$sysstat con, v$sysstat phy
where cur.name = 'db block gets'
  and con.name = 'consistent gets'
  and phy.name = 'physical reads';

If below 90 %, raise db_cache_size: alter system set db_cache_size=<value> scope=spfile; Dictionary Cache Hit Ratio (target > 95 %):

select 1 - (sum(getmisses) / sum(gets)) "Data Dictionary Hit Ratio" from v$rowcache;

If low, increase shared_pool_size as in step 1.

Log Buffer Hit Ratio (target < 1 %):

select (req.value * 5000) / entries.value "Ratio"
from v$sysstat req, v$sysstat entries
where req.name = 'redo log space requests'
  and entries.name = 'redo entries';

If the ratio exceeds 1 %, raise log_buffer: alter system set log_buffer=<value> scope=spfile; undo_retention (must exceed max maxquerylen):

col undo_retention format a30
select value "undo_retention" from v$parameter where name='undo_retention';
select max(maxquerylen) From v$undostat Where begin_time>sysdate-(1/4);

If insufficient, increase:

alter system set undo_retention=<value> scope=spfile;

32‑bit vs 64‑bit Considerations

On 32‑bit Oracle installations the SGA cannot exceed roughly 1.7 GB, even if the host has much more RAM. Switching to a 64‑bit database (and a 64‑bit OS) removes this ceiling. The bitness can be checked with: select version, banner from v$instance; Some OSes provide work‑arounds to exceed the 1.7 GB limit, but native 64‑bit deployments are recommended for large memory allocations.