What is new with Automatic SQL Tuning in 11.2.0.2?

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:

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:

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:

Julian is the Global Database Lead of Accenture. His primary responsibility is managing and leading the Global Oracle Technology Practice which includes Autonomous Cloud, IaaS, PaaS, Database Services, Engineered Systems, Java, Middleware, Security and all other areas falling under Oracle Technology. He is also the Accenture-Enkitec Group Managing Director for ... Continue reading →