Oracle Database 11g: Automatic SQL Tuning

Wednesday Sep 26th 2007 by Jim Czuprynski

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 boundaries.

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 boundaries.

As a relatively well-seasoned Oracle DBA, I’ll 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 10g’s 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 10g’s SQL Tuning Advisor.

The SQL Tuning Advisor (STA) will analyze each submitted high-volume SQL statement and then offer tuning recommendations in four different areas:

  • 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 statement’s 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 it’s probably wise to edit the provided statements so that the index matches the DBA’s 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 procedure).

To illustrate, I’ve created some poorly performing SQL statements as shown in Listing 1. I’ve included the results of the EXPLAIN PLAN analysis for the statements as well. Next, I used Oracle Database 11g’s SQL Tuning Advisor from an Enterprise Manager session to review the poorly performing SQL statement, as shown in Figure 1 below:

Figure 1. Executing Oracle Database 11g SQL Tuning Advisor.

Once it’s 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 Profile.

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 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 it’s important to recognize that AST won’t 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 it’s 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, it’s 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 statement’s 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 AST’s 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 11g’s 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, I’ve 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 I’ve 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.

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 below:

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 SQL statement


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 that’s 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 is zero.


The total number of days for which the task history for the SQL Tuning Advisor run should be saved. The default duration before the task’s 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, I’ve created a new user account named LDGN (Load Generator) and granted that account the DBA role as well as specific object privileges. I’ve 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 simulation’s setup was complete, the code forces open the current day’s 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, I’ll 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 I’d 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, I’ve shown a sample of the report summary that’s typically generated:

Figure 7. Automated SQL Tuning Task Execution: Summary Report, Part 1.

Each AST task execution provides quite a bit of valuable information:

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 automatically.

Figure 9. Automated SQL Tuning: Detail Report.

It’s 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 currently utilizing.


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.


Contains detailed 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 11g’s 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 it’s a welcome addition to the toolbelt of any overworked DBA that’s struggling to keep poorly-written SQL from overwhelming her database’s performance.

References and Additional Reading

While I’m hopeful that I’ve given you a thorough grounding in the technical aspects of the features discussed in this article, it’s just as likely that there may be better documentation available since it’s 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 I’ve 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 11gR1 Reference

B28419-02 Oracle Database 11gR1 PL/SQL Packages and Types Reference

Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved