Oracle Database 11g: Automatic SQL Tuning - Page 3
September 26, 2007
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:
In addition, several other PL/SQL packaged functions and procedures provide control mechanisms for Automatic SQL Tuning tasks:
Implicit AST Deactivation. Finally, please note that AST will be completely deactivated by default if either:
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:
Each AST task execution provides quite a bit of valuable information:
Task Status. AST provides basic information about its current configuration, including:
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.
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.
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 11gR1 Reference
B28419-02 Oracle Database 11gR1 PL/SQL Packages and Types Reference