Identify the activities performed as part of the Automatic SQL Tuning process in the maintenance window?
(Choose all that apply.)
A.
generating the SQL profile
B.
testing and accepting the SQL profile
C.
generating a list of candidate SQLs for tuning
D.
adding tuned SQL plans into the SQL plan baseline
E.
tuning each SQL statement in the order of importance
F.
generating baselines that include candidate SQLs for tuning
Explanation:
17.2.1 How Automatic SQL Tuning Works (link)
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. You can customize attributes of the maintenance windows, including start and end time,
frequency, and days of the week.
After automatic SQL tuning begins, the database performs the following steps:
1. Identifies SQL candidates in the AWR for tuning
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.
2. Tunes each SQL statement individually by calling SQL Tuning Advisor
During the tuning process, the database considers and reports all recommendation types, but it can implement
only SQL profiles automatically.
3. Tests SQL profiles by executing the SQL statement
If a SQL profile is recommended, the database tests the new profile by executing the SQL statement both with
and without the profile. If the performance improvement improves at least threefold, then the database accepts
the SQL profile, but only if the ACCEPT_SQL_PROFILES task parameter is set to TRUE. Otherwise, the
automatic SQL tuning reports merely report the recommendation to create a SQL profile.
4. 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. See Chapter 15, “Using SQL Plan Management”. At any time during or after
the automatic SQL tuning process, you can view the results using the automatic SQL tuning report. This report
describes in detail all the SQL statements that were analyzed, the recommendations generated, and the SQL
profiles that were automatically implemented.
