Wouldn’t “Automatic SQL Plan Management” be the 11g “killer app” ?

Anyway, before you answer this question, lets illustrate with an example how the “non-automatic”, “no tuning Pack” SQL Plan Management works. This 11g new feature completes (replaces ?) the outlines by providing a new plan stability capability to Oracle database 11g Enterprise Edition.

If you want the truth behind this example, you should refer before anything else to the 11g documentation :

We’ll need to use SQL*Plus ! I haven’t been able to use SQL*Developer as the last session query, from my experience is not the one you’ve just run even with “set serveroutput off” and I don’t want to use “explain plan” neither “set autotrace on”. Connected as the sample user (Let’s say SCOTT), run :

set serveroutput off
select a.id,
b.id
from gark a,
gark b
where a.id=500
and b.id=a.id;
select * from table(dbms_xplan.display_cursor);

What if I just accept a new plan and it is indeed not efficient ? You can of cause drop the plan from the SQL Plan baselines as described in Step 7 or you can use the FIXED attribute which set the plans that have to be used. Using this attribute enables you to easily go back and forward between a set of plans :

The instances capture the “repeatable” SQL and their plan during the “normal” period.

The new automatic maintenance schedule runs the SQL Tuning Advisor and set the SQL plans. As with 10g the SQL Tuning Advisor does not only consider the statistics but goes much deeper into SQL and plan analysis to evolve the plan…

While new plans can be consider, the SQL Tuning Advisor evolves plans during the Maintenance Window making plans probably more accurate and probably less candidate to sudden changes.

It looks a fantastic 11g feature, don’t you think ? The question now is what performance enhancement I should expect from it and a good way to evaluate it would be to run the command below on a production system… Who dares ?

alter system set optimizer_capture_sql_plan_baselines=true

Step 9 : Drop the sample schema

drop index gark_idx;
drop table gark;

Step 10 : To conclude

No doubt that in manual mode SQL Plan Management highly improves the way we manage plan stability. It enables to easily switch from one plan to the other, it can be exported/imported from one database to the other and as a good news **considering the best 11g new features seem to be part of options**, it’s part of the classic “EE”. Now I miss a real production use case of Automatic SQL Plan Management.

where is it documented that optimizer_capture_sql_plan_baselines is part of the Tuning Pack?
followed the link at top “optimizer_capture_sql_plan_baselines (Tuning Pack)” but didn’t see any reference to the Tuning Pack.
Did a google search but didn’t see anything in the first 10 hits

The question is What is “Automatic Plan Evolution of SQL Plan Management” ? Obviously if you search the documentation for this feature, you find it only in the Licensing Guide.

I’ve disabled the Tuning pack and change the value and it doesn’t fail. The licensing terms suggest that’s actually included in that case! or, maybe, Oracle disables the feature, in spite of the parameter and doesn’t tell:
SQL> alter system set control_management_pack_access=DIAGNOSTIC;

System altered.

SQL> alter system set OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=true;

System altered.

So it’s worth more testing (at least to me) to figure out what exact part of SPM is included in the Tuning Pack Only!

SQL Plan Management is not part of the Tuning Pack. It is a free tool available to all users of Oracle Enterprise Edition. What Kyle found in the Oracle documentation is a documentation bug. The wording implies you need the Tuning Pack when you don’t. SQL Plan Management works in conjunction with the Tuning Pack specifically the auto SQL Tune feature but is not part of it.
Automatic Plan Evolution of SQL Plan Management means if the Auto SQL Tune advisor finds a better plan for a SQL statement that has a SQL Plan Baseline that plan will be automatically added to the plan baseline as an accepted plan. Nothing more. More info on this can be found in this white paper http://www.oracle.com/technetwork/database/features/bi-datawarehousing/twp-sql-plan-management-11gr2-133099.pdf

PYTHIAN®, LOVE YOUR DATA®, and ADMINISCOPE® are trademarks and registered trademarks owned by Pythian in North America and certain other countries, and are valuable assets of our company. Other brands, product and company names on this website may be trademarks or registered trademarks of Pythian or of third parties. Use of trademarks without permission is strictly prohibited.