Oracle 11g automated SQL Tuning:  The SPA treatment Oracle 11g Tips by Burleson Consulting

 

 

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 tuning.

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 know 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).  I've 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 statements.

 

Many Oracle professional who adopt a bottom-up approach (tune the SQL first), find all of their hard-work un-done when a change is made to a global setting, such as one of the SQL optimizer parameters.

 

Madness or Marvel?

 

Many Oracle professional who adopt a bottom-up approach (tune the SQL first), find all of their hard-work un-done when a change is made to a global setting, such as one of the SQL optimizer parameters. 

Oracle’s holistic SQL tuning approach is new, and many Oracle professionals find it difficult to embrace, as in this note on OTN:

“Any advice to start playing with kernel parameters and initialization parameters before tuning a single SQL statement is madness”

Is Oracle embracing madness?  Lets 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 advisories.

 

The holistic approach to SQL tuning

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 proof for 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 dbms_sqltune.create_sqlset procedure:

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.

For more details on these exciting new 11g features, see my book "Oracle 11g New Features", available in Fall 2007.