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.

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. 

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.

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

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.

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.

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.

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.