Automatic SQL Tuning and Profiles in 11g
Oracle 11g New Features Tips by Burleson
July 12, 2008
Oracle 11g SQL New Features Tips
Oracle 10g gave Remote DBAs the ability to create SQL
Profiles. This is a powerful tool, especially for database
administrators who need to tune SQL that is outside of their
control. For example, there is a third-party application where the
queries it generates cannot be modified. In this case, a Remote DBA could
generate a SQL Profile to tune poorly written SQL from this
Now with 11g, Oracle can automatically generate
and implement SQL improvements. These improvements may be through
one of the following methods:
SQL Profiles -
supplementary statistics specific to a given statement; SQL
profiles are particularly useful for packaged applications
SQL Structure – these are problems that may be
syntactic, semantic or just poorly written SQL
Statistics – good statistics are generally
always required for the optimizer
Indexes - a data structure that improves the
speed of operations in a table
Automatic SQL Tuning utilizes the statistics
from the Automatic Workload Repository (AWR) and churns through this
data on a nightly basis. The administrator can decide to have these
improvements be automatically implemented.
Automatic SQL Tuning can be managed from the 11g
OEM Database Control by clicking on the Server tab and then on
Automated Maintenance Tasks. Figure 1 below shows that Automatic
SQL Tuning is a schedulable maintenance task.
Automated Maintenance Tasks
In Figure 2, SQL Profiles would not be
automatically implemented on this particular database since this is
the default for 11g. This is usually the way Oracle introduces new
options in their products. In the initial release, it is disabled
by default. In release plus one or release plus two, it is enabled
Tuning Result Summary
In order to enable the automatic implementation
of SQL Profiles, the Remote DBA would click the Configure button. The next
screen is the task configuration screen.
Task Configuration Screen
Clicking the Configure button allows the
administrator to enable the Automatic
Implementation of SQL Profiles as shown below.
SQL Automatic Implementation
Any good Remote DBA is going to be very cautious of
this new feature. One way to ease into this new feature would be to
enable this on the test instance. Once enabled, it can be checked
periodically to see what SQL Profiles have been implemented as well
as other recommendations the tool is making. To see these
recommendations, log into the 11g OEM Database Control. Then click
on the Server tab, then on Automated Maintenance Tasks, and then on
Automatic SQL Tuning. For example, Automatic SQL Tuning has looked
at 237 SQL statements over a period of 31 runs (nightly by
default). It can also be seen from Figure 5 that the database most
likely has missing or old statistics. Additionally, it has found
some SQL that could use restructuring as well as several SQL
Profiles that would improve performance.
Automatic SQL Tuning Overall Task Statistics
The administrator can view the recommendations
made in even greater detail than is shown above, as seen in Figure
6. This screen also gives the ability to implement all
recommendations which may include gathering of statistics, creation
of SQL Profiles, index creation and the restructuring of SQL.
Implementing Recommendations Screen
Finally, to view the expected benefits gained by
implementing the recommendations, Automatic SQL Tuning provides the
pre-calculated time savings in the form of a column chart as shown
in Figure 7.
Precalculated Time Savings Chart
One may read about Automatic SQL Tuning and take
away that Remote DBAs are no longer needed to tune a database. However,
further inspection would show that Automatic SQL Tuning will free-up
a Remote DBA to focus on higher value tasks, at the very least.
This is an
excerpt from the new book
Oracle 11g New Features: Expert Guide to the Important
New Features by John Garmany, Steve Karam, Lutz Hartmann, V. J.
Jain, Brian Carr.
You can buy it direct from the publisher
for 30% off.