You notice some performance degradation for a high-load SQL statement in your database. After investigations, you run the SQL Tuning Advisor, which recommends a SQL Profile. You accept the profile recommendation resulting in a new, tuned execution plan for the statement.
Your database uses SQL plan management and a SQL plan baseline exists for this SQL statement.
Which statement is true?
A. The database adds the tuned plan to the SQL plan baseline as a nonfixed plan.
B. The database adds the tuned plan to the SQL plan baseline as a fixed plan.
C. The optimizer uses the new tuned plan only when a reproducible fixed plan is present.
D. The created SQL profile will continuously adapt to all changes made to the database, the object, and to the system statistics over an extended length of time.
Correct Answer: A
Explanation/Reference:
Note:
*When the SQL Tuning Advisor recommends that a SQL Profile be used, you should accept the SQL Profile that is recommended. In cases where the SQL Tuning Advisor recommends that an index and a SQL Profile be used, both should be used. You can use the DBMS_SQLTUNE.ACCEPT_SQL_PROFILE procedure to accept a SQL Profile recommended by the SQL Tuning Advisor. This creates and stores a SQL Profile in the database.
*When tuning SQL statements with the SQL Tuning Advisor, if the advisor finds a tuned plan and verifies its performance to be better than a plan chosen from the corresponding SQL plan baseline, it makes a recommendation to accept a SQL profile. When the SQL profile is accepted, the tuned plan is added to the corresponding SQL plan baseline.
*If SQL plan management is used and there is already an existing plan baseline for the SQL statement, a new plan baseline will be added when a SQL profile is created.
*SQL plan management is a preventative mechanism that records and evaluates the execution plans of SQL statements over time, and builds SQL plan baselines composed of a set of existing plans known to be efficient. The SQL plan baselines are then used to preserve performance of corresponding SQL statements, regardless of changes occurring in the system.
*SQL plan baseline is fixed if it contains at least one enabled plan whose FIXED attribute is set to
*ACCEPT_SQL_PROFILE Procedure and Function
This procedure creates a SQL Profile recommended by the SQL Tuning Advisor. The SQL text is normalized for matching purposes though it is stored in the data dictionary in de-normalized form for readability.
|
Download Printable PDF. VALID exam to help you PASS.
|
 |
