Oracle Performance Tuning Cheat Sheet — Key Parameters, Wait Events & SQL Tuning

Oracle Performance Tuning Cheat Sheet

Quick reference for Oracle database performance tuning. Key parameters, diagnostic shortcuts, and tuning formulas.

---

Key Initialization Parameters

Memory Parameters

Parameter	Default	Recommendation	Notes
MEMORY_TARGET	0	Physical RAM × 60-70%	AMM — manages SGA + PGA together
SGA_TARGET	0	Physical RAM × 40-50%	ASMM — auto-tunes SGA components
PGA_AGGREGATE_TARGET	10MB	Physical RAM × 20-25%	Target PGA for all sessions
PGA_AGGREGATE_LIMIT	2× PGA target	2-3× PGA target	Hard PGA limit (12c+)
SHARED_POOL_SIZE	Auto	Min 300MB-1GB	Set minimum if using ASMM
DB_CACHE_SIZE	Auto	Largest component	Buffer cache minimum
LARGE_POOL_SIZE	Auto	50-200MB	RMAN, parallel, shared server

Performance Parameters

Parameter	Default	Recommendation	Notes
OPTIMIZER_MODE	ALL_ROWS	ALL_ROWS	OLTP: consider FIRST_ROWS_n
CURSOR_SHARING	EXACT	EXACT	Use FORCE only as last resort
OPEN_CURSORS	50	300-1000	Per session cursor limit
SESSION_CACHED_CURSORS	50	100-200	Soft-parsed cursor cache
OPTIMIZER_INDEX_COST_ADJ	100	20-50	Lower = prefer indexes
DB_FILE_MULTIBLOCK_READ_COUNT	Auto	16-128	Full scan read size
RESULT_CACHE_MAX_SIZE	Auto	1-5% of shared pool	Query result caching (11g+)

Undo and Temp

Parameter	Default	Recommendation	Notes
UNDO_RETENTION	900	1800-3600	Seconds to keep undo (for ORA-01555)
UNDO_TABLESPACE	—	Sized for workload	Auto-extensible recommended
TEMP_UNDO_ENABLED	FALSE	TRUE (12c+)	Undo for temp tables in temp TS

---

Wait Event Quick Diagnosis

I/O Waits

Wait Event	Meaning	Tuning Action
db file sequential read	Single-block I/O (index reads)	Better indexes, reduce LIO
db file scattered read	Multi-block I/O (full scans)	Add indexes, tune SQL
log file sync	Redo write at commit	Batch commits, faster redo disk
log file parallel write	Background redo writes	Faster redo disk, fewer log switches
direct path read	Direct reads bypassing cache	Normal for large scans, parallel
direct path write	Direct writes (CTAS, sorts)	More temp space, PGA

Lock and Contention Waits

Wait Event	Meaning	Tuning Action
enq: TX - row lock contention	Row-level lock	Application design, kill blocker
enq: TM - contention	Table-level lock (DDL)	Schedule DDL in maintenance windows
buffer busy waits	Hot block contention	Reverse-key index, hash partition
latch: shared pool	Hard parsing	Use bind variables
latch: cache buffers chains	Buffer chain contention	Reduce logical I/O
library cache: mutex X	Library cache contention	Reduce hard parsing

RAC-Specific Waits

Wait Event	Meaning	Tuning Action
gc buffer busy acquire	Waiting for remote block	Reduce cross-instance access
gc cr/current block busy	Block held by remote node	Partition data by instance
gc cr/current grant 2-way	Normal RAC block transfer	Acceptable, monitor volume

Memory Waits

Wait Event	Meaning	Tuning Action
free buffer waits	No free buffers in cache	Increase DB_CACHE_SIZE
latch: shared pool	Shared pool fragmentation	Increase SHARED_POOL_SIZE

Scripts for wait analysis:

• vsysev.sql — System wait events
• vsessev.sql — Session wait events
• high_wait_times.sql — High wait sessions
• Wait Events Cheat Sheet — Detailed reference

---

Memory Sizing Formulas

Buffer Cache Hit Ratio

SELECT ROUND(1 - (phys.value / (consist.value + dbblk.value)), 4) * 100 hit_ratio
FROM v$sysstat phys, v$sysstat consist, v$sysstat dbblk
WHERE phys.name    = 'physical reads'
  AND consist.name = 'consistent gets'
  AND dbblk.name   = 'db block gets';

Target: > 95% for OLTP, > 80% for DSS

Library Cache Hit Ratio

SELECT ROUND(SUM(pins - reloads) / SUM(pins) * 100, 2) hit_ratio
FROM v$librarycache;

Target: > 99%

PGA Memory Sort Ratio

SELECT ROUND(mem.value / (mem.value + dsk.value) * 100, 2) memory_sort_pct
FROM v$sysstat mem, v$sysstat dsk
WHERE mem.name = 'sorts (memory)' AND dsk.name = 'sorts (disk)';

Target: > 99%

Scripts:

• vsgastat.sql — SGA breakdown
• pga.sql — PGA analysis
• vsortratio.sql — Sort ratio

---

I/O Tuning Targets

Metric	OLTP Target	DSS Target
Average single-block read	< 5ms	< 10ms
Average multi-block read	< 10ms	< 20ms
Redo write latency	< 5ms	< 10ms
Average I/O operations/sec	Depends on storage	Depends on storage

Scripts:

• vfileb.sql — File I/O with timing
• gvio.sql — I/O across RAC

---

SQL Tuning Shortcuts

Quick execution plan check

EXPLAIN PLAN FOR <your SQL>;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(NULL, NULL, 'ALL'));

Check plan for running SQL

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('&sql_id', NULL, 'ALL'));

Force plan stability (SQL Plan Baseline)

DECLARE
  l_plans PLS_INTEGER;
BEGIN
  l_plans := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(sql_id => '&sql_id');
  DBMS_OUTPUT.PUT_LINE(l_plans || ' plan(s) loaded');
END;
/

Flush a specific SQL from shared pool (12c+)

EXEC DBMS_SHARED_POOL.PURGE('&address,&hash_value', 'C');

Index recommendations from SQL Tuning Advisor

DECLARE
  l_task VARCHAR2(30);
BEGIN
  l_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_id => '&sql_id');
  DBMS_SQLTUNE.EXECUTE_TUNING_TASK(l_task);
  DBMS_OUTPUT.PUT_LINE('Task: ' || l_task);
END;
/
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('&task_name') FROM dual;

Scripts:

• ep.sql — Explain plan viewer
• explain_sqlid.sql — Explain by SQL ID
• gvsqltop.sql — Top SQL

---

Common Tuning Scenarios

High CPU Usage

1. Find top SQL by CPU: gvsqltop.sql, sysmetric_cpu.sql
2. Check execution plans for inefficient access paths
3. Verify statistics are current: SELECT last_analyzed FROM dba_tables
4. Consider parallel query for large scans

High I/O Wait

1. Check file I/O distribution: vfileb.sql
2. Find SQL causing most physical reads: gvsqltop.sql
3. Add or rebuild indexes for key predicates
4. Move hot files to faster storage

Shared Pool Contention

1. Check library cache: vlibrary.sql
2. Find hard-parsing SQL: gvsqltopunb.sql
3. Implement bind variables in application code
4. Increase SHARED_POOL_SIZE if needed

Temp Space Exhaustion

1. Check temp usage: temp.sql
2. Identify sessions using temp: large sorts, hash joins
3. Increase PGA_AGGREGATE_TARGET to reduce disk sorts
4. Resize temp tablespace if needed

---

Diagnostic Quick Commands

-- Current database time model
SELECT stat_name, ROUND(value/1000000) seconds
FROM v$sys_time_model ORDER BY value DESC;

-- Buffer cache advisory
SELECT size_for_estimate, estd_physical_read_factor
FROM v$db_cache_advice WHERE name = 'DEFAULT';

-- PGA target advisory
SELECT pga_target_for_estimate/1024/1024 target_mb,
       estd_pga_cache_hit_percentage hit_pct
FROM v$pga_target_advice;

-- SGA advisory
SELECT sga_size, estd_db_time FROM v$sga_target_advice;

-- Top 5 wait events right now
SELECT event, total_waits, time_waited_micro/1000000 seconds
FROM v$system_event
WHERE wait_class != 'Idle'
ORDER BY time_waited_micro DESC FETCH FIRST 5 ROWS ONLY;

---

Related Resources

• Performance Tuning Checklist — Full step-by-step tuning methodology
• Wait Events Cheat Sheet — Detailed wait event reference
• Oracle Troubleshooting Guide — Problem-based diagnostics
• Performance Analysis Scripts (171 tools) — Complete script library
• Oracle SQL Patterns Cheat Sheet — Common query patterns