Performance Tuning and Diagnostics
Performance Tuning and Diagnostics
This domain covers the Oracle diagnostic infrastructure - AWR, ADDM, ASH, wait-event analysis, SQL advisors and plan management - and whether you can read a workload report and act on it correctly.
Core rules
- DB time is the cumulative time foreground sessions spend in the database, CPU plus wait. Average active sessions (AAS) = DB time / elapsed time; an AAS of 2 means roughly two sessions were active on average.
- AWR snapshots default to every 60 minutes with 8 days retention (DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS). AWR requires STATISTICS_LEVEL=TYPICAL or ALL plus the Diagnostics Pack licence.
- ADDM runs automatically after each snapshot pair and reports the top bottleneck with estimated impact and recommendations; it is not a real-time tool.
- ASH samples active sessions about once per second into memory and flushes to DBA_HIST_ACTIVE_SESS_HISTORY, so it answers short-lived spike questions AWR smooths out.
- The SQL Tuning Advisor proposes statistics, SQL profiles, indexes or plan changes; the SQL Access Advisor evaluates index and materialized view candidates for a workload; SQL Plan Management captures accepted plan baselines so plans change only after verification.
- Real-time SQL monitoring (V$SQL_MONITOR) needs STATISTICS_LEVEL=TYPICAL and either parallelism or more than five seconds of execution time.
Worked example
A one-hour snapshot period shows DB time of 7,200 seconds. AAS = 7,200 / 3,600 = 2.0. One statement in the Top SQL section accounts for 3,600 seconds of that DB time, so it alone consumed half the instance's average active capacity. Reasoning: tune that statement first (plan, statistics, or SQL profile via the SQL Tuning Advisor) rather than adjusting SGA size, because the symptom is concentrated in one SQL's elapsed time.
Traps that cost marks
- Reading DB time as wall-clock elapsed time.
- Expecting ADDM to name a problem caused by an event that lasted seconds within the snapshot interval - use ASH.
- Accepting a plan baseline or SQL profile without checking it remains enabled and accepted.