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.
Next
Back to DBAsupport.com
