11g SQL Plan Management with SQL Plan Baselines Oracle 11g New Features Tips by Donald BurlesonJune 28, 2015

Oracle 11g New Features Tips

Among the biggest challenges for a DBA are changes in the execution plans for SQL statements which occur all of a sudden, sometimes for no visible reasons. This can be caused by changed behavior of the optimizer after a new patch was implemented or simply because an automatic job has gathered new optimizer statistics over night.

There was a situation a couple of years ago where hundreds of employees suddenly were queuing up in front of the main entrance of a large business bank without being able to access the building because the application which controlled the badges was so slow. This again led to manual access control like it was done ages ago. Investigations for the direct reasons took several weeks. This incident happened in times of terrorist threats with bomb alerts and an increasing need for security.

M SQL Plan Management is only available with the Enterprise Edition

M No extra cost options required for the package dbms_spm.

In this case, it would have been very handy if it would have been possible to guarantee execution plan stability somehow. Oracle has taken care of such issues in 11g with a feature called SQL Plan Management (SPM) with SQL plan baselines. Plan baselines can be either maintained manually by feeding well tuned execution plans into the system or by enabling automatic SQL plan baseline capturing.

In this final part of the change management chapter, it will be shown how to achieve stability for a good execution plan by maintaining SQL plan baselines. This is a method to preventively guarantee plan stability and thus, preserve performance characteristics of SQL statements.