Page History

...

The following table lists the tabs and entities from which you can launch the SQL tab.

Table 8- 1 Launching the SQL tab in context

In the Current, Activity, and Objects tabs, you can either launch the SQL tab by clicking the SQL tab button when the selected entity in the Main area is a statement, or by selecting a statement in the Association area and clicking the Tune icon that appears before the SQL text.

...

The following table describes the information displayed in the Plan view.

Table 8- 2 Information tabs

...

The following table describes the information displayed for the tables that are accessed in the execution plan.

Table 8- 3 Information on tables used in the plan

Column	Description
Image Modified	Launches the Objects tab so that you can focus on the specified table.
Image Modified	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 following table describes the information displayed for the indexes that are used to access the selected table.

Table 8- 4 Information on the indexes used to access the selected table

Column	Description
Image Modified	Launches the Objects tab so that you can focus on the specified index.
Image Modified	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 following table describes the information displayed for the columns of the table.

Table 8- 5 Information on the columns of a table

Column	Description
Image Modified	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 following table shows the information displayed in statistics table for the execution plan.

Table 8- 6 Execution plan statistics

Column	Description
Image Modified	Locates and highlights the step in the Execution Plan tree that matches the selected step in the grid.
Step ID	Displays the number of the explain plan step.
Step	Provides a short description of the explain plan step.
Estimated Cost	Estimated cost of the current operation. A high cost value may indicate a problem in the current implementation of the operation. Check the Estimated I/O cost and Estimated CPU cost values to determine whether the operation is an I/O consuming operation or a CPU consuming operation (or both).
Estimated Rows	Estimated number of rows returned by this step.
Estimated Bytes	Estimated number of bytes returned by this step.
Estimated CPU Cost	The estimated CPU cost of the current operation. A high cost value may indicate a problem in the current implementation of the operation.
Estimated I/O Cost	Estimated I/O cost of the current operation. A high cost value may indicate a problem in the current implementation of the operation.
Partitioned ID	Name of the table or index partition, if the step involves a partitioned table or index.
Partition Start	Low value of the partition key.
Partitioned Stop	High value of the partition key.
Access Predicates	Predicates used to locate rows in an access structure; for example start or stop predicates for an index range scan (as defined in Oracle documentation).
Filter Predicates	Predicates used to filter rows before producing them (as defined in Oracle documentation).
#	Step #.
In Oracle	In Oracle time for the specific step.
Cost	Cost for the specific step.
Other Tag	Data as seen in Oracle for specific step.
Plan Hash	Plan hash value.

About information on statement plan, location, and text

...

The following table describes how the information on the Workshop tab is structured.

Table 8- 7 Structure of information on the Workshop tab

About the Recommend button

...

The following table describes the information displayed for the alternative statements.

Table 8- 8 Alternative Statements

Column	Description
Image Modified	Launches to the Plan view with the statement in context and lets you view the statement's execution plan, in the Highlights tab.
Image Modified	Lets you compare the alternative statement with the original statement.
Status	Displays whether or not the statement was successfully run, or whether it is still running or has timed out.
Name	The information displays for the following statement names: For an alternative statement, it displays the alternative statement's name. For the original statement, it displays the original statement's name. For an alternative run of a statement, it displays the name of the alternative or statement used as a base for running the alternative statement.
Timestamp	Displays the time the statement was run.
Plan Hash Value	Displays the hash value for the plan associated with the selected statement.
Statement ID	Displays the statement ID.
Duration (Avg.)	Displays how long the statement ran.
Cost	Displays the average cost of the plan.
Logical I/O (Avg.)	Displays the average number of logical I/O operations per executed statement.
Physical Reads	Number of physical reads from disk.
Hit Ratio	Number of physical reads or 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	Total 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 via 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	Displays the host name of the server that the statement is running on.
Description	If you chose to run an alternative of the statement, this field displays the description assigned to the run in the Run Statement dialog box.
Attempted Executions	Number of executions started.
Actual Executions	Number of executions completed.

About the text of an alternative statement

...

The following table describes the additional statistics information displayed for each alternative.

Table 8- 9 Extended Statistics table

About the History view of resource consumption

...

The Overview, in the SQL tab, displays the following overtime graphs:

Table 8- 10 Graphs

About the text of a statement in the History view

The Text view, in the SQL tab, displays the full SQL text of the statement and information on the statement and execution plan, as follows:

Table 8- 11 Text view display

About the Change History area

...

The following table describes the information displayed in the All Changes table.

Table 8- 12 All Changes table

Column	Description
Image Modified	Switches to the Plan view and displays the first access plan that was created by the Explain Statements process after the specified change was detected.
Image Modified	If the object that was changed is one of the entities in the Objects tab (table, view, index, or column), this icon will launch the Objects tab with the selected object in context. Also, if the change type is Access Plan Changed, the icon will launch the Compare view with the specified access plan compared to the last access path, as detected by the Explain Statements process.
Timestamp	Indicates the date that the change was detected.
Change Type	Displays the type of change detected. Can be one of the following values: Table Created Table Dropped Table Altered Index Created Index Dropped Index Altered Table Statistics Changed Initial Execution Plan Different Execution Plan
Object	Indicates the object that was changed, such as table or index.
Change Details	Displays information about the change that was made. For example, if the Change Type is Different Execution Plan, the Change Details column gives the estimated cost.

About schema changes

The Schema Changes view displays a list of all schema type changes made.

The following table describes the information displayed in the Schema Changes table.

Table 8- 13 Schema Changes table

Column	Description
Image Modified	Switches to the Plan view and displays the first access plan that was created by the Explain Statements process after the specified change was detected.
Image Modified	If the object that was changed is one of the entities in the Objects tab (table, view, index, or column), this icon will launch the Objects tab with the selected object in context.
Timestamp	Indicates the date the change was detected.
Change Type	Displays the type of change detected. It can be one of the following values: Table Created Table Dropped Table Altered Index Created Index Dropped Index Altered
Object	Indicates the object that was changed, such as table or index.
Columns	Indicates the columns in the object that were affected by the change.

About statistics changes

The Statistics Changes view displays a list of changes made to Oracle statistics on tables, indexes, and columns. The following table describes the information displayed in the Statistics Changes table.

Table 8- 14 Statistics Changes table

Column	Description
Image Modified	Switches to the Plan view and displays the first access plan that was created by the Explain Statements process after the specified change was detected.
Image Modified	Launches the Objects tab with the selected instance.
Timestamp	Indicates the date the change was detected.
Object	Indicates the object that was changed, such as table or index.
Type	Displays the type of object, such as table or index.
Rows	A change in the number of rows.
Non-Empty Blocks	A change in the number of non-empty blocks.
Free Space (Avg.)	A change in the average free space in a block.
Chained Rows	A change in the number of chained rows.
Distinct Keys	A change in the number of distinct keys in an index.
Leaf Blocks	A change in the number of leaf blocks in an index.
Clustering Factor	A change in the clustering factor of an 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.

About execution plan changes

...

The following table describes the information displayed in the Execution Plan Changes table.

Table 8- 15 Execution Plan Changes table

Column	Description
Image Modified	Switches to the Plan view and displays the first access plan that was created by the Explain Statements process after the specified change was detected.
Image Modified	Opens the access plan in the Compare view and lets you compare the specified access plan with the last access plan, as detected by the Explain Statements process.
Timestamp	Indicates the date the specified access plan was detected by the Explain Statements process.
Cost	Estimated cost calculated by the Oracle cost-based Optimizer.
Nested Loops	Number of nested loop operations performed.
Hash Joins	Number of hash joins performed.
Merge Joins	Number of merge joins performed.
Sorts	Number of sorts performed.
Table Scans	Number of full table scans performed.
Index Scans	Number of index scans performed.
Steps	Bar graph showing the number of steps in the execution plan and a visual breakdown of the different types of operations performed.

About the run statistics history

...

The following table explains the information shown on the General tab.

Table 8- 16 Information shown on the General tab

The following table explains the information shown on the Statistics tab.

Table 8- 17 Information shown on the Statistics tab

Comparing execution plans

...

The following table describes the information displayed in the All Plans table.

Table 8- 18 All Plans table

Column	Description
Image Modified	Launches to the SQL tab with the selected plan in context.
Image Modified	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 following information is displayed for each bind set in the Bind Variables table in the left pane:

Table 8- 19 Bind set

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

To view the text of a bind set, with variables replaced

...

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: Red. Top findings. Orange. Medium findings. Yellow. Other findings. Info An orange or yellow finding in an execution plan with many findings, may become a red finding in an execution plan with minimum findings because it becomes one of the top findings.
Image Modified	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.

...

The following table describes the information displayed on the Details tab.

Table 8- 22 Details tab information

Column	Description
Image Modified	Switches to Plan view, in context with the selected related SQL statement.
Image Modified	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

...

It is possible to perform the following tuning actions:

Table 8- 23 Tuning actions

Opening an existing statement

...

The alternate SQL has no function around the C_LAST column. Therefore, Oracle is able to use the index CUSTOMER_I2 index to more efficiently access the records in the CUSTOMER table.

...

.

...