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

Your database supports a DSS workload. In an application, a few complex queries that contain multiple functions and expressions are using materialized views.
You notice that some queries are performing poorly because they are not benefiting from query rewrites.
Which three actions would you take to improve the performance of queries? (Choose three.)

Answer options

• A. Create an SQL Tuning Set (STS) and submit as input to the SQL Access Advisor to generate recommendations about query rewrite and fast refresh for materialized views.
• B. Use the DBMS_MVIEW.EXPLAIN_REWRITE procedure to analyze why a query failed to rewrite.
• C. Create an STS and submit as input to the SQL Performance Analyzer to get recommendations about improving the performance of queries.
• D. Use the DBMS_ADVISOR.TUNE_MVIEW procedure to get recommendations about rewriting materialized views.
• E. Use the DBMS_ADVISOR.QUICK_TUNE procedure to analyze queries based on the usage of query rewrite with materialized views.

Correct answer: A, C, E

Explanation

Option A is correct because creating an SQL Tuning Set and using the SQL Access Advisor can help identify opportunities for query rewrites and optimize materialized views for faster refresh. Option C is also valid as the SQL Performance Analyzer can provide insights for improving query performance. Option E is correct since the DBMS_ADVISOR.QUICK_TUNE procedure can analyze query usage with materialized views. Options B and D do not directly enhance performance but rather provide analysis or tuning recommendations without addressing the immediate performance issue.