Oracle Enterprise Manager Database Tuning with the Oracle Tuning Pack Release 9.0.1 Part Number A86647-01 |
|
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. |
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.
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.
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.
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.
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:
As for simple statements, the optimizer must choose an access path to retrieve data from each table in the join statement.
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.
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.
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.
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.
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.
Note: The optimizer ignores this hint if the inner table is not partitioned or not equi-joined on the partitioning key.
Note: The optimizer ignores this hint if the inner table is not partitioned or not equi-joined on the partitioning key.
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.
If this option is enabled, the Optimizer Cost Estimates:
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.
The estimate by the cost-based approach of the number of rows accessed by the operation.
The estimate by the cost-based approach of the number of bytes accessed by the operation.
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:
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:
|
![]() Copyright © 1996-2001, Oracle Corporation. All Rights Reserved. |
|