Page History
...
- When you edit a statement and save an alternative. see See “Editing an existing statement” on page 154.
- When you click New Alternatives in the Related SQL view, and Precise for Oracle creates an alternative SQL for you. See “Generating new alternatives” on page 156.
- When an index recommendation is made in the What-If tab and you click Compare to switch to the SQL tab, in which case a copy of the statement is saved along with its new virtual index and execution plan. See “About the What-If tab” on page 159.
...
- Plan
- Run Alternatives
- More ...
About viewing the execution plan of a statement
The Plan view displays the execution plan of a statement and related information, such as statistics, referenced objects, and operations performed in the execution plan. It lets you assess and tune statements based on real information.
Two types of execution plans are available when launching to the SQL tab:
Real execution plan. The plan which was collected from Oracles V$ tables. Additional real-time statistics are available for these plans. For example, you can view a breakdown of In Oracle time for a plan as compared to other plans, or a breakdown of its I/O when accessing Oracle objects.
When launching to the SQL tab with a specific SQL statement in context, Precise for Oracle presents actual execution plan information, including actual plan steps, and information and statistics for multiple plans. For example, if a statement has several different execution plans, all are displayed.
Note:Info This feature is only available for Oracle 10.1.0.4 monitored instances and later.
- Estimated execution plan. Depending upon the version of Oracle running on your system (such as Oracle 8i), you may be only able to generate and run an estimated execution plan. At times, real execution plan information is lost (as when they are removed from the system before Precise for Oracle could access them). If this happens, all real-time statistics (such as, I/O, and In Oracle) are considered to belong to other plans.
...
Table 8-2 Information tabs
| Information |
|---|
...
| Tabs | Description |
|---|---|
| Highlights | Displays the execution plan's formatted text and findings that enable you to identify the most probable cause of the problem your statement is experiencing. |
| Expanded |
...
| Text | Shows all views and synonyms expanded inline allowing you to match the access steps to the version of SQL that Oracle is actually executing. |
| Objects |
...
| Displays information on all referenced objects in the execution plan, including their indexes and columns. Statistical details and general details are displayed for each object. |
| More ... |
...
Shows the following additional information for a plan: |
...
|
...
|
...
|
...
|
| Info |
|---|
...
If you use the Oracle rule-based Optimizer, cost information is not available. If tables and indexes are not analyzed, statistics changes are not available. |
About the Execution Plan tree
The Execution Plan tree displays both the real execution plan (Oracle 10.1.0.4 and later) and the estimated execution plan, of the specified statement. When you analyze the access plan of a selected statement, you can examine the access path that was chosen by the Oracle Optimizer.
The execution plan's text or expanded text (with all referenced views expanded) appears both at the bottom of the execution plan tree (by clicking the plus sign (+) to view text) or at the top of the Details area (right pane). The text that relates to the selected step in the Plan tree is highlighted. This lets you view the text of the statement, the execution plan, and additional information, such as the objects referenced by the statement, all at once. See “About expanded text of a statement in the Plan view” on page 135.
Actions that can be performed on the tree
You can perform the following actions on the execution plan tree:
- The Playback controls, located at the top of the tree, enable you to freely move within the execution plan of an explained statement.
- By moving the pointer over the execution plan steps, you can view more statistical information, such as Estimated Cost, Estimated Rows, and Estimated I/O Cost, and description of the specified step.
- Selecting a specific step highlights nested steps and affects the information displayed in the information tabs: the relevant SQL text is highlighted on the Text tab; the referenced tables, indexes, and columns are shown on the Objects tab; and the relevant step is highlighted on the Statistics tab.
- Clicking the plus sign (+), located at the bottom of the tree, displays and highlights the statement's formatted text or expanded text.
About the text of a statement in the Plan view
The Highlights tab, in the SQL tab, displays the execution plan's formatted text and findings that enable you to identify the most probable cause of the problem your statement is experiencing. The Findings table and Findings Details area provide additional information on what steps you can take to continue your analysis and lets you launch to the appropriate tab, with the statement in context.
...
When a table is highlighted, all table columns appearing in the text are highlighted in the same color as the table. The following is an explanation of the major step types.
About steps that access a table or index
The table and its columns are highlighted and color-coded. The columns that are accessed by an index are underlined.
About sort steps
All tables whose columns were used by sort steps are highlighted and color-coded. Each table and its corresponding columns have their own color. The columns that participate in the sort are underlined.
About join steps
The methodology for highlighting nested loop steps differs slightly from that of the merge join and hash join steps. Nested loop steps are highlighted as follows: all tables and their corresponding columns participating in a sub-tree of the outer table (the first step that is a direct descendant of the nested loop step) are highlighted in blue; all tables and their corresponding columns participating in a sub-tree of the inner table (the second step that is a direct descendant of the nested loop step) are highlighted in red.
The highlight methodology for the other join steps (hash join and merge join) differs slightly. The first table and all its corresponding columns are highlighted in blue. The columns that are used for the join are underlined. The second table and all its corresponding columns are highlighted in red. The columns that are used for the join are underlined.
About expanded text of a statement in the Plan view
Oracle provides views and synonyms as a way of simplifying the complexity of an application. However, the Oracle Optimizer must generate an execution plan against the correct underlying tables even if they do not appear in the SQL text. The Expanded Text tab shows all views and synonyms expanded inline enabling you to match access steps to the version of SQL that Oracle is actually executing.
In the SQL tab, a statement with the original text appears in the bottom left corner and the expanded text on the right at the top. The expanded view text is shown in bold. The statement highlighting picks out the columns within the view referenced in the explain plan.
About the objects that are referenced by the execution plan
The Objects tab displays three tables (Tables in use, Indexes defined on table, Columns in table or index) that list all referenced objects in the execution plan, including their indexes and columns. Statistical details and general details are displayed for each object.
Each table has a title denoting the entities highlighted. The lower table could have a title denoting either the columns of a table or the columns of an index.
| Info |
|---|
...
If tables and indexes are not analyzed, statistics information is not available. |
About tables in use
The Tables in use lists all the referenced tables in the tree.
...
Table 8-3 Information on tables used in the plan
Column Description
...
| Column | Description |
|---|---|
 | Launches the Objects tab so that you can focus on the specified table. |
 |
...
| Locates and highlights all the steps in the execution plan that access the specified table. |
| Used |
...
| Indicates whether the specified table is used in the selected step in the execution plan tree. |
...
| Table | Displays the table name. A ToolTip displays the full name in the following format: Owner.Table_Name. |
| Rows |
...
| Number of rows in the table based on data dictionary statistics. |
| Blocks |
...
| Total number of blocks in the table. |
| Non-Empty |
...
| Blocks | Number of used blocks in the table. |
| Chained |
...
| Rows | Number of chained rows in the table. |
| Partitioned |
...
| Indicates whether the table is a partitioned table. |
| Index |
...
| Organized | Indicates whether the table is an index organized table. |
| Temporary |
...
| Indicates whether the table is a temporary table. |
| Object |
...
| ID | ID of the object. |
...
| Owner | Owner of the table. |
| I/O |
...
| Wait | Time of IO wait in the specific plan. |
About the indexes defined for the selected table
The Indexes defined for the selected table lists all the indexes that are used to access the selected table, whether they were used in the explain plan or not.
...
Table 8-4 Information on the indexes used to access the selected table
Column Description
...
| Column | Description |
|---|---|
| Launches the Objects tab so that you can focus on the specified index. |
...
| Locates and highlights all the steps in the execution plan that access the specified index. |
| Used |
...
| Indicates whether the specified index is used in the selected step in the execution plan tree. |
...
| Index | Index name. A ToolTip gives the full name, such as Owner.Index_name. |
| Unique |
...
| Indicates whether the index is unique. |
...
| Type | Index type, such as Normal (B*Tree), Bitmap, and so on. |
...
| Partitioned | Indicates whether the index is a partitioned index. |
...
| blocks | Total number of blocks in the index. |
| Leaf |
...
| Blocks | Number of leaf blocks in the index. |
| Distinct |
...
| Keys | Number of distinct keys or values in the index. |
| B- |
...
| Level | Depth of a B*Tree index. |
...
| Clustering Factor | Clustering |
...
factor of the index. The clustering factor is an important factor in determining how efficiently an index range scan will retrieve data from the table. It measures the degree to which the data in the index and its table are in the same order or, put another way, the probability that the next row to be fetched from the table is in the same block as the current row. It can vary between the number of blocks in the table (the best case because they are in the same order) and the number of rows in the table (the worst case because they are completely out of sync). The clustering factor tends to become worse over time as data is inserted and deleted. Note that the clustering factor makes no difference for a unique index lookup. |
| I/O |
...
| Wait | Time of IO wait for |
...
| index in the specific plan. |
| Last |
...
| Analyzed | Time when the table was last analyzed. |
| Object |
...
| ID | ID of the Object. |
...
| Owner | Owner of the table. |
| Locality |
...
| Indicates if the index is local or global. |
About the columns in table or index
The Columns in table or index lists all columns in the selected table displayed in Tables Used in Plan. When an index is selected in Indexes of Table, the first column constitutes the index column sorted by the position of the column in the index, and marked with an ascending or descending arrow.
...
Table 8-5 Information on the columns of a table
Column Description
...
| Column | Description |
|---|---|
 | Launch the Objects tab so that you can focus on the specified column. |
...
| Column | Column name. |
...
| Type | Physical storage type of the column. |
| Distinct |
...
| Values | Number of distinct values. |
| Key |
...
| Number | Position of the column in the index selected in Indexes of Table; otherwise blank. |
| Asc/ |
...
| Desc | Indicates whether the column is part of the selected index and whether it is sorted in ascending or descending order. |
| In |
...
| Clause | Location of the column in the statement. The column may appear in the Select clause, Where clause, Group by clause, Having clause, and Sort by clause. |
| Indexable |
...
| Indicates whether the column could be used as part of an index. A column may be indexable even though it is not currently part of any index. Alternatively, a column may not be indexable, even though it is currently part of an index, if the column does not appear within the where clause (or group by, or order by clauses) or there is a function on the column (and there is no function-based index). The Optimizer will not be able to use an index if the leading columns in the index are non-indexable (unless it can employ a skip-scan search). |
...
| Indexes | List of indexes in which the column is used. |
About statistical information on all operators in the execution plan
The Statistics tab displays statistical information on all steps in the execution plan tree. You can sort steps by their estimated cost or by any other column in the table.
...
Table 8-6 Execution plan statistics
Column Description
Locates and highlights the step in the Execution Plan tree that matches the selected step in the grid.
...