BC remote Oracle DBA - Call (800) 766-1884  
Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation










Automatic SQL Tuning and Profiles in 11g

Oracle 11g New Features Tips by Burleson Consulting
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 application. 

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.

 Figure 1:  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 by default. 

Figure 2:  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.

Figure 3:  Task Configuration Screen

Clicking the Configure button allows the administrator to enable the Automatic Implementation of SQL Profiles as shown below.

Figure 4:  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.

Figure 5:  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.

Figure 6:  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.

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.

Expert Remote DBA

BC is America's oldest and largest Remote DBA Oracle support provider.  Get real Remote DBA experts, call
BC Remote DBA today.



Remote DBA Service

Oracle Tuning Book


Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software







BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.

Hit Counter