Oracle Database 12c: Performance Management and Tuning — Question 17

Your database supports a mixed workload. In an application, multiple complex queries with functions and expressions are executing. You want to analyze the queries that are currently cached in the library cache to receive recommendations about the usage of indexes and materialized views.
What should you do to achieve this? (Choose the best answer.)

Answer options

• A. Create an STS for the queries cached in the library cache and submit it as an input to SQL Tuning Advisor.
• B. Create an STS for the queries cached in the library cache and submit it as an input to SQL Access Advisor.
• C. Capture the workload in an STS and submit to SQL Tuning Advisor for recommendations.
• D. Create an STS for the queries cached in the library cache and submit it as an input to SQL Performance Analyzer.

Correct answer: D

Explanation

The correct answer is D because the SQL Performance Analyzer is specifically designed to evaluate the performance of SQL statements under different conditions and provide insights about index and materialized view usage. Options A and B are incorrect because they refer to advisors that focus on tuning and access paths rather than performance analysis. Option C is also incorrect as it does not specify the library cache context, which is crucial for this scenario.