Oracle
1Z0-117 · Question #67
1Z0-117 Question #67: Real Exam Question with Answer & Explanation
The correct answer is B. USE DBMS_ADVISOR.QUICK_TUNE to analyze the query rewrite usage of materialized views for the D. Create an STS for all the queries in the application and use the SQL Tuning Advisor to generate. The dbms_advisor package has a procedure called dbms_advisor.quick_tune that allows the DBA to quickly tune a single SQL statement with a single procedure call. This procedure performs all of the stages that are necessary to launch the SQLAccess Advisor, e.g. creating a task, cre
Question
You are administering a database that supports a DSS workload, where in an application a set of queries use the query rewrite on materialized views. You notice that these queries are performing poorly. Which two actions can you make to improve the performance of theses queries?
Options
- AUse DBMS_MVIEW.EXPLAIN_REWRITE to analyze whether the queries are rewritten.
- BUSE DBMS_ADVISOR.QUICK_TUNE to analyze the query rewrite usage of materialized views for the
- CCreate an STS for all the queries and use SQL performance Analyzer to generate recommendations
- DCreate an STS for all the queries in the application and use the SQL Tuning Advisor to generate
- ECreate an STS for all the queries in the application and use the SQL Access Advisor to generate a
Explanation
- The dbms_advisor package has a procedure called dbms_advisor.quick_tune that allows the DBA to quickly tune a single SQL statement with a single procedure call. This procedure performs all of the stages that are necessary to launch the SQLAccess Advisor, e.g. creating a task, creating and populating a workload, and executing the task. * QUICK_TUNE Procedure This procedure performs an analysis and generates recommendations for a single SQL statement. This provides a shortcut method of all necessary operations to analyze the specified SQL statement. The operation creates a task using the specified task name. The task will be created using a specified Advisor task template. Finally, the task will be executed and the results will be saved in the repository. * DSS - decision support system * In tuning mode, the optimizer performs additional analysis to check whether the execution plan produced under normal mode can be improved further. The output of the query optimizer is not an execution plan, but a series of actions, along with their rationale and expected benefit for producing a significantly superior plan. When running in the tuning mode, the optimizer is referred to as the Automatic Tuning Optimizer. A: DBMS_MVIEW.EXPLAIN_REWRITE This procedure enables you to learn why a query failed to rewrite, or, if it rewrites, which materialized views will be used. Using the results from the procedure, you can take the appropriate action needed to make a query rewrite if at all possible. The query specified in the EXPLAIN_REWRITE statement is never actually executed.
Community Discussion
No community discussion yet for this question.