Page History
...
Table 8-18 All Plans table
Column description
...
| Column | Description |
|---|---|
 | Launches to the SQL tab with the selected plan in context. |
 |
...
| Launches to the Compare view so that you can compare the current plan and the selected plan. |
| Plan Hash |
...
| Value | Displays the plan's hash value, as computed by Oracle. |
| Plan Type |
...
| icon | Indicates whether a plan is a real or estimated execution plan. |
| First |
...
| Detected | For actual plans, displays when the plan was first encountered. For estimated plans, displays when the statement was first explained and the plan was produced. |
| Last |
...
| Detected | For actual plans, displays when the plan was last encountered. For estimated plans, displays when the statement was last explained and the plan was produced. |
| In |
...
| Oracle | Displays the total time spent in Oracle by statements which were using this plan during the selected time frame. |
| Duration (Avg.) |
...
| Displays the average amount of time it took the plan to run. | |
| Cost |
...
| Displays the last cost retrieved for the plan. |
| Executions |
...
| Displays the number of times the plan was executed, during the selected time frame. |
About the all plans overview
The All Plans overview lets you analyze how plans changed over time. This information is displayed in the following graphs:
- In Oracle (Avg.) vs. Executions. It displays the average time spent in Oracle vs. the number of executions, over time. This graph lets you analyze the scalability of the different plans displayed in the All Plans table.
It notices that at a certain point in time one of the plans was replaced by another and that the AVG in-oracle time of the second plan is considerably higher than the first one.
- In Oracle. It displays the total time spent in Oracle by statements which were using this plan over time.
- Cost. It displays the cost of the retrieved for the plan over time.
About the Plan tree
The Plan tree displays execution plan of the selected statement.
About the Bind Variables view
A bind variable is a placeholder in a SQL statement that must be replaced with a valid value (or address of a value) before the statement can be successfully executed. The execution program passes the value of the bind variable to Oracle when the statement is processed. Understanding which values were used when a statement was run, can be important to the tuning process.
...
- Run a statement with a real set of bind values, and observe how different binds sets influence the statement's execution).
- Note the existence of different sets of bind values for a statement.
- Determine if a correlation can be drawn between a statements performance to the bind values used during the period in question.
About the Bind Variables table
The following information is displayed for each bind set in the Bind Variables table in the left pane:
Table 8-19 Bind set
...
| Bind Set | Description |
|---|---|
| Plan Match |
...
| icon | Icon that indicates whether the plan's hash value matches the hash value of the real execution plan. |
| Estimated |
...
| Cost | Displays the estimated cost of the plan. |
| Best Plan Hash |
...
| Value | Displays the best hash value of the selected plan. This information is displayed after you run Get Best Plan. If different hash values are displayed, this may indicate that there is a matching problem. |
| Date Last |
...
| Captured | Displays the date and time the bind set was last captured. |
| Bind Variables (B1B50) |
...
| Displays the values of bind variables B1 through B50. Precise for Oracle can display up to 50 bind variables. | |
| Duplicated |
...
| Sets | The number of times this specific bind set was collected. |
Viewing information displayed for a particular bind set
You can view the text of a particular bind set, and copy an estimation of the text to another tool, in the Details area (right pane) of the Bind Variables view. You can view the following text for a bind set:
Table 8-20 Bind set
...
| Bind Set | Description |
|---|---|
| Text, with bind variables |
...
| replaced | Displays source text with bind variables replaced by the actual values run by the statement. |
| Text for |
...
| Estimation | Displays source text and hints that instruct Oracle how to sample the table's data and create an optimal plan for the same bind variables. |
| Bind variables |
...
| metadata | Displays additional information for the bind variables of the selected bind set (such as, bind name and type, and column name and type). This information can be useful in cases where the same bind name is used for different columns, with different data types. |
To view the text of a bind set, with variables replaced
- In the Bind Variables table, click on the plan whose text you want to view.
In the Details area, click on the
Text, with bind variables replacedtab.
...
Info You can select and copy this text to another tool for further analysis.
To obtain an estimate of the bind set's text
- In the Bind Variables table, click on the plan whose text you want to view.
- In the Details area, click on the Text for Estimation tab.
- You can select and copy this text to another tool for further analysis.
About Findings in the Details area
Whenever you select a step on the tree, the children appear as formatted text under Highlights in the Details area. At the top of the Details area, you find the date and time of the sampled execution plan, and its formatted text. Findings appear below the formatted text; it includes a table that lists the severity of each type of sort or other operation, and the name of each object that is referenced by the sampled execution plan.
About the findings table
The Findings table is the result of a proprietary tuning algorithm that recommends normal B*Tree indexes, bitmap indexes, index-only access, or statistics gathering on tables and/or indexes for the selected statement. Based on these recommendations, the Oracle Optimizer can choose a better access plan and improve the performance of the statement.
The following table describes the Findings table.
Table 8-21 Findings table
| Column |
|---|
...
| Description |
|---|
...
| Severity |
...
Indicates the severity alert that occurred during the sampled execution plan. Severity is displayed using the following colors: |
■ Top findings: red
■ Medium findings: orange
■ Other findings: yellow
...
| ||
| Launches to a tab in context with the chosen object. | |
| Type |
...
| Indicates the type of operation. Notice that the given type may be underlined. This indicates a live link ToolTip. Select the live link type to view ToolTip recommendations to better access the execution plan and improve the performance of the statement. |
| Object |
...
| Lists the object in the type of operation that is referenced by the execution plan. |
| Impact (%) |
...
| Indicates the maximum theoretical saving, expressed as a percentage of the total In Oracle time consumed. Note that the actual saving that can be made depends on the activity. | |
| In |
...
| Oracle | Indicates the time used to perform this operation In Oracle for this execution plan. |
About the Expanded view
The Expanded view displays recommendations that the Oracle Optimizer can use to create a better access plan and improve the performance of the statement.
...
- Highlights
- What To Do Next
About the Highlights area
The Highlights area displays a brief description of the findings for this type of operation. It also contains a link which provides more help.
About the What To Do Next area
The What To Do Next area displays one or more recommended steps to identify the cause of the problem. Carefully review all data for the finding before continuing.
How to investigate Findings
When you start investigating the findings, it is good practice to start with the finding that has the highest severity rank in the Findings table.
...
- Identify the finding with the highest severity rank in the Findings table.
- Select the finding type to view the expanded information for this type of operation.
- Read the Highlights and What To Do Next areas for the finding and perform the advice that best suits your needs.
- Follow up on performance to verify that the problem was resolved.
About a statement's different versions
The Compare view lists the different versions of a statement that have been saved together. It lets you compare Oracle Optimizer cost and execution statistics for each alternative so that you can choose the most efficient one. You can create related statements by doing any of the following:
- Clicking Edit in the Action menu and saving a new version of the statement.
- Clicking New Alternatives in the Action menu to generate alternatives.
Statements that are generated this way are named Alternative-nnn. Running a What-If simulation.
Statements that are generated this way are named Predicted-nnn.
Info
...
The alternative versions of a statement only appear in the Activity or What-If tabs or in the Open Statement dialog box after they have been run and captured by the Collector.
The information displayed in the Related SQL view is controlled by the information tabs (Details, Text, Run Statistics) that are located above this area.
About Optimizer cost and more for an alternative
When you click the Run Alternative tab (on the right pane), the cost and more is shown in the Details tab (left pane) for each alternative.
...
Table 8-22 Details tab information
| Column |
|---|
...
| Description |
|---|
...
 |
...
| Switches to Plan view, in context with the selected related SQL statement. |
|
...
| Available for related SQL statements but not for the original statement. |
| Name |
...
| Name of the statement. |
| Statement |
...
| ID | ID of the statement. |
| Timestamp |
...
| Last run date and time of the statement. |
| Cost |
...
| Bar graph showing the Oracle Optimizer cost for the statement. |
| Plan Hash |
...
| Value | Display plan hash value. |
| Duration (Avg.) |
...
| Average time of how long the statement took to run. | |
| Logical I/O (Avg.) |
...
| Number of average logical I/Os from the disk. | |
| Physical |
...
| Reads | Number of physical reads from disk. |
| Hit |
...
| Ratio | Number of physical reads or of logical reads, expressed as a percentage. |
| Table |
...
| Scans | Number of full table scans performed. |
| Write |
...
| Requests | Number of requests to write data to disk, usually of temporary data during joins and sorts. |
| Sorts |
...
| Number of sorts performed. |
| Sorted |
...
| Rows | Number of sorted rows. |
| Table Scans by Row |
...
| ID | Number of accesses to a table by Row ID. A Row ID contains the address of a row in a table and is the fastest way to gain access to an individual row, although not necessarily to multiple rows. Access using Row ID usually follows an index scan, because Row IDs are stored in the leaf blocks of an index. |
| Table Scan Rows |
...
| Gotten | Number of rows retrieved from tables. |
| Table Scan |
...
| Blocks | Number of blocks fetched from tables. |
| Recursive |
...
| Calls | Number of recursive SQL calls that Oracle made to the data dictionary while executing the statement. |
| Processed |
...
| Rows | Number of rows processed during the execution. This includes rows retrieved from tables, indexes, and temporary segments. It may be many more than are returned. |
| Host |
...
| Name | Name of the host. |
| Description |
...
| Description |
| Attempted |
...
| Executions | Number of executions started. |
| Actual |
...
| Executions | Number of actual executions performed. |
About the SQL text for each alternative
The Text tab displays the SQL text for each alternative.
...