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 database.  Without optimizer plan stability, any of the following 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 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.