Oracle Performance Tuning Checklist — Step-by-Step Methodology

Oracle Performance Tuning Checklist

A systematic approach to Oracle database performance tuning. Follow this checklist to identify and resolve performance bottlenecks methodically.

---

Step 1: Establish a Baseline

Before tuning, capture the current state of the database.

Scripts to run:

• gvsess.sql — Active sessions across all instances
• vsysev.sql — System-wide wait event statistics
• vsgastat.sql — SGA memory breakdown
• pga.sql — PGA memory allocation
• vfileb.sql — File I/O statistics with timing

What to record:

• Top 5 wait events and their average wait times
• Buffer cache hit ratio (target: >95%)
• PGA memory usage vs. target
• Active session count during peak hours
• Average response time for key queries

---

Step 2: Analyze Wait Events

Wait events tell you where Oracle is spending time.

Scripts to run:

• vsysev.sql — System-wide event statistics
• vsessev.sql — Session-level wait events
• high_wait_times.sql — Sessions with high wait times
• dhsysev.sql — AWR historical wait analysis
• vsysevw.sql — Wait events ranked by time

Common wait events and actions:

Wait Event	Likely Cause	Action
db file sequential read	Poor indexing or full table scans	Check execution plans, add indexes
db file scattered read	Full table scans	Verify query predicates, consider partitioning
log file sync	Commit-heavy workload	Batch commits, check redo log I/O
buffer busy waits	Hot blocks	Reverse-key indexes, hash partitioning
enq: TX - row lock contention	Application locking	Review application logic
latch: shared pool	Hard parsing	Use bind variables

Related errors:

• ORA-04031: Unable to Allocate Shared Memory — SGA-related waits
• ORA-04030: Out of Process Memory — PGA-related waits

---

Step 3: SQL Tuning

Poor SQL is the most common cause of performance issues.

Scripts to run:

• gvsqltop.sql — Top SQL by resource consumption
• vsqlexe.sql — Top SQL by execution count
• vsqltime.sql — SQL performance with timing
• ep.sql — Explain plan viewer
• gvplanid.sql — Execution plan analysis
• explain_sqlid.sql — Explain plan by SQL ID

Tuning priorities:

1. Highest CPU consumers — SQL using the most CPU time
2. Most I/O intensive — SQL generating the most physical reads
3. Most frequently executed — Even small improvements multiply across millions of executions
4. Longest running — Long-running queries that block other operations

Common fixes:

• Add missing indexes based on WHERE clause predicates
• Replace SELECT * with specific column lists
• Use bind variables to reduce hard parsing
• Rewrite correlated subqueries as joins
• Add hints only as a last resort

---

Step 4: Memory Tuning

Ensure SGA and PGA are properly sized.

SGA Analysis

Scripts to run:

• vsgastat.sql — SGA memory breakdown
• vsga_resize.sql — SGA resize history
• show_sga.sql — SGA statistics summary
• vsgasize.sql — SGA size breakdown
• vlibrary.sql — Library cache statistics

Key metrics:

• Buffer cache hit ratio > 95%
• Library cache hit ratio > 99%
• Dictionary cache hit ratio > 95%
• Shared pool free memory > 5% of total

PGA Analysis

Scripts to run:

• pga.sql — PGA memory analysis
• gvpga.sql — PGA memory across RAC instances
• vsortratio.sql — Memory sort ratio

Target: Memory sort ratio > 99% (sorts should happen in memory, not on disk)

Related errors:

• ORA-04031: Unable to Allocate Shared Memory
• ORA-04030: Out of Process Memory
• ORA-04036: PGA Memory Limit Exceeded

---

Step 5: I/O Tuning

Identify I/O bottlenecks and optimize storage access patterns.

Scripts to run:

• vfileb.sql — File I/O statistics with timing
• vfile.sql — File I/O statistics
• gvio.sql — I/O statistics across RAC
• random_io.sql — Random I/O analysis
• scattered-io.sql — Scattered I/O waits
• sequential-io.sql — Sequential I/O waits

What to check:

• Average read time per datafile (target: < 10ms)
• Even I/O distribution across files and disks
• Redo log write latency (target: < 5ms)
• Temp tablespace I/O (sort operations spilling to disk)

Related errors:

• ORA-01652: Unable to Extend Temp Segment
• ORA-01653: Unable to Extend Table

---

Step 6: Lock and Contention Analysis

Identify locking issues causing application waits.

Scripts to run:

• gvlock.sql — Lock analysis across RAC
• gvlockb.sql — Blocking lock chains
• vlockobj.sql — Locked objects
• vlockb2.sql — Blocking and waiting locks
• temp.sql — Temp space and lock analysis
• vsessenq.sql — Session enqueue waits

Resolution steps:

1. Identify the blocking session
2. Determine what SQL the blocker is running
3. Contact the application owner or kill the session if appropriate
4. Review application design for lock reduction opportunities

Related errors:

• ORA-00054: Resource Busy
• ORA-00060: Deadlock Detected

---

Step 7: Space Management

Prevent space-related outages proactively.

Scripts to run:

• ddbspacea.sql — Tablespace space with autoextend
• ddbspaceb.sql — Tablespace space summary
• fra_space.sql — Flash Recovery Area usage
• dsegbig.sql — Largest segments
• maxshrink.sql — Maximum shrink analysis

Related errors:

• ORA-01653: Unable to Extend Table
• ORA-01654: Unable to Extend Index
• ORA-19815: Flash Recovery Area Full

---

Step 8: Generate Reports

Document findings with AWR and ASH reports.

Scripts to run:

• awrrpt.sql — AWR report generation
• ashrpt.sql — ASH report generation
• addmrpt.sql — ADDM advisory report
• snap_tracker.sql — AWR snapshot SQL analysis

Recommended cadence:

• Daily: Review top wait events and top SQL
• Weekly: Generate AWR reports for peak periods
• Monthly: Full performance review with trend analysis
• Quarterly: Capacity planning review

---

Quick Decision Tree

Performance problem reported
├── Is it a specific query?
│   ├── Yes → Check execution plan (ep.sql) → SQL tuning
│   └── No → Continue
├── Check wait events (vsysev.sql)
│   ├── I/O waits dominant → Step 5 (I/O Tuning)
│   ├── Lock waits dominant → Step 6 (Lock Analysis)
│   ├── Memory waits dominant → Step 4 (Memory Tuning)
│   └── CPU waits dominant → Step 3 (SQL Tuning)
└── Check space (ddbspacea.sql)
    └── Tablespaces near full → Step 7 (Space Management)

---

Related Resources

• Oracle Database Health Check Guide — Comprehensive health assessment
• Oracle Troubleshooting Guide — Diagnostic approach by problem type
• Performance Analysis Scripts (171 tools) — Complete script library
• Wait Events Cheat Sheet — Quick wait event reference