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, Ill 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 10gs 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 10gs 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:
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, Ive created some poorly performing SQL statements as shown in Listing 1. Ive included the results of the EXPLAIN PLAN analysis for the statements as well. Next, I used Oracle Database 11gs SQL Tuning Advisor from an Enterprise Manager session to review the poorly performing SQL statement, as shown in Figure 1 below:
Once its 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.