RE: Any Good , Complete Docs , Source , Links on OUTLN ?

Oracle High-Performance SQL Tuning

by Don Burleson
608 pages
ISBN 0-07-219058-2 The following excerpt is from Chapter 13 of this publication.  

Tuning with Optimizer Plan Stability

This chapter discusses the use of optimizer plan stability in Oracle8i and shows how you can improve the run-time performance of SQL statements and also provide a easy method to permanently change the execution plans for SQL statements. This chapter will cover the following topics:

        Introduction to stored outlines

        Preparing Oracle for stored outlines

        How to create and modify a stored outline

        Managing a stored outline

Introduction to Stored Outlines

The optimizer plan stability feature of Oracle8i has been a long time coming. The earlier databases such as DB2 and IDMS have had the ability to store execution plans since the 1980s, and the concept of stored SQL outlines has had widespread acceptance in the non-Oracle world for decades.

The argument behind optimizer plan stability is that there exists only one optimal execution plan for any SQL statement, and once located, the execution plan should never change, even when the CBO statistics or initialization parameters are changed. Of course, this philosophy is contrary to the basic tenet of Oracle cost-based optimization, which expects SQL statements to change execution plans when the characteristics of the table and index statistics change, or when a change is made to an important initialization parameter such as sort_area_size or db_file_multiblock_read_count.

Regardless of philosophy, creating a stable execution plan for Oracle SQL has two major benefits:

        Change execution plan without touching SQL source code Many databases have SQL that is dynamically generated (e.g., SAP) or SQL that resides in unreachable PC libraries. For these types of applications, stored outlines allow you to change the execution plan for the SQL without the need to change the SQL source code.

        Permanent SQL tuning changes Once tuned, optimizer plan stability allows for SQL statements to always have the same execution plan. There will be no surprises when a change is made to an important initialization parameter such as sort_area_size or when the CBO statistics change.

TIP: Stored outlines are great for tuning SQL in database application suites where the SQL source is not available or cannot be changed. For example, SAP and PeopleSoft applications products can now have SQL tuning without touching the source code.

Now that we see the benefits of using stored outlines, let's take a look at how optimizer plan stability works. When a SQL statement enters Oracle8i, the database will perform the following actions    

.

.

.

.

-----Original Message-----
Sent: Thursday, June 13, 2002 12:44 PM
To: Multiple recipients of list ORACLE-L

Chapter 11 in Tom Kyte's Oracle one-on-one Export (start here if you can and save yourself time)
Metalink Note 92202.1 How To Specify Hidden Hints in SQL Oracle Corporation paper by David McElhoes, Stabilizing Query Performance With Stored Outlines
Chapter 10 of the Designing and Tuning for Performance documentation

Also, just in case you haven't already, try using "Plan Stability" in your web searches.

Good luck.

Tony Aponte

-----Original Message-----
<mailto:VIVEK_SHARMA_at_infosys.com> ]
Sent: Thursday, June 13, 2002 9:33 AM
To: Multiple recipients of list ORACLE-L

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
<http://www.orafaq.com>  
-- 
Author: VIVEK_SHARMA 
  INET: VIVEK_SHARMA_at_infosys.com 

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051 
San Diego, California        -- Public Internet access / Mailing Lists 
-------------------------------------------------------------------- 
To REMOVE yourself from this mailing list, send an E-Mail message 
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in 
the message BODY, include a line containing: UNSUB ORACLE-L 
(or the name of mailing list you want to be removed from).  You may 
also send the HELP command for other information (like subscribing). 


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jay Earle (DBA)
  INET: Jay_Earle_at_smartforce.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).