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.
