This article delves into a new Oracle Database 11g feature that
significantly extends this powerful 10g feature by providing the ability to
implement improved execution plans automatically within DBA-specified
Synopsis. Oracle Database 10g offered Oracle
database administrators the ability to analyze high-volume SQL statements and
then derive and implement more efficient alternative execution plans called SQL
Profiles to insure better query and DML performance. This article delves into a
new Oracle Database 11g Release 1 (11gR1) feature Automatic SQL Tuning that
significantly extends this powerful 10g feature by providing the ability to
implement these improved execution plans automatically within DBA-specified
As a relatively well-seasoned Oracle DBA, Ill admit that I
was extremely skeptical when Oracle announced that Oracle Database 10g would be
offering the capability to detect poorly-performing SQL statements and then
automatically provide a better execution plan for the misbehaving statement.
After all, I knew that even an experienced applications developer with an
excellent knowledge of SQL and PL/SQL coding techniques tended to tweak her SQL
statements with numerous optimizer hints to obtain maximum performance.
However, I was pleasantly surprised with Oracle Database
10gs implementation of the Automatic Tuning Optimizer (ATO), an
expansion of the cost-based optimizer (CBO). When the Automatic Database
Diagnostic Monitor (ADDM) runs whenever an Automatic Workload Repository
(AWR) snapshot has been taken, ADDM detects poorly performing high-volume SQL
statements that could benefit from SQL tuning, and it will recommend analyzing
the statements with Oracle 10gs SQL Tuning Advisor.
The SQL Tuning Advisor (STA) will analyze each submitted
high-volume SQL statement and then offer tuning recommendations in four
Missing Statistics. First, the STA checks if any of the
tables or indexes that the SQL statement is accessing might benefit from statistics
creation or refresh.
Improved Execution Plan. Next, the STA determines if the
SQL statement might benefit from the generation of a SQL Profile. SQL
Profiles are new objects in Oracle Database 10g that provide an alternative to
a SQL statements current execution plan. Similar to its predecessor, the
stored outline, a SQL Profile may rewrite the poorly performing statement by
providing additional optimizer hints, or it partially executes the questionable
SQL statement to gather ancillary statistics to aid in a better execution plan.
The results of these findings are stored within a new SQL Profile specific to
the poorly performing SQL statement, and the next time that the SQL statement
is executed, the newly generated SQL Profile will be used to provide an optimal
execution plan. Best of all, when ADDM detects that a SQL Profile no longer
provides an improved execution plan, it will recommend the regeneration
of the SQL Profile for the SQL statement.
Missing Indexes. Next, the STA
determines if the SQL statement might benefit from an additional (but as yet
non-existent!) index on a column that the statement accesses, it will recommend
the creation of that index. It also generates the SQL statements necessary to
create the index; however, please note that its probably wise to edit the
provided statements so that the index matches the DBAs established naming
conventions for database objects.
Poorly Constructed SQL. Finally, the STA checks the SQL statement
for any obvious (or not-so-obvious!) errors in construction. For example, it
may detect that a missing join predicate is producing an unintended Cartesian
product, or that a type mismatch between two tables is causing significant malperformance
for the SQL statement.
Oracle Database 10g offered the new DBMS_SQLTUNE package that performs these
analyses, records recommendations for improving the SQL statement performance,
offers justifications for the recommendations, and finally offers to implement
the specified recommendations with one simple click of a button (or the
execution of the DBMS_SQLTUNE. ACCEPT_SQL_PROFILE
To illustrate, Ive created some poorly performing SQL
statements as shown in Listing 1.
Ive included the results of the EXPLAIN PLAN analysis for the statements as well.
Next, I used Oracle Database 11gs SQL Tuning Advisor from an Enterprise
Manager session to review the poorly performing SQL statement, as shown in Figure
Figure 1. Executing Oracle Database 11g SQL Tuning
Once its completed analyzing the poorly performing SQL
statement, SQL Tuning Advisor can recommend possible solutions for improving
its performance, as shown in Figure 2 below:
Figure 2. SQL Tuning Advisor Suggestions for Improved SQL
While the ability to generate accurate tuning
recommendations about poorly performing SQL statements was a welcome addition
in Oracle Database 10g, there was one major shortcoming: Even though DBMS_SQLTUNE
could certainly generate SQL Profiles automatically, it did not implement
them unless the DBA specifically took note of the recommended SQL Profiles,
reviewed the detailed justification reasons for its implementation, and then
accepted the SQL Profiles even when it was obvious that the SQL Profile would
have an overwhelmingly positive impact for overall database performance.
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
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
Parallel queries (i.e. that use the PARALLEL optimizer
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
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
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
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
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
Figure 6. Configuring
Automatic SQL Tuning Settings Panel.
Configuring Automatic SQL Tuning With PL/SQL
Oracle Database 11g also
provides the ability to easily modify AST-specific settings via calls to PL/SQL
supplied packages. These are the same parameters that the panel shown in Figure
4 also controls. Procedure DBMS_SQLTUNE.SET_TUNING_TASK_PARAMETER
provides the means to modify numerous AST settings summarized in Table 1
Table 1. Parameters Controlling Automatic SQL Profile
Generation and Implementation
Indicates whether or not
Oracle 11g should automatically accept auto-generated SQL Profiles. The
default is FALSE (i.e. do not accept them).
Specifies the maximum amount
of time in total that the Automatic SQL Tuning process will spend
executing and evaluating SQL statements
Specifies the maximum amount
of time for the Automatic SQL Tuning process to spend on tuning each individual
Tells DBMS_SQLTUNE how to
test and execute candidate SQL statements:
When set to FULL, test-execution is allowed to
continue for up to LOCAL_TIME_LIMIT seconds
When set to AUTO, test-execution continues for an
automatically-determined time thats proportional to the tuning time
When set to OFF, no test-execution is performed
The maximum number of SQL
Profiles that may be created for each run of Automatic SQL Tuning.
The maximum number of
Automatic SQL Profiles that may exist at any time. The default value
The total number of days for
which the task history for the SQL Tuning Advisor run should be saved. The
default duration before the tasks history statement is allowed to be purged
is thirty (30) days.
In addition, several other PL/SQL packaged functions
and procedures provide control mechanisms for Automatic SQL Tuning tasks:
Procedure DBMS_AUTO_TASK_ADMIN.ENABLE permits the DBA to enable or disable SYS_AUTO_SQL_TUNING_TASK, the task that controls the execution of AST.
Procedure DBMS_SQLTUNE.EXECUTE_TUNING_TASK provides the means to manually run the existing AST
task in foreground, just as if it had been run in background during its
normally scheduled execution time.
DBMS_SQLTUNE.REPORT_AUTO_TUNING_TASK function will generate a text-based report that
displays formatted results for a specific run of an AST task.
Implicit AST Deactivation. Finally, please note that
AST will be completely deactivated by default if either:
The STATISTICS_LEVEL initialization parameter has been set to BASIC; or
Generation of AWR snapshots have
been deactivated via DBMS_WORKLOAD_REPOSITORY; or
AWR snapshot retention has been
set to a time period of under seven days.
Automatic SQL Tuning: An Example
To illustrate how AST might benefit the SQL statements in Listing 1
above, Ive created a new user account named LDGN (Load Generator)
and granted that account the DBA role as well as specific object privileges. Ive
also created a new package, LDGN.PKG_LOAD_GENERATOR,
that will execute the SQL statement that I previously identified as a sample of
potentially horrendous performance. These setup activities are shown in Listing 2.
I then used the code shown in Listing 3
to simulate execution of the regularly-scheduled AST task. To insure that I
received a clear recommendation from AST, this code first drops any SQL
Profiles that may have already been created via prior AST task runs for the LDGN schema,
and then also temporarily deactivates the automatic nightly execution of the
Automatic Optimizer Statistics gathering task and the Automatic Segment
Advisor. Once this simulations setup was complete, the code forces open the
current days maintenance window (MONDAY_WINDOW, in this example), and that in turn triggered the
execution of just the AST task. Note that I limited the entire run time of the
AST task to only 30 minutes (1800 seconds) and limited the test-execute of each
individual SQL tuning candidate to only 15 minutes (900 seconds) for the
purposes of more accurate simulations.
Finally, while the AST window remains open, Ill run the
code shown in Listing
4. This code executes the SQL statement shown in Listing 1
several dozen times the same SQL statement that Id earlier identified as an
example of a poorly-performing SQL query. This gave the AST task some
significantly deleterious SQL to digest during its simulated run period.
Viewing Automatic SQL Tuning Results in Enterprise Manager
When the AST task has completed its execution, I can then
review the results of its SQL tuning analysis and determine whether any SQL
Profiles have been generated and automatically implemented. Oracle Database 11g
Enterprise Manager Database Control (EM) provides an excellent interface for
viewing the results of prior AST executions. In Figure 7 below, Ive
shown a sample of the report summary thats typically generated:
Figure 7. Automated SQL
Tuning Task Execution: Summary Report, Part 1.
Each AST task execution provides quite a bit of valuable
Task Status. AST provides basic information about its
current configuration, including:
Whether AST is currently activated
Whether SQL Profiles will be automatically accepted during
an AST run
How many high-value SQL Profiles that have been
automatically accepted during prior AST task runs
Task Activity Summary. This report section allows the
DBA to selectively filter information about the most recent AST task
executions, ranging from just the last execution to all AST executions that
have ever been recorded (and for which AWR statistics are still available).
Overall Task Statistics. This section of the report
is the most valuable, as it breaks down how many individual SQL statements AST
actually evaluated, how many evaluations resulted in a finding for improved
efficiency, and how many evaluations produced no additional tuning benefits.
This section also shows a frequency distribution of the types of
recommendations that the selected AST task(s) actually produced, including
those to create additional indexes, generate or refresh optimizer statistics,
or restructure SQL statements.
Profile Effect Statistics. This final report section
(shown in Figure 8 below) summarizes how well the SQL Profiles that AST
recommended have improved overall database performance as measured by a
positive increase in overall database throughput (i.e. the ability to now
perform the same amount of work in less time and/or with fewer resources than
it took originally).
Figure 8. Automated SQL
Tuning Task Execution: Summary Report, Part 2.
Report Drilldown Capabilities. This summary report is
just the tip of the iceberg, however. By clicking on the View Report button,
AST generates a detailed report of what tuning was performed on a
statement-by-statement basis. As the sample report in Figure 9 below
shows, there are several SQL statements that were found to benefit from the
implementation of a SQL Profile, and AST did implement these profiles
Figure 9. Automated SQL
Tuning: Detail Report.
Its also interesting to note that SQL statements that AST
had deemed would not benefit from a SQL Profile are also shown,
along with the other recommendations that AST found (e.g. a new index, poorly
constructed SQL). The good news here is that the DBA can always return to this
report, drill down to the individual SQL statement, and continue her analysis
of the SQL statement from a central location.
Viewing AST Results Using Data Dictionary Views
Information about Automatic SQL Tuning tasks is also
accessible through numerous data dictionary views, as shown below in Table 2.
Table 2. Data Dictionary Views for Managing Automatic
SQL Tuning Tasks
Displays information about
each AST task execution.
Shows various test vs.
execution statistics that AST generated during the evaluation and testing of
its SQL Profiles.
Lists the SQL Plans
that AST found during its test vs. execution processing.
Lists statistical data
for each automated maintenance task over 7-day and 30-day periods.
Displays a history of job
execution counts for each automated maintenance task.
Shows all currently
executing jobs that are performing automated maintenance tasks, including
limited information about the objects and statistics that the task is
Lists the history of
automated maintenance task job execution. Note that an executed task is only
present here when the task has completed execution.
Shows all automated task
operations for each client.
Lists which maintenance
windows are scheduled for the next 32 days.
information about all current as well as past automated tasks.
Identifies which windows
belong to MAINTENANCE_WINDOW_GROUP, the window group that primarily controls execution
of automated tasks.
Shows the history of
automated tasks window openings and closings.
Oracle Database 11gs new Automatic SQL Tuning (AST)
feature is one of many next logical steps designed to provide aid to
overburdened DBAs in identifying, tuning, and monitoring high-load SQL
statements. AST provides intuitive graphic and PL/SQL interfaces for the
control, maintenance, and monitoring of its myriad tuning activities, and its
a welcome addition to the toolbelt of any overworked DBA thats struggling to
keep poorly-written SQL from overwhelming her databases performance.
References and Additional Reading
While Im hopeful that Ive given you a thorough grounding
in the technical aspects of the features discussed in this article, its just
as likely that there may be better documentation available since its been
published. I therefore strongly suggest that you take a close look at the
corresponding Oracle documentation on these features to obtain crystal-clear
understanding before attempting to implement them in a production environment.
Please note that Ive drawn upon the following Oracle Database 11gR1
documentation for the deeper technical details of this article:
B28274-01 Oracle Database
11gR1 Performance Tuning Guide
B28279-02 Oracle Database
11gR1 New Features Guide
B28320-01 Oracle Database
B28419-02 Oracle Database
11gR1 PL/SQL Packages and Types Reference