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.

Accessing the Outline Editor

• From Outline Management

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.

• From TopSQL charts in Performance Manager

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 the illustration oe_main.gif

Creating an Outline

To create an outline, you must provide the following:

• A name for the new outline. This name must be unique within the database.
• A category type for the new outline. By default, the DEFAULT category is specified for you. To change the category, type in a new category name.
• The SQL text used to create the outline. If you are creating an outline like another outline, the SQL text is provided.

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.

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.

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:

• Nested Loop Joins-- causes Oracle to join each specified table to another row source with a nested loops join using the specified table as the inner table.
• Merge Join-- causes Oracle to join each specified table with another row source with a sort-merge join.
• Hash Join-- causes Oracle to join each specified table with another row source with a hash join.
• Hash Anti-Join-- uses a hash anti-join which causes Oracle to subtract two sets from the join.
• Merge Anti-Join-- uses a sort merge anti-join which cause Oracle to subtract one set from the join.
• Hash Semi-Join-- uses a hash semi-join which causes Oracle to preserve the semantics of the subquery.
• Merge Semi-Join-- uses a sort merge semi-join which causes Oracle to preserve the semantics of the subquery.

Distribution Method Descriptions:

• Hash/Hash--Maps the rows of each table to consumer query servers using a hash function on the join keys. When mapping is complete, each query server performs the join between a pair of resulting partitions. This hint is recommended when the tables are comparable in size and the join operation is implemented by hash-join or sort-merge join.
• Broadcast/None--All rows of the outer table are broadcast to each query server. The inner table rows are randomly partitioned. This hint is recommended when the outer table is very small compared to the inner table.

  A rule-of-thumb is: Use the Broadcast/None hint if the size of the inner table * number of query servers> size of the outer table.

• None/Broadcast-- All rows of the inner table are broadcast to each consumer query server. The outer table rows are randomly partitioned. This hint is recommended when the inner table is very small compared to the outer table.

  A rule of thumb is: Use the None/Broadcast hint if the size of the inner table * number of query servers < size of the outer table.

• Partition/None--Maps the rows of the outer table using the partitioning of the inner table. The inner table must be partitioned on the join keys. This hint is recommended when the number of partitions of the outer table is equal to or nearly equal to a multiple of the number of query servers, for example, 14 partitions and 15 query servers.

  Note: The optimizer ignores this hint if the inner table is not partitioned or not equi-joined on the partitioning key.

• None/Partition--Maps the rows of the inner table using the partitioning of the outer table. The outer table must be partitioned on the join keys. This hint is recommended when the number of partitions of the outer table is equal of nearly equal to a multiple of the number of query servers, for example, 14 partitions and 15 query servers.

  Note: The optimizer ignores this hint if the inner table is not partitioned or not equi-joined on the partitioning key.

• None/None--Each query server performs the join operation between a pair of matching partitions, one form each table. Both tables must be equi-partitioned to the join keys.
• Random/None

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.

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:

• Full--This method performs a full table scan (the TABLE ACCESS FULL operation) on the listed table.
• Rowid--This method tells the optimizer to use a TABLE ACCESS BY ROWID operation to access the rows in the table. In general, you should use a TABLE ACCESS BY
• ROWID operation whenever you need to return rows quickly to users and whenever the tables are large.
• Cluster--If you are using nonhash clusters, this method tells the optimizer to read a table using the TABLE ACCESS CLUSTER operation. This method should be used against queries that do not include joins.
• Hash--This method tells the optimizer to use a TABLE ACCESS HASH operation when scanning a table.
• Index--This method tells the optimizer to use an index-based scan on the specified table. You can use the INDEX method for domain, B*-tree, and bitmap indexes. (Note that INDEX_COMBINE is a more versatile hint for bitmap indexes. ) This method may also be used to specify one or more indexes.
• Index Ascending--This method tells the optimizer to scan the specified table's indexes in ascending order. Because Oracle's default behavior for a range scan is to scan index entries in ascending order of their indexed values, this hint does not specify anything more than the INDEX hint. INDEX_ASC is useful when you want to specify ascending range scan explicitly, should the default behavior change.
• Index Combine--This method identifies the most cost effective indexes for the optimizer. The optimizer recognizes all indexes that can potentially be combined, given the predicates in the WHERE clause. However, it may not be cost effective to use all of them.
• Index Descending--This method tells the optimizer to scan the index in descending order (from its highest value to its lowest).
• Fast Full Index-- This method causes a fast full index scan to be performed rather than a full table scan.
• And Equal--This method chooses an execution plan that uses an access path that merges the scans on several single-column indexes.
• No Index
• No Access