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:

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:

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

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.

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

Conclusion

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

Previous

Back to DBAsupport.com