Performance Monitoring and SQL Tuning
This domain tests using AWR, ASH, and the advisors to find high-load SQL and improve its execution plan without guessing. Know which tool produces which output and which recommendation changes the plan.
Monitoring
AWR snapshots are collected by MMON when STATISTICS_LEVEL is TYPICAL or ALL and are stored in DBA_HIST_* views; baselines preserve a snapshot range for comparison. ASH samples active sessions about once per second into V$ACTIVE_SESSION_HISTORY, making it useful for transient bottlenecks. ADDM analyzes AWR data and reports findings. Real-time SQL monitoring appears in V$SQL_MONITOR for long-running or parallel statements. DBMS_XPLAN displays plans, including actual plans from cursor cache.
SQL tuning
Identify top SQL by elapsed time, CPU, or I/O. SQL Tuning Advisor, invoked through DBMS_SQLTUNE or Enterprise Manager, can recommend statistics refresh, a SQL profile, an index, or a restructured statement. SQL profiles supply optimizer corrections without locking a plan. SQL Plan Management uses SQL plan baselines to prevent plan regressions by accepting only verified plans; DBMS_SPM loads and evolves baselines. SQL Access Advisor advises on indexes, materialized views, and partitioning for a workload.
Worked example
An AWR report shows one SELECT consuming 40% of DB time. Create a SQL tuning set containing the statement, run SQL Tuning Advisor, and review its findings. If it recommends a SQL profile, accept it with DBMS_SQLTUNE.ACCEPT_SQL_PROFILE, then verify the new plan using DBMS_XPLAN.DISPLAY. If the statement already has a good plan that regressed after a stats refresh, create and fix a SQL plan baseline instead of relying on the profile.
Traps
A SQL profile is not a plan baseline: a profile adjusts optimizer estimates, while a baseline restricts the accepted plan set. EXPLAIN PLAN does not execute the statement, so it may not reflect runtime row counts. Stale or missing statistics mislead every advisor. ASH is a sampled history, not a complete record of every session.