Note
AWR and ASH need the Oracle Diagnostics Pack licence. Without it, use Statspack.
1. Generate the report for the problem window
-- Snapshots around the slow period
SELECT snap_id, begin_interval_time FROM dba_hist_snapshot
WHERE begin_interval_time > SYSDATE - 1 ORDER BY snap_id;
@?/rdbms/admin/awrrpt.sql -- single instance (RAC: awrgrpt.sql)
@?/rdbms/admin/ashrpt.sql -- for a short spike (minutes)
Also generate one for the same hours on a normal day. Comparing is much easier than reading one in isolation (awrddrpt.sql creates a diff report).
2. Is the database busy at all?
In the header: DB Time vs Elapsed.
- 60 minutes elapsed, 30 minutes DB time → about half an active session on average. The database is mostly idle; the slowness may be in the application or the network.
- 60 minutes elapsed, 1,200 minutes DB time on a 16-core box → 20 active sessions on 16 cores. Busy.
3. Top 10 foreground events
The most important section. The top event by % DB time says what sessions waited on:
| Top event | Usually means | Look at |
|---|---|---|
DB CPU | CPU-bound SQL (often logical reads) | SQL ordered by CPU / gets |
db file sequential read | Single-block reads (index lookups) | SQL ordered by physical reads; storage latency (avg wait > 10 ms is slow) |
db file scattered read / direct path read | Full scans | Missing indexes, or expected for reports |
log file sync | Commit waits | Too many commits, or slow redo log disks |
enq: TX - row lock contention | Sessions blocking each other | Application logic; the blocking SQL in ASH |
gc ... events (RAC) | Inter-node block shipping | Interconnect and application partitioning |
latch: shared pool, library cache | Hard parsing | SQL without bind variables |
4. Load profile
Per-second numbers: logical reads, physical reads, redo size, hard parses, executes, transactions. Compare with the normal day. "Hard parses jumped from 5/s to 400/s" is a strong clue (a new release using literals).
5. SQL statistics
SQL ordered by Elapsed Time / CPU / Gets / Reads. Note the top 3–5 SQL_IDs. Then:
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_AWR('7b2twsn8vgfsc'));
Several plan hash values for one SQL_ID → a plan change. Fix it with a SQL plan baseline or SQL profile, after checking statistics.
6. Instance efficiency and memory advisories
Buffer cache and PGA advisories show whether more memory would reduce reads. Small expected gains = don't buy memory.
Write the conclusion like this
"Between 10:00 and 11:00 DB time was 4× normal. 62% was
db file sequential readat 28 ms average (normal 3 ms), mostly from SQL_ID 7b2twsn8vgfsc. Storage latency increased; raised with the storage team."
Tip
Customers trust a finding with numbers and a comparison. Always attach the normal-day report next to the bad one.