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?
A. Use DBMS_MVIEW.EXPLAIN_REWRITE to analyze whether the queries are rewritten.
B. USE DBMS_ADVISOR.QUICK_TUNE to analyze the query rewrite usage of materialized views for the entire workload.
C. Create an STS for all the queries and use SQL performance Analyzer to generate recommendations for determining the regressed SQL statements.
D. Create an STS for all the queries in the application and use the SQL Tuning Advisor to generate recommendations.
E. Create an STS for all the queries in the application and use the SQL Access Advisor to generate a recommendation for optimizing materialized views for maximum query rewrite usage and fast refresh.
Correct Answer: BD
Explanation/Reference:
B:
*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.
Note:
* 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.
Incorrect:
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.
|
Download Printable PDF. VALID exam to help you PASS.
|
 |
