Optimizer Plan Stability

Summary

In this chapter, we thoroughly explored the Optimizer Plan Stability feature of Oracle8i. This feature was designed to allow a set of SQL statements performance to remain stable, regardless of changes in the database itself (for example, version upgrades, init.ora parameter changes, and so on). We have found some other useful functions of this feature, such as tuning applications that we cannot modify for whatever reason, finding out what indexes we really use, what SQL we really execute, and so on. Given that stored outlines are so transparent to an application, and add little or no overhead at run-time, their usefulness is increased. There are some crucial caveats to be aware of with regards to this feature, but once educated about them, stored outlines can be very powerful.