Which statement is true about an automatic SQL task?
A. It will attempt to tune the currently running SQL statements that are highly resource intensive.
B. It will automatically implement new SQL profiles for the statements that have existing SQL profiles.
C. It will attempt to tune all-long-running queries that have existing SQL profiles.
D. It will automatically implement SQL profiles if a three-fold benefit can be achieved and automatic profile implementation is enabled.
E. It will tune all the top SQL statements from AWR irrespective of the time it takes to complete the task in a maintenance window.
Correct Answer: D
Explanation/Reference:
Optionally, implements the SQL profiles provided they meet the criteria of threefold performance improvement
The database considers other factors when deciding whether to implement the SQL profile. For example, the database does not implement a profile when the objects referenced in the statement have stale optimizer statistics. SQL profiles that have been implemented automatically show type is AUTO in the DBA_SQL_PROFILES view.
If the database uses SQL plan management, and if a SQL plan baseline exists for the SQL statement, then the database adds a new plan baseline when creating the SQL profile. As a result, the optimizer uses the new plan immediately after profile creation.
Incorrect:
E:Oracle Database automatically runs SQL Tuning Advisor on selected high-load SQL statements from the Automatic Workload Repository (AWR) that qualify as tuning candidates. This task, called Automatic SQL Tuning, runs in the default maintenance windows on a nightly basis. By default, automatic SQL tuning runs for at most one hour.
Note:
After automatic SQL tuning begins, the database performs the following steps:
Oracle Database analyzes statistics in AWR and generates a list of potential SQL statements that are eligible for tuning. These statements include repeating high-load statements that have a significant impact on the database.
The database tunes only SQL statements that have an execution plan with a high potential for improvement. The database ignores recursive SQL and statements that have been tuned recently (in the last month), parallel queries, DML, DDL, and SQL statements with performance problems caused by concurrency issues.
The database orders the SQL statements that are selected as candidates based on their performance impact. The database calculates the impact by summing the CPU time and the I/O times in AWR for the selected statement in the past week.
During the tuning process, the database considers and reports all recommendation types, but it can implement only SQL profiles automatically.
Reference: OracleDatabase Performance Tuning Guide,Automatic SQL Tuning
|
Download Printable PDF. VALID exam to help you PASS.
|
 |
