Oracle Database Performance Tuning: AWR, ADDM, Wait Events & SQL Optimization Guide

A query that ran in 3 seconds last quarter now takes 45 seconds. The ERP application is slow every morning between 9 and 11 AM. End-of-day batch is overrunning its window. Users are complaining but nobody knows why. These are the performance problems that Oracle DBAs face in production — and Oracle gives you one of the most comprehensive diagnostic toolkits in the database industry to solve them. This guide covers the complete performance tuning methodology: from reading AWR reports and acting on ADDM recommendations to diagnosing wait events, optimizing SQL execution plans, and tuning memory. The methodology that has solved performance problems in manufacturing ERPs, bank core systems, and pharma production databases.

1. The Right Approach: Top-Down Tuning

The biggest mistake in Oracle performance tuning is starting at the wrong level. Many DBAs jump straight to SQL optimization — rewriting queries, adding indexes — without first understanding what the database is actually waiting for. A query that takes 30 seconds because it is waiting for disk I/O needs a different fix than one taking 30 seconds because of lock contention or a missing index.

The correct top-down methodology:

1. System level: What is the database spending its time on? (AWR, ADDM, wait events)
2. Session level: Which sessions are consuming the most resources?
3. SQL level: Which SQL statements are the biggest consumers?
4. Execution plan level: Are those SQL statements using efficient plans?
5. Schema level: Are indexes, statistics, and data structures optimal?
6. Memory level: Are SGA and PGA sized correctly for the workload?

2. AWR — Automatic Workload Repository

AWR is Oracle's built-in performance snapshot repository. Every hour (configurable), Oracle captures a snapshot of hundreds of system statistics — wait events, SQL execution statistics, memory usage, I/O rates. Comparing two snapshots gives you a performance report for that period.

2.1 Generating AWR Reports

-- Generate AWR HTML report (most useful format — readable in browser)
-- First, find the snapshot IDs for the period of interest
SELECT snap_id,
       TO_CHAR(begin_interval_time,'DD-MON-YYYY HH24:MI') AS snap_time,
       ROUND(snap_interval) AS interval_min
FROM DBA_HIST_SNAPSHOT
WHERE begin_interval_time BETWEEN
  TO_TIMESTAMP('2026-06-04 09:00','YYYY-MM-DD HH24:MI') AND
  TO_TIMESTAMP('2026-06-04 11:00','YYYY-MM-DD HH24:MI')
ORDER BY snap_id;

-- Generate report: replace begin_snap and end_snap with actual IDs
SELECT OUTPUT FROM TABLE(
  DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML(
    l_dbid     => (SELECT dbid FROM V$DATABASE),
    l_inst_num => 1,
    l_bid      => 1234,   -- begin snapshot ID
    l_eid      => 1236    -- end snapshot ID
  )
);

-- Or use the script (easier)
@$ORACLE_HOME/rdbms/admin/awrrpt.sql

2.2 The Five Sections That Matter Most in an AWR Report

Section 1: DB Time and Load Profile

DB Time is the total time all sessions spent doing database work. If DB Time per second is significantly higher than elapsed time, the database is under heavy load. The Load Profile shows transactions per second, logical reads, and physical reads — your baseline metrics.

Section 2: Top 10 Foreground Events

This is the most important section. It tells you what Oracle was waiting for most during the snapshot period. The top wait event explains 70–80% of performance problems.

• db file sequential read: Single-block reads — typically index lookups. High values mean index scans with lots of physical I/O — tune SQL or improve storage speed
• db file scattered read: Multi-block reads — full table scans. Indicates missing indexes or full scans that should be indexed
• log file sync: Waits for LGWR to write redo to disk on COMMIT. High values mean too many small commits, slow redo log storage, or redo log sized too small
• enq: TX - row lock contention: Row-level locking — one session blocking another. Application is holding locks too long
• CPU time: The database is CPU-bound — not waiting, just computing. Usually means sorting, hashing, or high-volume query processing
• buffer busy waits: Multiple sessions competing for the same buffer block — hot block contention, often in sequence generators or frequently-updated index blocks

Section 3: SQL Statistics

Top SQL by elapsed time, CPU, logical reads, physical reads, and executions. The SQL ID of the worst-performing statements is your next diagnostic target.

Section 4: Instance Activity Statistics

Physical reads, logical reads, redo size, user commits, user rollbacks, parse counts. Compare these against your baseline to spot anomalies.

Section 5: Segment Statistics

Top segments by logical reads, physical reads, buffer busy waits. Identifies hot tables and indexes — candidates for partitioning, caching, or I/O optimization.

3. ADDM — Automatic Database Diagnostic Monitor

ADDM analyzes AWR data automatically and produces prioritized recommendations. It is one of the most underused Oracle tools — many DBAs collect AWR data without reading the ADDM findings that Oracle generates from it.

-- View the latest ADDM report
SELECT task_name, description, status,
       TO_CHAR(created,'DD-MON-YYYY HH24:MI') AS created
FROM DBA_ADVISOR_TASKS
WHERE advisor_name = 'ADDM'
ORDER BY created DESC
FETCH FIRST 5 ROWS ONLY;

-- Read ADDM findings with recommendations
SELECT f.impact_type, f.impact,
       f.message AS finding,
       r.message AS recommendation
FROM DBA_ADVISOR_FINDINGS f
JOIN DBA_ADVISOR_RECOMMENDATIONS r
  ON f.task_name = r.task_name
  AND f.finding_id = r.finding_id
WHERE f.task_name = (
  SELECT task_name FROM DBA_ADVISOR_TASKS
  WHERE advisor_name = 'ADDM'
  ORDER BY created DESC
  FETCH FIRST 1 ROW ONLY
)
ORDER BY f.impact DESC;

ADDM findings are ranked by impact — percentage of DB Time attributable to the issue. Focus on findings with impact > 10% first.

3.1 Common ADDM Recommendations and Actions

ADDM Finding	Typical Root Cause	Action
SQL statements consuming significant DB Time	Untuned SQL, missing index	Run SQL Tuning Advisor on the SQL ID
Hard parsing consuming CPU	Literals in SQL (not bind variables)	Set CURSOR_SHARING=FORCE or fix application
I/O related to datafile	Slow storage, hot datafile	Move hot datafile to faster storage tier
Undersized SGA components	Buffer cache or shared pool too small	Increase SGA_TARGET
Excessive log file sync waits	Slow redo logs or too many commits	Move redo logs to faster disk; use batch commits

4. Wait Event Diagnosis

Wait events are Oracle's diagnostic language. When a session cannot proceed — waiting for a lock, a disk read, a network response — Oracle records the wait event and duration. Reading wait events correctly leads directly to the root cause.

4.1 Real-Time Wait Event Monitoring

-- Current wait events across all active sessions
SELECT s.sid, s.username, s.status,
       s.event AS wait_event,
       s.wait_time,
       s.seconds_in_wait,
       s.sql_id,
       SUBSTR(q.sql_text,1,60) AS sql_preview
FROM V$SESSION s
LEFT JOIN V$SQL q ON s.sql_id = q.sql_id
WHERE s.status = 'ACTIVE'
  AND s.username IS NOT NULL
  AND s.event NOT LIKE '%idle%'
ORDER BY s.seconds_in_wait DESC;

4.2 Diagnosing the Most Common Wait Events

db file sequential read (High Physical I/O)

-- Find SQL causing the most physical reads
SELECT sql_id, plan_hash_value,
       disk_reads, executions,
       ROUND(disk_reads/NULLIF(executions,0)) AS reads_per_exec,
       SUBSTR(sql_text,1,80) AS sql_preview
FROM V$SQL
WHERE disk_reads > 10000
ORDER BY disk_reads DESC
FETCH FIRST 20 ROWS ONLY;

log file sync (Slow Commits)

-- Check redo log write performance
SELECT l.group#, l.members, l.bytes/1024/1024 AS size_mb,
       l.status, lf.member AS log_file
FROM V$LOG l
JOIN V$LOGFILE lf ON l.group# = lf.group#
ORDER BY l.group#;

-- Check if redo logs need to be moved to faster storage
-- Average log file sync wait > 5ms = storage issue for redo logs
SELECT event, total_waits,
       ROUND(time_waited/total_waits,2) AS avg_wait_ms
FROM V$SYSTEM_EVENT
WHERE event = 'log file sync';

enq: TX - row lock contention

-- Find who is blocking whom RIGHT NOW
SELECT
  DECODE(request,0,'Blocker','Waiter') AS lock_type,
  sid, type, id1, id2, lmode, request,
  ctime AS seconds_held_or_waited,
  block
FROM V$LOCK
WHERE (id1,id2,type) IN (
  SELECT id1,id2,type FROM V$LOCK WHERE block=1
)
ORDER BY id1, id2, request;

5. SQL Tuning — Finding and Fixing Bad Execution Plans

After identifying the worst-performing SQL from AWR, the next step is diagnosing why it is slow and fixing it.

5.1 Reading an Execution Plan

-- Get the current execution plan for a SQL statement
SELECT * FROM TABLE(
  DBMS_XPLAN.DISPLAY_CURSOR(
    sql_id      => '&sql_id',
    cursor_child_no => 0,
    format      => 'ALLSTATS LAST +PEEKED_BINDS'
  )
);

-- Or explain a query directly
EXPLAIN PLAN FOR
  SELECT c.customer_name, SUM(o.order_value)
  FROM customers c JOIN orders o ON c.customer_id = o.customer_id
  WHERE o.order_date > SYSDATE - 30
  GROUP BY c.customer_name;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());

5.2 What to Look For in an Execution Plan

• FULL TABLE SCAN on large tables: Usually means missing index — check WHERE clause columns
• Estimated rows vs Actual rows very different: Stale statistics — run DBMS_STATS.GATHER_TABLE_STATS
• Nested Loop join on large result sets: Should be Hash Join for large tables — optimizer may have wrong cardinality estimate
• High A-Rows (actual) with low E-Rows (estimated): Statistics are outdated — optimizer made wrong plan choice
• SORT operations without index support: ORDER BY or GROUP BY columns not supported by index — add index or increase PGA

5.3 SQL Tuning Advisor

-- Run SQL Tuning Advisor on a specific SQL ID
DECLARE
  l_task_name VARCHAR2(30);
BEGIN
  l_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
    sql_id      => '&bad_sql_id',
    scope       => DBMS_SQLTUNE.SCOPE_COMPREHENSIVE,
    time_limit  => 60,
    task_name   => 'TUNE_BAD_SQL',
    description => 'Tuning slow query from AWR'
  );
  DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name => 'TUNE_BAD_SQL');
END;
/

-- Read the recommendations
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNE_BAD_SQL')
FROM DUAL;

5.4 SQL Plan Management (SPM) — Preventing Plan Regressions

One of the most damaging Oracle performance issues is a query that was fast for years suddenly becoming slow because the optimizer chose a different plan after statistics refresh or an upgrade. SQL Plan Management prevents this by locking the good plan.

-- Capture a good plan into SQL Plan Baseline
VAR ret NUMBER
BEGIN
  :ret := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(
    sql_id          => '&good_sql_id',
    plan_hash_value => &good_plan_hash
  );
END;
/

-- Verify the baseline was created
SELECT sql_handle, plan_name, enabled, accepted, fixed
FROM DBA_SQL_PLAN_BASELINES
WHERE sql_text LIKE '%your_query_fragment%';

6. Index Optimization

Indexes are the most powerful SQL tuning tool — and the most commonly misused. Too few indexes cause full table scans. Too many indexes slow down DML and consume storage without benefit.

6.1 Finding Missing Indexes

-- Tables with high full scan counts — candidates for index review
SELECT o.object_name AS table_name,
       s.value AS full_scans
FROM V$SEGMENT_STATISTICS s
JOIN DBA_OBJECTS o ON s.obj# = o.object_id
WHERE s.statistic_name = 'table scans (long tables)'
  AND s.value > 500
  AND o.object_type = 'TABLE'
ORDER BY s.value DESC
FETCH FIRST 20 ROWS ONLY;

6.2 Finding Unused Indexes

-- Monitor index usage for 2-4 weeks then check
ALTER INDEX erp.idx_orders_customer MONITORING USAGE;

-- After monitoring period, check which indexes were NOT used
SELECT index_name, table_name, used, monitoring
FROM V$OBJECT_USAGE
WHERE used = 'NO' AND monitoring = 'YES'
ORDER BY table_name, index_name;

6.3 Index Rebuilding

-- Check index fragmentation (rebuild if PCT_USED < 50%)
ANALYZE INDEX erp.idx_transactions_date VALIDATE STRUCTURE;

SELECT name, del_lf_rows, lf_rows,
       ROUND(del_lf_rows/NULLIF(lf_rows,0)*100,1) AS pct_deleted,
       btree_space, used_space,
       ROUND(used_space/btree_space*100,1) AS pct_used
FROM INDEX_STATS;

-- Rebuild fragmented index online (no downtime)
ALTER INDEX erp.idx_transactions_date REBUILD ONLINE;

7. Memory Tuning — SGA and PGA

7.1 SGA Component Sizing

-- Check buffer cache hit ratio (should be > 95% for OLTP)
SELECT
  ROUND((1-(pr.value/(dbg.value+cg.value)))*100,2) AS buffer_hit_pct
FROM V$SYSSTAT pr, V$SYSSTAT dbg, V$SYSSTAT cg
WHERE pr.name='physical reads'
  AND dbg.name='db block gets'
  AND cg.name='consistent gets';

-- Buffer Cache Advisor: recommended size for 95% hit ratio
SELECT size_for_estimate AS cache_mb,
       ROUND(physical_reads_factor,3) AS io_factor,
       ROUND(estd_physical_read_time,0) AS estd_io_time
FROM V$DB_CACHE_ADVICE
WHERE block_size = (SELECT value FROM V$PARAMETER WHERE name='db_block_size')
  AND advice_status = 'ON'
ORDER BY size_for_estimate;

-- Shared Pool sizing
SELECT component, current_size/1024/1024 AS current_mb,
       min_size/1024/1024 AS min_mb,
       last_oper_type AS last_resize
FROM V$SGA_DYNAMIC_COMPONENTS
WHERE component IN ('shared pool','buffer cache','large pool','java pool');

7.2 PGA Tuning

-- Check PGA usage and optimal/one-pass/multi-pass workarea stats
SELECT name, value
FROM V$PGASTAT
WHERE name IN (
  'aggregate PGA target parameter',
  'aggregate PGA auto target',
  'global memory bound',
  'total PGA inuse',
  'total PGA allocated',
  'maximum PGA allocated',
  'cache hit percentage'
);

-- If cache_hit_percentage < 90%, increase PGA_AGGREGATE_TARGET
-- Workarea execution modes (optimal = no disk spill)
SELECT ROUND(optimal_executions/(optimal_executions +
       onepass_executions + multipass_executions)*100,1) AS pct_optimal
FROM (
  SELECT SUM(DECODE(operation_type,'SORT',optimal_executions,0)) AS optimal_executions,
         SUM(DECODE(operation_type,'SORT',onepass_executions,0)) AS onepass_executions,
         SUM(DECODE(operation_type,'SORT',multipasses_executions,0)) AS multipass_executions
  FROM V$SQL_WORKAREA_HISTOGRAM
);

8. Gathering and Managing Statistics

Stale or missing optimizer statistics are responsible for a large percentage of execution plan regressions. A proper statistics strategy is part of performance management.

-- Check tables with stale statistics
SELECT owner, table_name, last_analyzed,
       num_rows, stale_stats
FROM DBA_TAB_STATISTICS
WHERE stale_stats = 'YES'
  AND owner = 'ERP'
ORDER BY last_analyzed NULLS FIRST;

-- Gather statistics with auto-sampling (recommended for most tables)
EXEC DBMS_STATS.GATHER_TABLE_STATS(
  ownname   => 'ERP',
  tabname   => 'TRANSACTIONS',
  cascade   => TRUE,
  degree    => 8,
  method_opt => 'FOR ALL COLUMNS SIZE AUTO'
);

-- For large tables: gather with incremental statistics (partitioned)
EXEC DBMS_STATS.SET_TABLE_PREFS(
  ownname   => 'ERP',
  tabname   => 'TRANSACTIONS',
  pname     => 'INCREMENTAL',
  pvalue    => 'TRUE'
);

9. Real Performance Tuning Cases

Three real cases from my production experience:

Case 1 — Manufacturing ERP Slow Between 9–11 AM

Symptom: ERP application took 20–40 seconds for basic inventory queries every morning. Normal speed by afternoon.

Diagnosis: AWR showed top wait event was buffer busy waits on a specific block in the inventory sequence generator. Multiple users requesting sequence numbers simultaneously were all hitting the same cached block.

Fix: Changed sequence CACHE parameter from 20 to 500. Eliminated the hot block contention. Query time returned to under 1 second.

Case 2 — Bank EOD Batch Overrunning Window

Symptom: End-of-day account posting batch took 6 hours instead of 3 hours, overrunning the business day start.

Diagnosis: ADDM identified a single SQL statement responsible for 45% of DB Time — a balance summary query doing a full table scan on 200 million rows because statistics on the partitioned table were calculated at the partition level but the query was accessing the global table.

Fix: Gathered global statistics with GRANULARITY=>'ALL'. Optimizer chose partition pruning instead of full table scan. Batch reduced from 6 hours to 2.5 hours.

Case 3 — Pharma Production Database Slow After Patch

Symptom: After applying a CPU patch, 3 critical production queries regressed from 2 seconds to over a minute.

Diagnosis: The patch had reset some optimizer parameters to new defaults, causing plan regression. SQL Plan Baselines were not in place for these queries.

Fix: Loaded the pre-patch good plans into SQL Plan Baselines using SQL Plan Management. Queries immediately returned to fast plans. Baseline prevents future plan regressions.

Final Thoughts

Oracle performance tuning is a discipline, not a bag of tricks. The DBA who starts with AWR, reads the top wait events, identifies the highest-impact ADDM findings, and then systematically works down to SQL-level optimization will consistently solve performance problems. The DBA who adds indexes randomly, changes parameters without measurement, and blames the application without looking at the data will spend time firefighting the same problems repeatedly.

The tools Oracle provides — AWR, ADDM, SQL Tuning Advisor, SQL Plan Management — are genuinely excellent. Use them in order, measure before and after every change, and document what worked. Performance tuning without measurement is guessing.