Dontcheff

What is new with Automatic SQL Tuning in 11.2.0.2?

In DBA, Oracle database, SQL on April 24, 2011 at 14:32

When learning PL/1 and Fortran 4 in the very beginning of the 80s, I remember how the teacher was telling us that perhaps some day software will be able to create software. Something like that, I am paraphrasing. At that time software development was called programming.

With Oracle11, this became somehow true. What happens behind the curtain is that Oracle’s CBO is able to correct/change some bad execution plans without human intervention. Which actually turns out to be one of the best new features in 11g and for sure the best 11g new feature for Data Center DBAs.

But even in 11.2.0.2, the complete automation of Automatic SQL Tuning is not turned on by default. You have to manually enable it, just like this:

BEGIN
DBMS_AUTO_SQLTUNE.SET_AUTO_TUNING_TASK_PARAMETER(
parameter => 'ACCEPT_SQL_PROFILES', value => 'TRUE');
END;
/

After creating an 11g database, that is all you have to do in order to automate the process! For all the details, check the 11gR2 documentation on Automatic SQL Tuning.

The first time I did this in a mission critical production database, was in November 2007, as soon as Oracle 11g was available for Solaris. However, I did spend 100s of hours before that working/testing on 11.1.0.3 as an alpha tester.

In my opinion, starting 12g, accepting SQL profiles should be enabled by default. However, in order to use automatic SQL tuning and in particular SQL profiles the Tuning and the Diagnostics packs need to be licensed! Automatic SQL tuning via profiles is the most efficient way to massively tune SQL statements: CBO learns from its mistakes!

Back to 11.2.0.2: there are 3 new and updated performance tuning features:

• Resource Manager enhancements for parallel statement queuing and CPU utilization limit
• The Oracle Orion I/O Calibration Tool
• The new DBMS_AUTO_SQLTUNE package for Automatic SQL Tuning which is the new interface for manually managing the automatic SQL tuning task

Let us have a look at the 3 main subprograms of DBMS_AUTO_SQLTUNE:

Here are example of how you should use them:

1. Execute the Automatic SQL Tuning task immediately: EXEC DBMS_AUTO_SQLTUNE.EXECUTE_AUTO_TUNING_TASK; Note: must be run as SYS!

2. Display a text report of the automatic tuning task’s history: SELECT DBMS_AUTO_SQLTUNE.REPORT_AUTO_TUNING_TASK FROM DUAL;

3. Change a task parameter value for the daily automatic runs: see in the beginning how to enable automatic acceptance of SQL profiles. You can view all parameters as follows:

SELECT PARAMETER_NAME, PARAMETER_VALUE, IS_DEFAULT, DESCRIPTION
FROM DBA_ADVISOR_PARAMETERS
WHERE task_name = 'SYS_AUTO_SQL_TUNING_TASK'
/

I never understood why IE and Mozilla show me a mixture of Finnish and English in the description fields:

Back to the beginning of my post, have a look at how Oracle fixes its own errors, the Optimizer is optimizing its own execution plans! If you have not yet enabled Automatic SQL Tuning, do not heziate to do it, ask me for help or assistance if needed:

As of today when posting this, 24 April 2011, Metalink returns only 1 article (from all sources) on DBMS_AUTO_SQLTUNE, while I get almost 500 on DBMS_SQLTUNE:

If you are still interested in the topic check additionally:

DBMS_AUTO_SQLTUNE: ORA-01748 and Documentation Bugs
Morgan’s Library Oracle DBMS_AUTO_SQLTUNE Example Sample Code

Advertisements
  1. […] Enable Automatic SQL Tuning, Automatic Memory Management (not on Linux of course where HugePages are enabled) and use […]

  2. […] Enable Automatic SQL Tuning, Automatic Memory Management (not on Linux of course where HugePages are enabled) and use […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: