Instance Tuning: AWR, ASH, ADDM and Wait Events

Reading an AWR report: Load Profile → Top 10 Foreground Events → Time Model → SQL ordered by Elapsed/CPU/Gets/Reads/Executions/Parse Calls → Instance Efficiency → Segment Statistics → I/O Stats → Advisory sections (buffer cache, PGA, SGA).

51.1 Time Model and Wait Interface

DB Time = CPU time + non-idle wait time across all foreground sessions. The goal is to reduce DB Time.

V$SYS_TIME_MODEL, V$SESS_TIME_MODEL, V$SYSTEM_EVENT, V$SESSION_EVENT, V$SESSION_WAIT, V$EVENT_HISTOGRAM, V$WAITCLASSMETRIC.

Wait classes: User I/O, System I/O, Concurrency, Commit, Configuration, Application, Network, Cluster, Administrative, Idle.

51.2 Common Wait Events

Meaning / Typical Fix db file sequential read Single-block (index) reads. Tune SQL, indexes, clustering factor, I/O.

db file scattered read Multiblock full scans. Tune SQL, indexes, partitioning.

direct path read / write (temp) Serial/parallel direct reads, sorts and hashes spilling to TEMP log file sync

Commit waits. Commit less often, faster redo storage.

log file parallel write LGWR I/O speed buffer busy waits / read by other session

Hot blocks. Partition, use reverse keys, adjust ASSM/INITRANS.

enq: TX - row lock contention Application row locking (blocking sessions) enq: TM - contention Unindexed foreign keys enq: TX - index contention

Right-hand index growth. Reverse key, hash partitioning.

latch: shared pool / library cache: mutex X / cursor: pin S wait on X Hard parsing. Bind variables.

latch: cache buffers chains Hot blocks, inefficient SQL with high logical reads free buffer waits

DBWn can't keep up. Check I/O and buffer cache size.

log buffer space Redo log buffer too small, slow LGWR gc cr/current block busy/2-way/3-way RAC interconnect / global cache resmgr:cpu quantum Throttled by Resource Manager SQL*Net message from client Idle (or chatty applications)

51.3 AWR (Automatic Workload Repository)

Hourly snapshots (default), 8-day retention (default), stored in SYSAUX.

Statspack: the free alternative when you don't have the Diagnostics Pack.

EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT;
EXEC DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(retention => 43200, interval => 30);   -- minutes
SELECT snap_id, begin_interval_time FROM dba_hist_snapshot ORDER BY snap_id DESC;
-- Reports
@?/rdbms/admin/awrrpt.sql      -- AWR report
@?/rdbms/admin/awrddrpt.sql    -- compare periods
@?/rdbms/admin/awrsqrpt.sql    -- single SQL
@?/rdbms/admin/awrgrpt.sql     -- RAC global
-- Baselines: DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE

51.4 ASH (Active Session History)

Samples active sessions every second (V$ACTIVE_SESSION_HISTORY). Every 10th sample is persisted to DBA_HIST_ACTIVE_SESS_HISTORY.

SELECT sql_id, event, COUNT(*) samples
FROM v$active_session_history
WHERE sample_time > SYSDATE - 15/1440
GROUP BY sql_id, event ORDER BY samples DESC FETCH FIRST 10 ROWS ONLY;
@?/rdbms/admin/ashrpt.sql

51.5 ADDM and Advisors

ADDM (Automatic Database Diagnostic Monitor) analyzes each pair of AWR snapshots and recommends fixes. Real-time ADDM. Compare-period ADDM.

Advisors: SQL Tuning, SQL Access, Memory (SGA/PGA/buffer cache), Segment, Undo, MTTR, Compression (DBMS_COMPRESSION).

@?/rdbms/admin/addmrpt.sql
SELECT * FROM dba_advisor_findings WHERE task_name = '...';

51.6 Session-Level Diagnosis

Hang analysis: oradebug hanganalyze, V$WAIT_CHAINS, Hang Manager.

SELECT s.sid, s.serial#, s.username, s.status, s.sql_id, s.event, s.wait_class, s.seconds_in_wait,
       s.blocking_session, s.module, s.action, s.machine, s.program
FROM v$session s WHERE s.type = 'USER' AND s.status = 'ACTIVE';

SELECT sql_id, executions, elapsed_time/1e6 secs, cpu_time/1e6 cpu, buffer_gets, disk_reads, rows_processed, sql_text
FROM v$sqlstats ORDER BY elapsed_time DESC FETCH FIRST 10 ROWS ONLY;

SELECT * FROM v$session_longops WHERE sofar <> totalwork;
SELECT * FROM v$sql_monitor WHERE status = 'EXECUTING';