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:

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

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.

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.