8
SQL Explain Plan

When you install the Oracle Tuning Pack, you receive the SQL Explain Plan functionality in selected areas throughout the Oracle Enterprise Manager console and management pack applications. The SQL Explain Plan provides a graphical representation of a SQL Statement's execution plan. An execution plan is the sequence of operations Oracle performs to execute the statement. The components of execution plans include:

• An ordering of the tables referenced by the statement.

• An access method for each table mentioned in the statement.

• A join method for tables affected by join operations in the statement.

Optimization is the process of choosing the most efficient way to execute a SQL statement. This is an important step in the processing of any data manipulation language (DML) statement: SELECT, INSERT, UPDATE, or DELETE. Many different ways to execute a SQL statement often exist, for example, by varying the order in which tables or indexes are accessed. The procedure Oracle uses to execute a statement can greatly affect how quickly the statement executes.

A part of Oracle called the optimizer chooses what it believes to be the most efficient way. The optimizer evaluates a number of factors to select among alternative access paths. The access paths the optimizer chooses can be displayed by generating explain plans.

Note: For more information on the Oracle Optimizer, see Oracle9i Database Performance Guide and Reference.

The graphical display mode (see Figure 8-1, "Graphical Display Mode") of SQL Explain Plan's main window is made up of two panes. The first pane shows the generated SQL text of the explain plan. The second pane shows the steps of the explain plan in a graphical or tabular display. The graphical display mode shows a visual representation of the relationships of each step in an execution plan. The tabular or "traditional" display mode (see Figure 8-2, "Tabular Display Mode") shows the relationships in a hierarchical, numbered list. You can specify the display mode you wish to use by clicking View from the menu bar and selecting either Graphical Display or Tabular Display.

Figure 8-1 Graphical Display Mode

Text description of the illustration graphica.gif

Figure 8-2 Tabular Display Mode

Text description of the illustration tabulard.gif

Stepping Through the SQL Explain Plan

The SQL Explain Plan is available in areas of Oracle Enterprise Manager that show SQL performance information such as overview charts from Performance Manager and the Instance Management window from the console.

You can "step through" the plan and immediately understand how the statement will execute and what step each operation is performing. Each operation is highlighted in order of execution. You can control the pace of the walkthrough, and can back-up or start over at any point. As an operation is highlighted, a description of the operation is displayed in the "step description" window directly below the Explain Plan display. You can also choose to view details for any objects involved in a particular operation.

When you click on a step, the description appears in the Step Description box below the plan. (If you do not see the descriptions, make sure that you have the Step Description option enabled in the View menu.

Note: For more information on using and understanding explain plans see Chapter 5, "Gathering and Analyzing Information".