Call (800) 766-1884 for Oracle support & training
Free Oracle Tips


Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance
 

Free Oracle Tips


 

HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

 

 

   
 

Tuning with Optimizer Plan Stability
Oracle Tips by Burleson
 

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:

  • Faster run-time execution   The SQL does not have to be reparsed and a new execution plan does not have to be developed when a SQL statement is executed. This greatly improves the preprocessing for SQL statements, and this benefit is especially evident in decision support systems where thousands of different SQL statements can be generated.

  • 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 (Figure 13-1).

1.      Check shared pool Hash the SQL statement and see if an identical statement is ready to go in the shared pool. If it is found, reexecute the SQL statement from the shared pool.

2.      Check 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.

Figure 1: Executing a new SQL statement

Optimizer plan stability can also aid us in our SQL tuning effort. Oracle provides a package called outln_pkg that allows you to manage your stored outlines and Oracle also provides an option for the ALTER SYSTEM and ALTER SESSION commands for the automatic collections of stored outlines for all of your SQL statements. However, it is not a good idea to turn-on the option to automatically store outlines unless you are prepared to examine each stored outline and ensure that the best execution plan is being used.

If you have already tuned your SQL, you can alter system set_stored_outlines=true and Oracle will define plans for all issued SQL statements in all sessions at the time they are executed; these stored plans will be reused until altered or dropped. If you have not tuned your SQL, you can create individual stored outlines for your session by entering the alter session set_stored_outlines=true command.

Just as the shared pool will not reuse a SQL statement unless it is identical to a previous statement, the effective use of stored outlines depends on the statement being reissued in an identical fashion each time it is used. If even one space is out of place, the stored outline is not reused. Whenever possible, all SQL should be stored in a PL/SQL procedures, functions, or packages, and bind variables should always be used to ensure that the SQL is reusable. This allows reuse of the stored image of the SQL as well as reuse of your stored outlines.


This is an excerpt from "Oracle High-Performance SQL Tuning" by Donald K. Burleson, published by Oracle Press.

  
 

Oracle performance tuning software 
 
 
 
 

Oracle performance tuning book

 

 
 
 
Oracle performance Tuning 10g reference poster
 
 
 
Oracle training in Linux commands
 
Oracle training Excel
 
Oracle training & performance tuning books
 

 

   

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

Oracle® is the registered trademark of Oracle Corporation. 

Hit Counter