Oracle Database 11g: Automatic SQL Tuning

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:



Figure 1. Executing Oracle Database 11g SQL Tuning
Advisor.

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:



Figure 2. SQL Tuning Advisor Suggestions for Improved SQL
Profile.

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.

Jim Czuprynski
Jim Czuprynski
Jim Czuprynski has accumulated over 30 years of experience during his information technology career. He has filled diverse roles at several Fortune 1000 companies in those three decades - mainframe programmer, applications developer, business analyst, and project manager - before becoming an Oracle database administrator in 2001. He currently holds OCP certification for Oracle 9i, 10g and 11g. Jim teaches the core Oracle University database administration courses on behalf of Oracle and its Education Partners throughout the United States and Canada, instructing several hundred Oracle DBAs since 2005. He was selected as Oracle Education Partner Instructor of the Year in 2009. Jim resides in Bartlett, Illinois, USA with his wife Ruth, whose career as a project manager and software quality assurance manager for a multinational insurance company makes for interesting marital discussions. He enjoys cross-country skiing, biking, bird watching, and writing about his life experiences in the field of information technology.

Latest Articles