Managing Performance
Scope
This domain tests performance diagnosis with AWR, ADDM, ASH, SQL tuning, execution plans, and memory advisors. Expect questions on which tool answers which question.
Core rules
- AWR stores snapshots in the SYSAUX tablespace; DBMS_WORKLOAD_REPOSITORY manages snapshots and baselines. AWR requires the Diagnostics Pack license. STATISTICS_LEVEL must be TYPICAL or ALL.
- ADDM analyzes AWR snapshots and reports top problems and recommendations. ASH samples active sessions and is useful for short-lived issues; V$ACTIVE_SESSION_HISTORY is the in-memory sample.
- V$SQL, V$SQLSTATS, and DBMS_XPLAN show SQL text, elapsed time, and plans. EXPLAIN PLAN estimates; DBMS_XPLAN.DISPLAY_CURSOR shows the actual child cursor plan.
- Memory advisors include V$SGA_TARGET_ADVICE, V$PGA_TARGET_ADVICE, and V$MEMORY_TARGET_ADVICE. Wait events identify where time is spent.
Worked example
A user reports a slow query. Query V$ACTIVE_SESSION_HISTORY for the SQL_ID to see top wait events, then DBMS_XPLAN.DISPLAY_CURSOR for the plan. ADDM may report a high-load SQL statement from the last AWR snapshot. The fix could be an index, a plan baseline, or more PGA, not necessarily more SGA.
Traps
AWR is not available without the Diagnostics Pack license. ASH is sampled, not a complete trace. EXPLAIN PLAN is an estimate. ADDM is not real-time. A low cost plan is not always the fastest plan.