Title: RE: Any Good , Complete Docs , Source , Links on OUTLN ?
Oracle High-Performance SQL Tuning
by Don Burleson608
pagesISBN 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:[EMAIL PROTECTED]]Sent: Thursday, June 13, 2002 12:44
PMTo: Multiple recipients of list ORACLE-LSubject: 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:[EMAIL PROTECTED]]
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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
