Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Sep 26, 2007

Oracle Database 11g: Automatic SQL Tuning - Page 3

By Jim Czuprynski

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

Oracle Archives

Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM