In the last three posts we covered creating SQL Tuning Sets, diagnosing regressed SQL, creating SQL Plan Baselines, and verifying that the baselines have taken effect. Now it’s just a matter of upgrading your production database.

But, what happens if a baseline doesn’t work? Baselines are essentially just hints and the CBO can be temperamental at times. 12c includes many changes to the CBO so anything can happen. You can troubleshoot by capturing a 10053 trace and grep’ing for SPM. Note that baselines may no longer work for internal SYS SQL, so keep that in mind.

Also in 12c, SPM now stores the actual execution plan right in the SQL Management Base where as before the SQL had to be compiled to generate the plan. Check to make sure the plan in the baseline is the plan that you are expecting.

SELECT * FROM TABLE(DBMS_XPLAN.display_sql_plan_baseline('&&sql_handle.', '&&plan_name.', 'ADVANCED'));

Other things of note:

Licensing. SPM is included with Enterprise Edition and has been enabled by default since 11.1. That means you can use DBMS_SPM to create SQL Plan Baselines, but as soon as you start using DBMS_SQLTUNE you’ve ventured into the realm of a TUNING PACK license.

What if you are using Standard Edition or are worried about execution plans on your Active Data Guard database? Remember that at the end of the day SQL Plan Baselines are effectively just stored hints. Execution Plans has an outline section that includes all the hints that Oracle would need to reproduce that plan. Nothing is stopping you from using them by other means.

Don’t forget Bugs!

Master Note: Master Note: Plan Stability Features (Including SQL Plan Management (SPM)) (Doc ID 1359841.1)

Document 1948958.1 Patches to Consider for 11.2.0.3 to Avoid Problems with SQL Plan Management (SPM) Document 2034706.1 Patches to Consider for 11.2.0.4 to Avoid Problems with SQL Plan Management (SPM) Document 2035898.1 Patches to Consider for 12.1.0.2 to Avoid Problems with SQL Plan Management (SPM)

Things to Consider When Upgrading From 12.1.0.1 to Avoid Problems with SQL Plan Management (SPM) (Doc ID 2035897.1)

Finally, there is always a danger in using SPM. They are addictive and are really still a bandaid. You still need to determine the reason why the plans changed and if it is because the CBO just doesn’t have the right data it needs to make better decisions. That being said, this approach doesn’t necessarily have to be only for upgrades. Because it is so simple, you can use it to address issues with application releases, other system changes, or Y3K bugs.

Thanks for playing along.

Note: This topic was presented at NYOUG (September 2016) and RMOUG (February 2017)