Which type of SQL statement would be selected for tuning by the automatic SQL framework?
A. Serial queries that are among the costliest in any or all of the four categories: the past week, any day in the past week, any hour in the past week, or single response, and have the potential for improvement
B. Serial queries that have been tuned within the last 30days and have been SQL profiled by the SQL tuning Advisor.
C. Serial and parallel queries that top the AWR Top SQL in the past week only and have been SQL profiled by the SQL Tuning Advisor.
D. Serial queries that top the AWR Top SQL in the past week only and whose poor performance can be traced to concurrency issues.
E. Serial and parallel queries that are among the costliest in any or all of the four categories: the past week, and day in the past week, any hour in the past week, or a single response, and that can benefit from access method changes.
Correct Answer: E
Explanation/Reference:
The Automatic Tuning Optimizer is meant to be used for complex and high-load SQL statements that have non-trivial impact on the entire system. The Automatic Database Diagnostic Monitor (ADDM) proactively identifies high-load SQL statements which are good candidates for SQL tuning.
Note:
*When SQL statements are executed by the Oracle database, the query optimizer is used to generate the execution plans of the SQL statements. The query optimizer operates in two modes: a normal mode and a tuning mode.
In normal mode, the optimizer compiles the SQL and generates an execution plan. The normal mode of the optimizer generates a reasonable execution plan for the vast majority of SQL statements. Under normal mode, the optimizer operates with very strict time constraints, usually a fraction of a second, during which it must find a good execution plan.
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.
Reference: OracleDatabase Performance Tuning Guide,Automatic SQL Tuning
|
Download Printable PDF. VALID exam to help you PASS.
|
 |
