 |
|
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:
www.oracle-script.com
The
definitive Oracle Script collection for every Oracle professional DBA
|
|
