Performance Monitoring and Advisory Framework
This domain tests the built-in instrumentation: dynamic performance views, the Automatic Workload Repository, ADDM, Active Session History, and the advisors that interpret the collected data.
The data layer
V$ views are dynamic performance views built on X$ fixed tables in memory; GV$ views add RAC instance columns. AWR snapshots are captured by MMON, by default every 60 minutes and retained for 8 days, and are stored in the DBA_HIST_* views. Baselines preserve a snapshot range for later comparison. ADDM runs automatically after each snapshot pair and reports findings ranked by impact with recommendations. ASH samples active sessions once per second into V$ACTIVE_SESSION_HISTORY, roughly the last hour in memory, and persists to DBA_HIST_ACTIVE_SESS_HISTORY.
Reading waits
DB time is CPU time plus wait time. Top timed events come from V$SYSTEM_EVENT and V$SESSION_EVENT. db file sequential read indicates single-block I/O, typically index access; db file scattered read indicates multiblock reads, typically full scans; log file sync indicates commit-related waiting.
Advisors
SQL Tuning Advisor, SQL Access Advisor, Memory Advisor, MTTR Advisor, and Segment Advisor consume the collected data and produce recommendations. Enterprise Manager Express provides a lightweight browser interface.
Worked example
Users report a two-hour slowdown. An AWR report shows db file sequential read as the top foreground event and one SQL_ID accounting for most of DB time. Reasoning: DB time is dominated by a single-block read wait from one statement. That pattern usually means an index-driven plan executing many round trips, so inspect the execution plan, the index clustering factor, and whether a full table scan would be cheaper.
Traps that cost marks
- Assuming the AWR retention and interval defaults are something other than 60 minutes and 8 days.
- Forgetting that reading V$ views requires a privilege such as SELECT ANY DICTIONARY or SELECT_CATALOG_ROLE, and that AWR reports can require the Diagnostics and Tuning Packs.
- Blaming the storage subsystem for log file sync waits caused by excessive commit frequency.