Oracle Enterprise Manager Database Tuning with the Oracle Tuning Pack
Release 9.0.1

Part Number A86647-01

Library

Product

Contents

Index

Go to previous page Go to next page

26
Introduction to the Outline Editor

When you create an outline, hints are added to the database which are used by the optimizer when executing the SQL statement. The outline affects the execution plan of the SQL text because the optimizer uses the hints stored in the outline to execute the SQL. The Outline Editor application window shows the SQL text of the outline and the graphical layout of the outline's execution plan. The execution plan is the set of steps Oracle uses to execute a statement and includes the access method for each table that the statement accesses, the join methods used, and an ordering of the tables (the join order.)

With the Outline Editor, you can change the outline's join order, access methods, and other outline properties to optimize its performance. Note that changes to an outline may result in invalid hints to the optimizer. The optimizer may revert your changes if the changes are unacceptable. It is important to note that these changes will not be applied to the outline until the changes have been validated. It is also important to note that these changes will not be available to other users until the outline has been saved. You will not see the updated outline in Outline Management until the outline has been saved with a unique name and Outline Management has been refreshed. You must provide a unique name and a category for the outline.


Note :

For more information on using outlines, see the Oracle9i Database Performance Guide and Reference.  


Accessing the Outline Editor

Perhaps the most common way of accessing the Outline Editor is through the Outline Management application. In a typical use scenario, Outline Management would be used to preview all of the existing outlines. If a change was needed for one of the outlines, the user would double-click on the outline and the Outline Editor application would appear. Once the changes have been made, the outline must be validated and saved in Outline Editor before the updated outline will be available from the Outline Management tool.

If you have the Oracle Diagnostics Pack installed, you can launch the Outline Editor from one of the TopSQL charts in Performance Manager. To get to the Outline Editor from Performance Manager, select a SQL statement from the TopSQL chart, choose Drilldown from the menu bar and select Explain Plan. Once the Graphical Explain Plan window is displayed, choose Drilldown from the menu bar and select Create/Edit Outlines. This will launch the Outline Editor in context to the SQL explain plan currently in the Explain Plan window.

Figure 26-1 Outline Editor Main Window


Text description of oe_main.gif follows.
Text description of the illustration oe_main.gif

Creating an Outline

To create an outline, you must provide the following:

Once the outline has been created, the Outline Editor allows you to look at the graphical representation of the Outline's execution plan. To do this, choose View\View Outline from the menu bar. The outline will not be accessible to others until the outline has been saved, which does an implicit validation. To save a new outline and commit it to memory, choose File\Save from the menu bar. Once saved, you can manage the outline using the Outline Management tool. Select Refresh from the Outline Management tool to see the newly created outline in the outline list.


Note:

Within one category, each outline must have unique SQL. 


Using Categories

When you create an outline, special care should be given to the category you assign to the outline. Categories are useful for sorting, deleting, importing, and exporting outlines. The category name provides a good handle for bundling outlines together. You can also use category names to distinguish one outline from another. In some situations, you may find that you have two outlines with the same name and SQL text. The only difference may be that one is used in monthly reporting applications and the other is used in daily reporting applications. A category name would help you distinguish which outline is used for a particular operation.

Using Bind Variables

Note that there is a one-to-one correspondence between SQL text and its outline. If you specify a different literal in a predicate, then a different outline applies. To avoid this, replace literals in applications with bind variables.


Note:

If your SQL contains bind variables and you want to execute Outline Editor, you must first specify literal values for the bind variables by choosing Outline\Bind Variables from the Outline Editor menu. 


Editing an Outline

Once an outline has been created, you may need to make modifications to optimize its performance. With the Outline Editor, you can change the outline's join order, access methods, and other outline properties. Note that changes to an outline may result in invalid hints to the optimizer. The optimizer may revert your changes if the changes are unacceptable. It is important to note that these changes will not be applied to the outline until the changes have been validated.

It is also important to note that these changes will not be available to other users until the outline has been saved. You will not see the updated outline in Outline Management until the outline has been saved and Outline Management has been refreshed.

To choose an execution plan for a join statement, the optimizer must make these interrelated decisions:

Access Paths

As for simple statements, the optimizer must choose an access path to retrieve data from each table in the join statement.

Join Method

To join each pair of row sources, Oracle must perform one of these operations:

To execute a statement that joins more than two tables, Oracle joins two of the tables, and then joins the resulting row source to the next table. This process is continued until all tables are joined into the result.

Changing the Join Order

The Join Order of an explain plan tells the optimizer how to join multiple tables within a single statement. For example, when executing a statement that joins more than two tables, Oracle joins two of the tables, and then joins the resulting row source to the next table. This process is continued until all tables are joined into the result. The Outline Editor makes it very easy for you to change the join order by allowing you to drag and drop a table to another position or by selecting the Outline\Join Order menu option.

Changing the Join Method

The outline's join method tells Oracle how to process a join. There are three basic types of joins: Merge join operations, Nested Loop operations, and Hash join operations. Based on the conditions in your query, the available indexes, and (for the Cost-based optimizer) the available statistics, the optimizer will choose which join operation to use. Depending on the nature of your application and queries, you may want to suggest the optimizer to use a method different from its first choice of join methods. The following list describes the join operations.

Join Method Descriptions:

Distribution Method Descriptions:

Join Predicate:

The Join Predicate is the where clause and, more specifically, a selection criteria clause based on one of the operators (=,!=, IS, IS NOT,>,>=) and containing no AND, OR, or NOT.

Swap Join Inputs:

If this option is enabled, the Optimizer Cost Estimates:

Cost:

The cost of the operation as estimated by the optimizer's cost-based approach. For statements that use the rule-based approach, this column is null. Cost is not determined for table access operations. The value of this column does not have any particular unit of measurement, it is merely a weighted value used to compare costs of execution plans.

Cardinality:

The estimate by the cost-based approach of the number of rows accessed by the operation.

Bytes:

The estimate by the cost-based approach of the number of bytes accessed by the operation.


Note :

For more information see the Oracle9i Database Performance Guide and Reference.  


Changing the Access Method

Access methods are ways in which data is retrieved from the database. For any row in any table accessed by a SQL statement, there are three common ways by which that row can be located and retrieved:

  1. A row can be retrieved through a full table scan, which reads all rows from a table and filters out those that do not meet the selection criteria.

  2. A row can also be retrieved through the use of an index, by traversing the index using the indexed column values specified by the statement.

  3. A row can be retrieved by specifying the ROWID. ROWID access is the fastest way to retrieve a row, because this specifies the exact location of the row in the database.

The Access Method dialog allows you to specify how the optimizer should scan a particular table or index. By default, Oracle attempts to choose the access method that will yield the fastest results and consume the least amount of resources. In some cases, however, you may need to force the optimizer to use a specific method.

Outline Editor provides the following Access Method options:

Access Method Descriptions:


Go to previous page Go to next page
Oracle
Copyright © 2001 Oracle Corporation.

All Rights Reserved.

Library

Product

Contents

Index