SQL Performance Analyzer (SPA) has reported several regressed SQL statements for one of your databases. Which action could help in the performance management of these regressed SQL statements?

Question

Options

• ACreate SQL plan directives for each of the regressed statements.
• BUse The SQL Tuning Advisor for each of the regressed statements.
• CCreate an adaptive execution plan for each of the regressed statements
• DUse the SQL Access Advisor for each of the regressed statements.

• A
  6% (1)
• B
  72% (13)
• C
  17% (3)
• D
  6% (1)

Explanation

SQL Tuning Advisor (B) is correct because it is the designated Oracle tool for analyzing and resolving individual SQL performance issues - including regressions identified by SPA. SPA is explicitly designed to integrate with SQL Tuning Advisor: after identifying regressed statements, you run SQL Tuning Advisor against them to receive actionable recommendations such as SQL profiles, statistics gathering, or query rewrites.

• A is wrong - SQL Plan Directives are created automatically by the database engine when Adaptive Query Optimization detects cardinality misestimates; you do not manually create them as a remediation step for SPA regressions.
• C is wrong - Adaptive execution plans are also generated automatically by the optimizer at runtime; they are not something a DBA creates on demand to fix a regressed statement.
• D is wrong - SQL Access Advisor focuses on physical design recommendations (indexes, materialized views, partitioning) for a workload, not on tuning specific regressed SQL statements identified by SPA.

Memory tip: Think of the workflow as a pipeline - SPA spots the regression, SQL Tuning Advisor fixes it. The word "Tuning" is your clue: when a SQL statement is performing badly, you tune it, so SQL Tuning Advisor is the right tool. SQL Access Advisor = schema access design; SQL Tuning Advisor = individual statement tuning.

No community discussion yet for this question.