Automatic SQL Tuning: Overview
Oracle Database 11g overcomes this shortcoming by expanding
the impact of SQL Profile generation with a brand-new feature called Automatic
SQL Tuning (AST). AST uses a few simple rules to determine if a SQL Profile
should be automatically implemented:
Step 1: Identify Candidate SQL Statements. AST first
interrogates the Automatic Workload Repository (AWR) to find high-load SQL
statements that have been executed repeatedly and have a significant impact on
the system. Using the following rules of precedence, AST captures SQL
statements that resulted in:
-
The highest load during the past
week
-
The highest load during any
one day in the past week
-
The highest load during any
one hour in the past week
-
The highest load when measured
within an average single execution
AST only looks for up to 150 statements per each of these
categories, and then it assigns weights to each SQL statement captured so that
the statements that would most benefit from Automatic SQL Tuning are placed
highest in the list.
Only those SQL statements that have an execution plan with a
high potential for improvement will be tuned, so its important to recognize
that AST wont even consider the following SQL statements as candidates for
Automatic SQL Tuning:
-
Recursive SQL statements (i.e. those that result in additional SQL
statements being generated, such as those which require additional calls to
allocate space during an INSERT)
-
Recently-tuned statements (i.e. those that have been tuned within the last 30
days)
-
Parallel queries (i.e. that use the PARALLEL optimizer
hint)
-
DML and DDL statements (including INSERT INTO
SELECT FROM and CREATE TABLE
AS SELECT statements)
-
SQL statements whose poor
performance can be traced to concurrency issues
-
SQL statements that have been
executed on an ad hoc basis, or only executed a few times within
the default evaluation period of one week
-
Long-running queries (after a profile has been generated). For example,
if a SQL query is taking too long to run even after a SQL Profile has been made
available, the Automatic Tuning Advisor will ignore the statement because its
simply not practical to test-execute it. (Of course, if AST locates a SQL
Profile that helps a long running query to run in a few minutes instead of
several hours, AST may still determine that test-execution is possible. In this
case AST would run the query long enough to prove that query takes longer to run
with the original execution plan instead of the new plan, and then it would
implement the new plan instead.)
Step 2: Generate SQL Profiles for Candidate Statements. Next,
while AST is executing within its maintenance window, it automatically tunes
each identified candidate SQL statement by invoking the SQL Tuning
Advisor, creating a SQL Profile if one is needed, and then executing
the SQL statement to test it against the newly-generated SQL Profile. (AST
execution may indeed result in additional recommendations, such as the
suggestion to generate missing statistics or refresh stale optimizer
statistics, create new indexes, or restructure SQL, but these recommendations
will simply be retained for later DBA review and possible implementation in the
future. Also, its important to note that AST generates all SQL profiles using
the standard setting of EXACT for the
CURSOR_SHARING
initialization parameter.)
However, before Oracle Database 11g will even consider a SQL
Profile for automatic implementation, it must determine that the benefit
derived result in performance is at least three times greater than the
current SQL statements performance. This measurement is based on the following
formula:
Benefit Percentage =
(Original Execution Time - New Execution Time)
-----------------------------------------------
Old Execution Time
For example, if a SQL statement executed for 200 seconds
without the recommended SQL Profile, but only took 60 seconds to execute with
the recommended SQL Profile in place, then the benefit would be (200 60) /
200, or 70%. In addition, any performance increase is based on the sum of CPU
time and I/O time; if AST detects an actual degradation in either of these
statistics, then AST will reject the decision to implement the suggested SQL
Profile.
Step 3: Verification of Generated SQL Profiles.
Because AST is scheduled to run automatically, the DBA can run a report against
ASTs suggestions for tuning SQL statements. The DBA can then validate that the
SQL Profiles were generated and automatically implemented are desirable
changes, and can either allow the SQL Profiles to remain in place, or simply
remove them entirely.
Configuring Automatic SQL Tuning With Enterprise Manager
Oracle Database 11gs
implementation of Enterprise Manager provides an intuitive interface for
modification of the parameters that control AST-specific settings, including
the scheduled frequency of how often the AST task will execute. To access this
interface, Ive started an Enterprise Manager session and then navigated to the
Server
panel, and then selected the Automatic SQL Tuning Settings breadcrumb
under the Oracle
Scheduler section as shown in Figure 3 below:
Figure 3. Oracle Database 11g
Enterprise Manager: Server Panel.
Once Ive selected that option,
EM displays the Automated
Maintenance Tasks panel that shows what automated tasks,
including Automatic SQL Tuning, have either run recently or are ready to run
within the next selected time frame. The range of displayed tasks can be
controlled with the drop-down lists near the top of the panel, as shown below
in Figure 4.
Figure 4. Automated
Maintenance Tasks Panel.
I can access information about
the Automatic SQL Tuning task by simply clicking on the link to that task.
Oracle Database 11g Enterprise Manager then displays the Automated Maintenance
Tasks Configuration panel, which displays extremely detailed
information about when the next automated scheduled tasks will run based on the
standard or customized schedules that the DBA has specified for each task. An
example of this panel is shown in Figure 5.
Figure 5. Automated
Maintenance Tasks Configuration Panel.
Finally, Oracle Database 11g
Enterprise Manager also provides the DBA with the ability to modify AST
settings with fine precision. Clicking on the Configure button from the prior panel
brings into focus the Automatic SQL Tuning Settings panel (Figure
6) from which here the DBA can activate, deactivate, set, or reset several
AST-specific settings.
Figure 6. Configuring
Automatic SQL Tuning Settings Panel.