Return-Path: Received: from newsfeed.cts.com (newsfeed.cts.com [209.68.248.164]) by naude.co.za (8.11.2/8.11.2) with SMTP id g5E19bx19946 for ; Thu, 13 Jun 2002 21:09:37 -0400 Received: from fatcity.UUCP (uucp@localhost) by newsfeed.cts.com (8.9.3/8.9.3) with UUCP id KAA44282; Thu, 13 Jun 2002 10:07:18 -0700 (PDT) Received: by fatcity.com (26-Feb-2001/v1.0g-b71/bab) via UUCP id 0047D604; Thu, 13 Jun 2002 09:30:12 -0800 Message-ID: Date: Thu, 13 Jun 2002 09:30:12 -0800 To: Multiple recipients of list ORACLE-L X-Comment: Oracle RDBMS Community Forum X-Sender: "Jay Earle (DBA)" Sender: root@fatcity.com Reply-To: ORACLE-L@fatcity.com Errors-To: ML-ERRORS@fatcity.com From: "Jay Earle (DBA)" Subject: RE: Any Good , Complete Docs , Source , Links on OUTLN ? Organization: Fat City Network Services, San Diego, California X-ListServer: v1.0g, build 71; ListGuru (c) 1996-2001 Bruce A. Bergman Precedence: bulk Mime-Version: 1.0 Content-Type: multipart/alternative; boundary="----_=_NextPart_001_01C212F6.8149D6F0" ------_=_NextPart_001_01C212F6.8149D6F0 Content-Type: text/plain; charset="iso-8859-1" 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----- ] 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 -- Author: VIVEK_SHARMA INET: VIVEK_SHARMA@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@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). ------_=_NextPart_001_01C212F6.8149D6F0 Content-Type: text/html; charset="iso-8859-1" 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-----
From: Aponte, Tony [mailto:AponteT@hsn.net]
Sent: Thursday, June 13, 2002 12:44 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: Any Good , Complete Docs , Source , Links on OUTLN ?

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-----
From: VIVEK_SHARMA [mailto:VIVEK_SHARMA@infosys.com]
Sent: Thursday, June 13, 2002 9:33 AM
To: Multiple recipients of list ORACLE-L
Subject: Any Good , Complete Docs , Source , Links on OUTLN ?


--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: VIVEK_SHARMA
  INET: VIVEK_SHARMA@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@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).

------_=_NextPart_001_01C212F6.8149D6F0-- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jay Earle (DBA) INET: Jay_Earle@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@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).