Quick Oracle DBA Guide: How to Pinpoint Database Hangs and Slowdowns

Investigating Slow Database

Determine whether the slowdown is continuous or occurs only during specific time windows.

If continuous

Collect an ADDM report, review the findings and follow the recommendations.

Collect AWR and ASH reports for the problematic interval (typically one hour; for many issues a 10‑30 minute AWR may be sufficient).

Collect a baseline AWR report from a period of normal performance, preferably at the same time of day or under the same workload.

If intermittent

Collect an ADDM report that covers the problem window.

Collect matching AWR and ASH reports for that window.

Collect a baseline AWR report as described above.

High‑CPU Sessions

When CPU usage is unusually high, capture error stacks (or pstack) for the offending processes.

SELECT p.pid, p.SPID, s.SID
FROM v$process p, v$session s
WHERE s.paddr = p.addr
AND s.SID = &SID;
connect / as sysdba
ALTER SESSION SET tracefile_identifier = 'STACK_10046';
oradebug setospid 9834
oradebug unlimit
oradebug event 10046 trace name context forever,level 12
oradebug dump errorstack 3
oradebug tracefile_name

Reference: Oracle Doc 1364257.1 “How to Collect Errorstacks for Diagnosing Performance Issues”.

Session‑Level Diagnosis

Identify whether one, several, or all sessions are affected and collect 10046 trace logs accordingly.

One session: collect its 10046 trace.

Multiple sessions: collect traces for one or two problematic sessions.

All sessions: repeat the ADDM/AWR/ASH collection steps.

Investigating Database Hangs

Determine whether a single session, a single SQL statement, multiple sessions, or the entire database is hanging.

Single session hang

Collect the session's 10046 trace and error stacks.

Collect AWR and ASH reports for the hang interval and a normal‑performance baseline.

Single SQL hang

Collect the 10046 trace for the session executing the SQL.

Collect error stacks for high‑CPU processes.

Collect AWR/ASH for the interval and a baseline.

Reference: Oracle Doc 1916479.1 “How to Investigate Hanging SQL Statements”.

Multiple sessions hang

Collect 10046 traces for the affected sessions.

Collect error stacks, AWR, and ASH as above.

Whole‑database hang

Follow the same steps as for “all sessions” in the slow‑database section.

For true hangs, use Oracle hang‑analysis documents (Doc 2293066.1, Doc 452358.1). If the database appears hung but is actually “spinning” on CPU, consult Doc 68738.1.

Hang analysis commands

sqlplus '/ as sysdba'
oradebug setmypid
oradebug unlimit
oradebug hanganalyze 3
-- wait ~10 seconds
oradebug hanganalyze 3
oradebug tracefile_name
exit