Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation







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.



Remote DBA Service

Oracle Tuning Book


Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software










BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.