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:[EMAIL PROTECTED]]
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 documentationAlso, 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).
