Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 

 
 

Oracle automated SQL Tuning:  The SPA treatment

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

 

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 OTN:

"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 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 packageThe 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.

 
Get the Complete
Oracle SQL Tuning Information 

The landmark book "Advanced Oracle 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 buy it for 30% off directly from the publisher.

 

 

��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.