Oracle Memory Tuning Best Practices

 

1️⃣ Use Automatic Memory Management (AMM) or ASMM

Oracle provides automatic memory management to simplify tuning.

Automatic Memory Management (AMM)

MEMORY_TARGET
MEMORY_MAX_TARGET

Example:

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

Oracle automatically manages SGA + PGA.

---

Automatic Shared Memory Management (ASMM)

If AMM is not used, configure:

SGA_TARGET
PGA_AGGREGATE_TARGET

Example:

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

---

2️⃣ Monitor SGA Components Regularly

Key SGA areas:

• Shared Pool

• Buffer Cache

• Large Pool

• Java Pool

• Redo Log Buffer

Check SGA usage:

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

---

3️⃣ Properly Size the Shared Pool

Shared pool stores:

• SQL execution plans

• PL/SQL

• Data dictionary

Too small → errors like:

ORA-04031: unable to allocate shared memory

Check usage:

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

---

4️⃣ Optimize Buffer Cache

Buffer cache stores data blocks.

If too small → excessive disk I/O.

Check hit ratio:

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

Recommended:

Buffer Cache Hit Ratio > 90%

---

5️⃣ Tune PGA Memory

PGA handles:

• Sorting

• Hash joins

• Session memory

Check PGA statistics:

SELECT * FROM v$pgastat;

Best practice:

PGA_AGGREGATE_LIMIT ≈ 2 × PGA_AGGREGATE_TARGET

Example:

PGA_AGGREGATE_TARGET = 4G
PGA_AGGREGATE_LIMIT  = 8G

---

6️⃣ Monitor SQL Work Areas

Large queries may consume huge PGA memory.

Check active work areas:

SELECT *
FROM v$sql_workarea_active;

Look for:

• Large sorts

• Hash joins

---

7️⃣ Avoid Excessive Hard Parsing

Hard parsing fills shared pool memory.

Check parsing:

SELECT sql_text, parse_calls
FROM v$sqlarea
ORDER BY parse_calls DESC;

Best practice:

Use bind variables.

Example:

SELECT * FROM employees WHERE employee_id = :emp_id;

---

8️⃣ Monitor Memory Usage with AWR Reports

Use AWR reports to analyze memory performance.

Steps:

@$ORACLE_HOME/rdbms/admin/awrrpt.sql

Look for:

• Memory advisory

• PGA usage

• SGA tuning suggestions

---

9️⃣ Use Memory Advisory Views

Oracle provides advisory views for tuning.

Example:

SELECT *
FROM v$sga_target_advice;

For PGA:

SELECT *
FROM v$pga_target_advice;

These show recommended memory sizes.

---

🔟 Monitor OS Memory

Always check server memory.

Example (Linux):

free -g
top
vmstat

Ensure Oracle does not consume all physical RAM.

---

📊 Recommended Memory Allocation (General Guideline)

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

---

⚠️ Common Memory Errors DBAs Face

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

---

📘 Key Learning for Oracle DBAs

Always monitor these views:

v$sga
v$sgastat
v$pgastat
v$sql_workarea
v$sga_target_advice
v$pga_target_advice

Memory tuning should be data-driven, not guesswork.