Oracle Database 11g: Automatic SQL Tuning

September 26, 2007

Synopsis. Oracle Database 10g offered Oracle database administrators the ability to analyze high-volume SQL statements and then derive and implement more efficient alternative execution plans called SQL Profiles to insure better query and DML performance. This article delves into a new Oracle Database 11g Release 1 (11gR1) feature – Automatic SQL Tuning – that significantly extends this powerful 10g feature by providing the ability to implement these improved execution plans automatically within DBA-specified boundaries.

As a relatively well-seasoned Oracle DBA, I’ll admit that I was extremely skeptical when Oracle announced that Oracle Database 10g would be offering the capability to detect poorly-performing SQL statements and then automatically provide a better execution plan for the misbehaving statement. After all, I knew that even an experienced applications developer with an excellent knowledge of SQL and PL/SQL coding techniques tended to tweak her SQL statements with numerous optimizer hints to obtain maximum performance.

However, I was pleasantly surprised with Oracle Database 10g’s implementation of the Automatic Tuning Optimizer (ATO), an expansion of the cost-based optimizer (CBO). When the Automatic Database Diagnostic Monitor (ADDM) runs whenever an Automatic Workload Repository (AWR) snapshot has been taken, ADDM detects poorly performing high-volume SQL statements that could benefit from SQL tuning, and it will recommend analyzing the statements with Oracle 10g’s SQL Tuning Advisor.

The SQL Tuning Advisor (STA) will analyze each submitted high-volume SQL statement and then offer tuning recommendations in four different areas:

• Missing Statistics. First, the STA checks if any of the tables or indexes that the SQL statement is accessing might benefit from statistics creation or refresh.
• Improved Execution Plan. Next, the STA determines if the SQL statement might benefit from the generation of a SQL Profile. SQL Profiles are new objects in Oracle Database 10g that provide an alternative to a SQL statement’s current execution plan. Similar to its predecessor, the stored outline, a SQL Profile may rewrite the poorly performing statement by providing additional optimizer hints, or it partially executes the questionable SQL statement to gather ancillary statistics to aid in a better execution plan. The results of these findings are stored within a new SQL Profile specific to the poorly performing SQL statement, and the next time that the SQL statement is executed, the newly generated SQL Profile will be used to provide an optimal execution plan. Best of all, when ADDM detects that a SQL Profile no longer provides an improved execution plan, it will recommend the regeneration of the SQL Profile for the SQL statement.
• Missing Indexes. Next, the STA determines if the SQL statement might benefit from an additional (but as yet non-existent!) index on a column that the statement accesses, it will recommend the creation of that index. It also generates the SQL statements necessary to create the index; however, please note that it’s probably wise to edit the provided statements so that the index matches the DBA’s established naming conventions for database objects.
• Poorly Constructed SQL. Finally, the STA checks the SQL statement for any obvious (or not-so-obvious!) errors in construction. For example, it may detect that a missing join predicate is producing an unintended Cartesian product, or that a type mismatch between two tables is causing significant malperformance for the SQL statement.

Oracle Database 10g offered the new DBMS_SQLTUNE package that performs these analyses, records recommendations for improving the SQL statement performance, offers justifications for the recommendations, and finally offers to implement the specified recommendations with one simple click of a button (or the execution of the DBMS_SQLTUNE. ACCEPT_SQL_PROFILE procedure).

To illustrate, I’ve created some poorly performing SQL statements as shown in Listing 1. I’ve included the results of the EXPLAIN PLAN analysis for the statements as well. Next, I used Oracle Database 11g’s SQL Tuning Advisor from an Enterprise Manager session to review the poorly performing SQL statement, as shown in Figure 1 below:

Once it’s completed analyzing the poorly performing SQL statement, SQL Tuning Advisor can recommend possible solutions for improving its performance, as shown in Figure 2 below:

While the ability to generate accurate tuning recommendations about poorly performing SQL statements was a welcome addition in Oracle Database 10g, there was one major shortcoming: Even though DBMS_SQLTUNE could certainly generate SQL Profiles automatically, it did not implement them unless the DBA specifically took note of the recommended SQL Profiles, reviewed the detailed justification reasons for its implementation, and then accepted the SQL Profiles – even when it was obvious that the SQL Profile would have an overwhelmingly positive impact for overall database performance.