Oracle Database 11g: Automatic SQL Tuning - Page 2
September 26, 2007
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:
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:
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 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 statements 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 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 Server panel, and then selected the Automatic SQL Tuning Settings breadcrumb under the Oracle Scheduler section as shown in Figure 3 below:
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.
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.
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.