Call for Oracle support & training (800) 766-1884
Free Oracle Tips

Corporate Oracle Training
Custom Oracle Training
Oracle New Features Training
Advanced Oracle DBA Classes
Oracle Tuning Courses
Oracle Tips & Tricks
Oracle Training Links
Oracle Training Links
Oracle Training Links

We are top for USA Oracle Training Clients


Free Oracle Tips


Free Oracle App Server Tips


Oracle support

Oracle training

Oracle tuning


Remote Oracle

Custom Oracle Training


  Oracle Concepts by Burleson Consulting

Optimizer Plan Stability

If you have not done so already, please read the Internet link titled “What is your SQL optimizer Philosophy?”.  In this article, we explore the idea that SQL should change execution plan whenever the nature of the statistics on the tables and indexes change.

While some highly-volatile database will want this feature, the majority of databases need to ensure that once the optimal execution plan is located, that the execution plan always stays the same.

Oracle provides a utility called optimizer plan stability (also called stored outlines) that allows you to optimize and save the execution plans for any SQL statement. This utility has several features:

* SQL parsing and execution time is reduced because Oracle will quickly grab and execute the stored outline for the SQL.

* Tuning of SQL statements can easily be made permanent without locating the source code.

* SQL from third-party products (e.g., SAP, Peoplesoft) can be tuned without touching the SQL source code.

Optimizer plan stability enables you to maintain the same execution plans for the same SQL statements, regardless of changes to the Oracle database.  Without optimizer plan stability, any of the following Oracle database changes may alter the execution plan for every SQL statement on your system:

1 – Re-analyzing tables

2 - Adding or deleting data from tables

3 - Modifying a table's columns, constraints, or indexes

4 - Changing the system configuration

5 - Upgrading to a new version of the optimizer.

While the implementation of optimizer plan stability is detailed in Chapter 13, the concept is quite simple.  Before generating an execution plan for a new SQL statement, Oracle will always check to see if you have created a stored outline for the SQL.  If so, Oracle will load your stored SQL outline, and bypass the re-generation of an execution plan.

When a SQL statement enters Oracle8i, the database will perform the following actions.

1. Check shared pool?The Oracle database will hash the SQL statement and see if an identical statement is ready to go in the shared pool. If it is found, re-execute the SQL statement from the shared pool.

2. Check for stored outlines?If the SQL is not found in the shared pool, check for a stored outline in DBA_OUTLINES view in the OUTLINE tablespace. If a stored outline is found, load it into the shared pool and begin execution.

3. Start from scratch?If nothing for the SQL statement is found in the shared pool or stored outlines, parse the SQL, develop an execution plan, and begin execution.

While the use of stored outlines has generated a great deal of interest, we must remember that optimizer plan stability is only used because the SQL optimizer does not always generate the optimal execution plan.  In other words, if the SQL optimizer never made any mistakes, then we would not need this utility.

In the real-world, optimizer plan stability is used in shops with large vendor-based application suites (i.e. SAP, Peoplesoft), where you are not allowed to change the syntax of SQL statements.  In these cases, you can use stored outlines to alter the execution of the SQL without changing the source code for the SQL.

For more details, see the "Easy Oracle Series" a set of books especially designed by Oracle experts to get you started fast with Oracle database technology.

Download your Oracle scripts now:

The definitive Oracle Script collection for every Oracle professional DBA


Oracle performance tuning book



Oracle performance tuning software

Oracle performance tuning software
Oracle performance Tuning 10g reference poster
Oracle training in Linux commands
Oracle training Excel


email BC:

Copyright © 1996 -  2014 by Burleson Inc. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.