Oracle Memory Tuning Guide (Complete DBA Guide)

1️⃣ Oracle Memory Architecture

Oracle memory is mainly divided into two parts:

🔹 SGA (System Global Area)

Shared memory used by all sessions.

Main components:

• Database Buffer Cache – stores data blocks

• Shared Pool – stores SQL execution plans

• Large Pool – used for RMAN and parallel queries

• Java Pool

• Redo Log Buffer

Check SGA size:

SHOW PARAMETER sga;

---

🔹 PGA (Program Global Area)

Private memory allocated to each server process.

Used for:

• Sorting

• Hash joins

• Session variables

• Bitmap operations

Check PGA parameters:

SHOW PARAMETER pga;

---

2️⃣ Memory Management Methods

Automatic Memory Management (AMM)

Oracle automatically manages SGA + PGA.

Parameters:

memory_target
memory_max_target

Example:

ALTER SYSTEM SET memory_target=8G SCOPE=SPFILE;
ALTER SYSTEM SET memory_max_target=8G SCOPE=SPFILE;

---

Automatic Shared Memory Management (ASMM)

Oracle manages SGA automatically.

Parameters:

sga_target
pga_aggregate_target

Example:

ALTER SYSTEM SET sga_target=6G SCOPE=BOTH;
ALTER SYSTEM SET pga_aggregate_target=2G SCOPE=BOTH;

---

3️⃣ Check Current Memory Usage

Check SGA components:

SELECT component,
       current_size/1024/1024 MB
FROM v$sga_dynamic_components;

Check PGA usage:

SELECT name,value
FROM v$pgastat;

Check total SGA:

SELECT * FROM v$sga;

---

4️⃣ Shared Pool Tuning

Shared pool stores:

• SQL execution plans

• PL/SQL code

• Data dictionary

Check shared pool memory:

SELECT pool,name,bytes/1024/1024 MB
FROM v$sgastat
WHERE pool='shared pool';

Common issue:

ORA-04031: unable to allocate shared memory

Solution:

ALTER SYSTEM SET shared_pool_size=1G SCOPE=BOTH;

---

5️⃣ Buffer Cache Tuning

Buffer cache stores frequently accessed data blocks.

Check buffer cache hit ratio:

SELECT
(1 - (physical reads /
(db block gets + consistent gets))) * 100
AS buffer_cache_hit_ratio
FROM v$sysstat;

Recommended:

Hit Ratio > 90%

Increase buffer cache:

ALTER SYSTEM SET db_cache_size=4G SCOPE=BOTH;

---

6️⃣ PGA Memory Tuning

PGA handles:

• Sort operations

• Hash joins

• Session memory

Check PGA statistics:

SELECT * FROM v$pgastat;

Important columns:

• total PGA allocated

• cache hit percentage

Recommended configuration:

PGA_AGGREGATE_LIMIT ≈ 2 × PGA_AGGREGATE_TARGET

Example:

ALTER SYSTEM SET pga_aggregate_target=4G SCOPE=BOTH;
ALTER SYSTEM SET pga_aggregate_limit=8G SCOPE=BOTH;

---

7️⃣ Monitor SQL Work Areas

Large queries may consume excessive PGA.

Check active work areas:

SELECT
sql_id,
operation_type,
actual_mem_used
FROM v$sql_workarea_active;

Look for:

• Large sort operations

• Hash joins

---

8️⃣ Use Oracle Advisory Views

Oracle provides recommendations for memory tuning.

Check SGA advisory:

SELECT
sga_size,
estd_db_time
FROM v$sga_target_advice;

Check PGA advisory:

SELECT
pga_target_for_estimate,
estd_pga_cache_hit_percentage
FROM v$pga_target_advice;

These views help determine optimal memory sizes.

---

9️⃣ Monitor with AWR Reports

Generate AWR report:

@$ORACLE_HOME/rdbms/admin/awrrpt.sql

Important sections:

• Memory statistics

• PGA usage

• SGA recommendations

• Top wait events

---

🔟 OS Level Memory Monitoring

Always check server memory.

Linux commands:

free -g
top
vmstat

Ensure Oracle does not consume all physical RAM.

---

1️⃣1️⃣ Common Oracle Memory Errors

Error	Cause
ORA-04031	Shared pool memory shortage
ORA-04036	PGA memory limit exceeded
ORA-04030	Process memory exhausted

---

1️⃣2️⃣ Recommended Memory Allocation

Typical OLTP database:

SGA = 60–70% of RAM
PGA = 20–30% of RAM
OS  = remaining memory

Example (Server RAM 32GB):

SGA = 20GB
PGA = 8GB
OS  = 4GB

---

⭐ Best Practices for Oracle Memory Tuning

✔ Use automatic memory management when possible
✔ Monitor memory usage regularly
✔ Use bind variables to reduce shared pool usage
✔ Avoid excessive parallel queries
✔ Analyze AWR reports periodically
✔ Tune SQL before increasing memory