Which is the source used by Automatic SQL Tuning that runs as part of the AUTOTASK
framework?
A.
SQL statements that are part of the AWR baseline only
B.
SQL statements based on the AWR top SQL identification
C.
SQL statements that are part of the available SQL Tuning Set (STS) only
D.
SQL statements that are available in the cursor cache and executed by a user other than SYS
Input for SQL Tuning Advisor can come from several sources, including the following:
ADDM (Automatic Database Diagnostic Monitor)
The primary input source is ADDM. By default, ADDM runs proactively once every hour and analyzes key statistics gathered by the Automatic Workload Repository (AWR) over the last hour to identify any performance problems including high-load SQL statements. If a high-load SQL is identified, ADDM recommends running SQL Tuning Advisor on the SQL. See “Overview of the Automatic Database Diagnostic Monitor”.
AWR
The second most important input source is the Automatic Workload Repository (AWR). AWR takes regular snapshots of system activity, including high-load SQL statements ranked by relevant statistics, such as CPU consumption and wait time.
You can view the AWR and manually identify high-load SQL statements. You can run SQL Tuning Advisor on these statements, although Oracle Database automatically performs this work as part of automatic SQL tuning. By default, AWR retains data for the last eight days. You can locate and tune any high-load SQL that ran within the retention period of AWR using this method. See “Overview of the Automatic Workload Repository”.
Shared SQL area
The third likely source of input is the shared SQL area. The database uses this source to tune recent SQL statements that have yet to be captured in the AWR. The shared SQL area and AWR provide the capability to identify and tune high-load SQL statements from the current time going as far back as the AWR retention allows, which by default is at least 8 days.
SQL tuning set
Another possible input source for SQL Tuning Advisor is the SQL tuning set. A SQL tuning set (STS) is a database object that stores SQL statements along with their execution context. An STS can include SQL statements that are yet to be deployed, with the goal of measuring their individual performance, or identifying the ones whose performance falls short of expectation. When a set of SQL statements serve as input, the database must first construct and use an STS