Oracle automated SQL Tuning: The SPA
Oracle 11g Tips by Donald Burleson
Oracle has made exciting headway
with SQL profiles in Oracle 11g by "closing the loop" and
allowing the SQL profiles to be automatically implemented.
Traditionally, Oracle has tuned SQL by making recommendations
(i.e. recommending new indexes, recommending new materialized
views), but the Oracle 11g automated SQL tuning takes this one
step further, with a holistic workload-based approach to SQL
In Oracle 11g fully automated SQL tuning, the DBA defines a
representative workload, and Oracle tests the work load
empirically, against the database. Instead of using theory and
calculations, Oracle tests the SQL in a real world environment
running it repeatedly and determining heuristically the optimal
execution plan for the SQL. Oracle then builds SQL profile and
implements it directly.
The challenge of tuning SQL
The declarative nature of the SQL
access syntax has always made it difficult to perform SQL
tuning. The basic tenet of cost-based SQL optimization is
that the person who writes a SQL query simply "declares" what
columns they want to see (the SELECT clause), the tables where
the columns reside (the FROM clause), and the filtering
conditions (the WHERE clause).
It's up to the SQL optimizer to
always determine the optimal execution plan, a formidable
challenge, especially in a dynamic environment.
Oracle embraces holistic tuning
Oracle tuning consultants have
known for many years that the best way to tune an Oracle system
is to take a top-down approach, finding the optimal
configuration for external factors (i.e. OS kernel settings,
disk I/O subsystem), and determining the best overall setting
for the Oracle instance (i.e. init.ora parameters).
Oracle University has been advocating holistic tuning for many years, and I go into
great detail in my book "Oracle
Tuning: The Definitive Reference".
Holistic tuning involves tuning a representative workload first,
adjusting global parameters in order to optimize as much SQL as
possible. Only then is it prudent to start tuning individual SQL
Many Oracle professional who adopt
a bottom-up approach (tune the SQL first), find all of their
hard work undone when a change is made to a global setting,
such as one of the SQL optimizer parameters.
Madness or Marvel?
Oracle's holistic SQL tuning
approach is new, and many Oracle professionals find it difficult to embrace, as
in this note on
"Any advice to start playing with kernel
parameters and initialization parameters before tuning a single SQL
statement is madness"
Is Oracle embracing madness?
Let's take a closer look.
The SPA treatment
The Oracle 11g SQL Performance Analyzer
(SPA), is primarily
designed to speed up the holistic SQL tuning process.
Until the advent of the Oracle 10g
intelligent SQL tuning advisors (The SQLAccess advisor and
SQLTuning Advisor), SQL tuning was a time-consuming and tedious
task. That all started to change in Oracle 10g, and it's
even more exciting in Oracle 11g, where Oracle has promised
"fully automated" SQL tuning, via the new SQL
Performance Analyzer and improvements in the existing 10g SQL
The holistic approach to SQL
Holistic tuning in Oracle 11g is a broad-brush
approach that can save thousands of hours of tedious SQL tuning because you can
tune hundreds of queries at once within an STS.
Now, Oracle 11g does not have all
of the intelligence of a human SQL tuning expert, but the 11g
SQL Performance Analyzer (SPA) is a great way to test for the
effect of environmental changes to your Oracle environment.
Let's take a closer look at how
Oracle has automated the SQL tuning process.
The SPA treatment
The SQL performance analyzer allows the DBA to define the SQL Tuning set
(the STS), as a source for the test (usually using historical
SQL from the AWR tables).
The SPA receives one or more SQL statements as input
(via the SPA), and provides
advice on which tuning conditions have the best execution plans, gives the
the advice, shows an estimated performance benefit, and allegedly has a
facility to automatically implement changes that are more than 3x faster
than the "before" condition".
The following steps are used to define the STS using the
dbms_sqltune package. The
steps within the new 11g OEM screen for "guided workflow" are simple and
straightforward, and serve as an online interface to the
1 - Options -
Choose a name for your SQL tuning set (STS). The SQL workload set is
created using the dbms_sqltune.create_sqlset procedure.
2 - Load methods -
Here is where you can choose the source for your SQL workload, and to take
historical SQL statements from AWR.
3 - Filter options
- You can choose ?filtering? conditions, based on your specific tuning
needs. For example, if your database is disk I/O bound, you might choose
only SQL statements that have more than 100k disk reads.
4 - Schedule - This
is an interface to the dbms_scheduler package, allowing you to define
and schedule a job.
5 - Review - Here
you can see the actual source calls to dbms_sqltune.create_sqlset and
the dbms_scheduler.create_job procedure call syntax.
Get the Complete
Oracle SQL Tuning Information
The landmark book
SQL Tuning The Definitive Reference" is
filled with valuable information on Oracle SQL Tuning.
This book includes scripts and tools to hypercharge Oracle 11g
performance and you can
for 30% off directly from the publisher.