Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

After a statement is explained, the explain results are stored in the PMDB. This information includes the objects referenced by the statement and the operations performed on these objects. The top statements are automatically explained every day. You can control how many statements to explain using a setting for the Explain Statements PMDB process in AdminPoint. For more information, see the see the Precise Administration Guide.

Statements are also parsed by a proprietary Precise for Oracle parser. Parsing allows Precise for Oracle for example to highlight statement objects from the execution plan and to format execution plans. The number of statements that are parsed is again governed by a setting in the PMDB. For more information, see the Precise Administration Guide.

Understanding the execution plan chosen by the Oracle Optimizer is extremely important when tuning your application. You can ensure optimal system performance by ensuring that the best plans are used for your queries.

...

An important concept within the SQL tab is that of alternative or related SQL. You can take a statement and create different alternatives for it as part of the tuning process. There are a number of different ways of doing this. For example, you may re-write a statement yourself so that it gives the same result in a more efficient manner; or you may click Actions>Generate Actions > Generate Alternatives to get Precise for Oracle to generate alternatives for you; or you may change a statement's execution plan by simulating the addition of an index in the What-If tab.

...

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

TabEntities
DashboardStatement
CurrentStatement, Active or Current Session that is currently executing a statement.
ActivityStatement, PL/SQL
ObjectsStatement, PL/SQL
What-IfStatement that is affected by one or more index evaluations.

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.

...

There can be statements with many execution plans. Only the latest 7 seven plans appear on the tree. Click More..., if If available, to click More to view additional execution plans. See “About About the Dashboard tab” on page 43, “About tab, About the Current tab” on page 51tab, "About the Activity tab” on page 61tab, and “About About the Objects tab” on page 87tab.

Anchor
HowtheSQLtabisstructured
HowtheSQLtabisstructured
How the SQL tab is structured

...

  • When you edit a statement and save an alternative. See “Editing Editing an existing statement” on page 154statement.
  • 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 Generating new alternatives.
  • 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 About the What-If tab” on page 159tab.

Anchor
AbouttheViewtabs
AbouttheViewtabs
About the View tabs

...

The following views are available:

  • Plan
  • Run Alternatives
  • More ...

About viewing the execution plan of a statement

...

  • 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.

    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.

The Plan view is divided into two panes. The Execution Plan tree is displayed in the left pane. The Details area is displayed in the right pane. The information displayed in the Details area is controlled by the information tabs (Highlights, Expanded Text, Objects, and More...) that are located above this area.

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

Table 8- 2 Information tabs

Information TabsDescription
HighlightsDisplays 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 TextShows all views and synonyms expanded inline allowing you to match the access steps to the version of SQL that Oracle is actually executing.
ObjectsDisplays 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:

  • Statistics view. Displays statistical information on all steps in the execution plan tree
  • Workshop view. Displays details on the execution plan, where the statement is stored, and the SQL text of the statement
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.

...

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 About expanded text of a statement in the Plan view” on page 135view.

Actions that can be performed on the tree

...

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.

Anchor
AboutexpandedtextofastatementinthePlanview
AboutexpandedtextofastatementinthePlanview
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.

...

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

ColumnDescription
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.
UsedIndicates 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.

RowsNumber of rows in the table based on data dictionary statistics.
BlocksTotal number of blocks in the table.
Non-Empty BlocksNumber of used blocks in the table.
Chained RowsNumber of chained rows in the table.
PartitionedIndicates whether the table is a partitioned table.
Index OrganizedIndicates whether the table is an index organized table.
TemporaryIndicates whether the table is a temporary table.
Object IDID of the object.
OwnerOwner of the table.
I/O WaitTime 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.

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

ColumnDescription
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.
UsedIndicates whether the specified index is used in the selected step in the execution plan tree.
IndexIndex name. A ToolTip gives the full name, such as Owner.Index_name.
UniqueIndicates whether the index is unique.
TypeIndex type, such as Normal (B*Tree), Bitmap, and so on.
PartitionedIndicates whether the index is a partitioned index.
blocksTotal number of blocks in the index.
Leaf BlocksNumber of leaf blocks in the index.
Distinct KeysNumber of distinct keys or values in the index.
B-LevelDepth 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 WaitTime of IO wait for index in the specific plan.
Last AnalyzedTime when the table was last analyzed.
Object IDID of the Object.
OwnerOwner of the table.
LocalityIndicates 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

ColumnDescription
Image Modified
Launch the Objects tab so that you can focus on the specified column.
ColumnColumn name.
TypePhysical storage type of the column.
Distinct ValuesNumber of distinct values.
Key NumberPosition of the column in the index selected in Indexes of Table; otherwise blank.
Asc/DescIndicates whether the column is part of the selected index and whether it is sorted in ascending or descending order.
In ClauseLocation 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.
IndexableIndicates 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).
IndexesList 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

ColumnDescription
Image Modified
Locates and highlights the step in the Execution Plan tree that matches the selected step in the grid.
Step IDDisplays the number of the explain plan step.
StepProvides a short description of the explain plan step.
Estimated CostEstimated 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 RowsEstimated number of rows returned by this step.
Estimated BytesEstimated number of bytes returned by this step.
Estimated CPU CostThe 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 CostEstimated I/O cost of the current operation. A high cost value may indicate a problem in the current implementation of the operation.
Partitioned IDName of the table or index partition, if the step involves a partitioned table or index.
Partition StartLow value of the partition key.
Partitioned StopHigh value of the partition key.
Access PredicatesPredicates 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 PredicatesPredicates used to filter rows before producing them (as defined in Oracle documentation).
#Step
#
number.
In OracleIn Oracle time for the specific step.
CostCost for the specific step.
Other TagData as seen in Oracle for specific step.
Plan HashPlan 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

ItemDescription
Explained onDate the latest explain plan was first generated.
CostEstimated cost calculated by the Oracle cost-based Optimizer.
Parsing UserIn the case of an imported or manually saved statement, this is the Oracle user that was specified at the time; in the case of an automatically captured statement, it is the first Oracle user that the Precise for Oracle Collector found running the statement.
CabinetCabinet where the statement is stored.
FolderFolder within the cabinet where the statement is stored.
Origin

Source of the statement. The Precise for Oracle Collector will automatically capture most statements when they are executing in Oracle, but some may be imported from source files or saved manually. Possible values are:

  • Automatically collected
  • Generated as related SQL
  • Predicted plan (What-If)
  • Entered by user
  • Imported from source file
  • Generated as related SQL by user
Saved onDate that the statement was saved.
Parsing InformationIndicates whether Precise for Oracle has performed an extra level of parsing above that performed by Oracle to support more detailed analysis and syntax color highlighting.
CommentUser-defined comment that can be entered against a statement when it is saved. See
“Editing
Editing the properties of a
statement” on page 31.
statement.

About the Recommend button

...

Info

The Precise Advisor on the SQL tab will be used instead of the Oracle Advisor when: the Oracle Advisor is not installed, the Oracle version does not support it, or the usage of the Oracle Advisor was manually disabled in our application (for more information on how to manually disable the default Oracle Advisor, see “Disabling Disabling the Oracle Advisor” on page 139.Advisor.

Anchor
DisablingtheOracleAdvisor
DisablingtheOracleAdvisor
Disabling the Oracle Advisor

When you decide to disable the Oracle Advisor, you have to make changes in the registry file per instance. The result will be that the Precise Advisor will be used with the following limitation:

...

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

Table 8- 8 Alternative Statements

ColumnDescription
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.
StatusDisplays 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.
TimestampDisplays the time the statement was run.
Plan Hash ValueDisplays the hash value for the plan associated with the selected statement.
Statement IDDisplays the statement ID.
Duration (Avg.)Displays how long the statement ran.
CostDisplays the average cost of the plan.
Logical I/O (Avg.)Displays the average number of logical I/O operations per executed statement.
Physical ReadsNumber of physical reads from disk.
Hit RatioNumber of physical reads or logical reads, expressed as a percentage.
Table ScansNumber of full table scans performed.
Write RequestsNumber of requests to write data to disk, usually of temporary data during joins and sorts.
SortsNumber of sorts performed.
Sorted RowsTotal number of sorted rows.
Table Scans by Row IDNumber 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 GottenNumber of rows retrieved from tables.
Table Scan BlocksNumber of blocks fetched from tables.
Recursive CallsNumber of recursive SQL calls that Oracle made to the data dictionary while executing the statement.
Processed RowsNumber 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 NameDisplays 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 ExecutionsNumber of executions started.
Actual ExecutionsNumber 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

ColumnDescription
#Displays the sequential number of the step.
Step IDDisplays the number of the explain plan step.
StepProvides a short description of the explain plan step.
RowsDisplays the number of rows returned by this step.
TimeDisplays when the alternative statement was last run.
BlocksDisplays the total number of blocks in the alternative statement.
ExecutionsDisplays the number of executions of the alternative statement.

About the History view of resource consumption

...

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

Table 8- 10 Graphs

GraphDescription
In Oracle (Avg.)Illustrates the average resource consumption of the statement over time; does not detail the states or substates.
CostDisplays the estimated cost calculated by the Oracle cost-based optimizer over time. This information is generated whenever you explain the statements using Precise for Oracle.
Changes

Marks significant events that affect the statement and the objects that it accesses over time, namely analyze statistics changes, schema changes, and execution plan changes.

The execution plan changes information is generated whenever you explain the statements using Precise for Oracle, either using the PMDB process or the SQL tab.

The statistics changes information is generated whenever you run the PMDB process Collect Schema Changes. It scans all the objects referenced by the statement.

The schema changes information is generated whenever you run the PMDB process Collect Schema Changes. It scans all the objects referenced by the statement. See

“About

About the Change History

area” on page 143

area.

ExecutionsDisplays the number of executions of the selected statement over time.

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

InformationDescription
CabinetThe cabinet where the statement is stored.
FolderThe folder within the cabinet where the statement is stored.
Origin

The source of the statement. The Precise for Oracle Collector automatically captures most statements when they are executing in Oracle, but you can also import statements from source files or save them manually.

A statement can be automatically collected, generated as related SQL, part of a predicted plan in the What-If tab, entered by users, imported from a source file, or generated as a related SQL by a user.

Saved onThe date that the statement was saved.
Parsing Information

Indicates whether Precise for Oracle has performed an extra level of parsing above that performed by Oracle to support more detailed analysis and syntax color highlighting.

For statements that are explained in the background, parsing must be enabled in the Explain Statements PMDB process in AdminPoint (see

the

the Precise Administration Guide) for statements that are explaining in the background. For statements that you explain in the SQL tab, parsing must be enabled from

Settings>General Settings>SQL

Settings > General Settings > SQL.

By default, parsing is enabled. Some statements cannot be parsed. See the Precise Administration Guide

. See “About SQL settings” on page 27

and About SQL settings.

Explained onThe date the latest explain plan was generated.
CostThe estimated cost calculated by the Oracle cost-based optimizer.
Parsing UserIn the case of an imported or manually saved statement, the Oracle user that was specified at the time; in the case of an automatically captured statement, the first Oracle user that the Precise for Oracle Collector found running the statement.

Anchor
AbouttheChangeHistoryarea
AbouttheChangeHistoryarea
About the Change History area

The Change History area displays the changes that may have affected the performance of the statement over time. Information on the following type of changes is displayed:

...

The All Changes view displays a list of all changes made. The changes can be statistics changes, schema changes, and execution plan changes. If there are many changes, you may want to use one of the other change types or filter your search using the More... option. See “Associating Associating entities with data that meets specific criteria” on page 30criteria.

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

Table 8- 12 All Changes table

ColumnDescription
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.
TimestampIndicates 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
ObjectIndicates the object that was changed, such as table or index.
Change DetailsDisplays 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

ColumnDescription
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.
TimestampIndicates 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
ObjectIndicates the object that was changed, such as table or index.
ColumnsIndicates 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

ColumnDescription
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.
TimestampIndicates the date the change was detected.
ObjectIndicates the object that was changed, such as table or index.
TypeDisplays the type of object, such as table or index.
RowsA change in the number of rows.
Non-Empty BlocksA change in the number of non-empty blocks.
Free Space (Avg.)A change in the average free space in a block.
Chained RowsA change in the number of chained rows.
Distinct KeysA change in the number of distinct keys in an index.
Leaf BlocksA 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

ColumnDescription
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.
TimestampIndicates the date the specified access plan was detected by the Explain Statements process.
CostEstimated cost calculated by the Oracle cost-based Optimizer.
Nested LoopsNumber of nested loop operations performed.
Hash JoinsNumber of hash joins performed.
Merge JoinsNumber of merge joins performed.
SortsNumber of sorts performed.
Table ScansNumber of full table scans performed.
Index ScansNumber of index scans performed.
StepsBar 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 Run Statistics History view displays Oracle run statistics from when the statement has been run manually from within the tool. Any changes that it makes are rolled back. The only overhead is the execution time. A time-out may be specified in the Run dialog box to prevent the statement from taking too long. See “Running a statement” on page 155 Running a statement.

The Run Statistics History is a useful way of testing the performance of different SQL alternatives to see which is the fastest before it is put into production. Until it is run, the Collector will not have captured its execution performance. Another use is to benchmark the performance of certain key statements over time.

...

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

Table 8- 16 Information shown on the General tab

ColumnDescription
TimestampDisplays when the statement was run.
DurationHow long the statement took to run.
Processed RowsNumber of rows processed during the execution. This includes rows retrieved from tables, indexes, and temporary segments. It may be many more than are returned.
Physical ReadsNumber of physical reads from disk.
Attempted ExecutionsNumber of executions started.
Actual ExecutionsNumber of executions completed.
Timed OutDenotes whether the timeout specified in the Run dialog was reached. The timeout is for all executions.
Timeout (Sec.)Timeout in seconds that was specified in the Run dialog box.
StatusOne of the following states: Started, Completed, or an error message if an error has occurred.

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

Table 8- 17 Information shown on the Statistics tab

ColumnDescription
TimestampWhen the statement was run.
Logical ReadsNumber of logical reads.
Hit RatioNumber of physical reads or logical reads, expressed as a percentage.
Write RequestsNumber of requests to write data to disk, usually of temporary data during joins and sorts.
SortsNumber of sorts performed.
Sorted RowsTotal number of sorted rows.
Table ScansNumber of full table scans performed.
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 GottenNumber of rows retrieved from tables.
Table Scan BlocksNumber of blocks fetched from tables.
Recursive CallsNumber of recursive SQL calls that Oracle made to the data dictionary while executing the statement.

Comparing execution plans

...

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

Table 8- 18 All Plans table

ColumnDescription
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 ValueDisplays the plan's hash value, as computed by Oracle.
Plan Type iconIndicates 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 OracleDisplays 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.
CostDisplays the last cost retrieved for the plan.
ExecutionsDisplays the number of times the plan was executed, during the selected time frame.

About the all plans overview

...

The Plan tree displays execution plan of the selected statement.

Anchor
AbouttheBindVariablesview
AbouttheBindVariablesview
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.

...

The following information is displayed for each bind set in the Bind Variables table in the left pane:

Table 8- 19 Bind set

Bind SetDescription
Plan Match iconIcon that indicates whether the plan's hash value matches the hash value of the real execution plan.
Estimated CostDisplays 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 CapturedDisplays 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 SetsThe 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 SetDescription
Text, with bind variables replacedDisplays source text with bind variables replaced by the actual values run by the statement.
Text for EstimationDisplays 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

...

The following table describes the Findings table.

Table 8- 21 Findings table

ColumnDescription
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.

ObjectLists 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 OracleIndicates 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.

...

  1. Identify the finding with the highest severity rank in the Findings table.
  2. Select the finding type to view the expanded information for this type of operation.
  3. Read the Highlights and What To Do Next areas for the finding and perform the advice that best suits your needs.
  4. Follow up on performance to verify that the problem was resolved.

Anchor
Aboutastatementsdifferentversions
Aboutastatementsdifferentversions
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:

...

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

Table 8- 22 Details tab information

ColumnDescription
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.
NameName of the statement.
Statement IDID of the statement.
TimestampLast run date and time of the statement.
CostBar graph showing the Oracle Optimizer cost for the statement.
Plan Hash ValueDisplay 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 ReadsNumber of physical reads from disk.
Hit RatioNumber of physical reads or of logical reads, expressed as a percentage.
Table ScansNumber of full table scans performed.
Write RequestsNumber of requests to write data to disk, usually of temporary data during joins and sorts.
SortsNumber of sorts performed.
Sorted RowsNumber 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 GottenNumber of rows retrieved from tables.
Table Scan BlocksNumber of blocks fetched from tables.
Recursive CallsNumber of recursive SQL calls that Oracle made to the data dictionary while executing the statement.
Processed RowsNumber 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 NameName of the host.
DescriptionDescription
Attempted ExecutionsNumber of executions started.
Actual ExecutionsNumber 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

ActionDescription
OpenOpen an existing statement.
NewCreate a new statement.
Edit PropertiesEdit the properties on an existing statement. See
“Editing
Editing the properties of a
statement” on page 31
statement.
Edit TextEdit an existing statement.
RunRun a statement.
Re-ExplainRe-explain a statement.
RecommendGenerate new recommendations.
Generate AlternativesGenerate new alternatives.
Get Best PlanGet the best plan according to collected bind sets.

Opening an existing statement

...

Info

You cannot open statements that were generated as related SQL. You must first open the original statement and then select the related SQL by using the Related SQL tab. See “Editing Editing the properties of a statement” on page 31statement.

To open an existing statement

  1. Click Actions>Open Actions > Open.
  2. In the Open Statement dialog box, choose the relevant properties for the statement that you want to view from the drop-down lists, as follows:
    • Instance. Indicates the name of the instance that the statement belongs to.
    • Cabinet. Indicates the name of the cabinet that the statement is saved in.
    • Folder. Indicates the name of the folder that the statement is saved in.
    • Statement. Indicates the ID or user-defined name of the statement.
    • Comment. Provides an optional comment that was previously associated with the statement.
    • Text. Provides a preview of the SQL text for the currently selected statement.
      The maximum number of items displayed in the Cabinet, Folder, and Statement lists is limited.
  3. Click OK.

...

Statements collected by the Collector are automatically saved in the default cabinet and folder, so it is recommended to choose another cabinet and folder. See “Editing Editing the properties of a statement” on page 31statement.

To create a new statement

  1. Click Actions>New Actions > New.
  2. In the New Statement dialog box, choose the relevant properties for the new statement from the drop-down lists and fill in the fields, as follows:
    • Instance. Indicates the name of the instance that the statement belongs to.
    • User. Indicates the Oracle user name that will parse the statement.
    • Cabinet. Indicates the name of the cabinet that the statement is saved in.
    • Folder. Indicates the name of the folder that the statement is saved in.
    • Statement. Indicates the user-defined name of the statement.
    • Comment. Provides an optional tuning comment that is to be associated with the statement.
    • Text. Indicates the SQL text for the statement.
      The maximum number of items displayed in the Cabinet, Folder, and Statement lists is limited. You may also type the names.
  3. In the Text field, enter the text for the statement.
  4. In the Comment field, type a tuning comment that is associated with the statement, if required.
  5. Click OK. The new statement is saved in the PMDB in the cabinet and folder hierarchy.

Anchor
Editinganexistingstatement
Editinganexistingstatement
Editing an existing statement

The Edit Text option lets you edit a statement that was saved in the PMDB through the New option. Collected and imported statements cannot be edited. However, you can use the Edit dialog box to save a related SQL statement and then edit the related SQL statement.

...

  • Plan
  • Run Alternatives
  • All Plans

See “Editing Editing the properties of a statement” on page 31statement.

To edit an existing statement

  1. Click Actions>Edit Actions > Edit Text. The Edit Statement dialog box opens.
  2. In the Edit Statement dialog box, do one of the following:
    • Enter a new name for the statement in the text field to rename the existing statement.
    • Enter a new name for the statement, check the Save as alternative option to save your changes under a different name.
    Either choose or save it as a related statement by entering a new name. If the statement was collected by the Collector, you can only save it under a new name. In this case, the Save as alternative option is pre-selected and cannot be unchecked. See “About About a statement's different versions” on page 151versions.
  3. In the Text box, edit the SQL text for the statement.
  4. Click Save. The edited statement is saved in the same cabinet and folder as the original.

Anchor
Runningastatement
Runningastatement
Running a statement

The Run option lets you run a statement in the monitored database so that you can gather its execution statistics. Any changes that it makes are rolled back. The only overhead is the execution time. A timeout may be specified in the Run dialog box to prevent the statement execution from running too long.

You can run a statement in the Run Alternatives view. see “Editing See Editing the properties of a statement” on page 31statement.

To run a statement

  1. Click Actions>Run Actions > Run.
  2. On the General tab in the Run Statement dialog box, do the following:
    1. Select the database instance that the statement will run on. The maximum number of items displayed in the Cabinet, Folder, and Statement lists is limited.
    2. In the Description box, type a short description of the run.
    3. Enter the Oracle user name and password with which to log in to Oracle.
    4. Choose one of the following options: Fetch all rows or Fetch first n rows
    5. Specify the number of times that you want to run the statement. You may want to run it more than once to reduce the effect of having to load the buffer cache on the first run. Precise for Oracle calculates and displays the average value for each statistic over the series.
    6. Check the Time out after n minutes or hours, if you want to configure a timeout after which the execution of the statement is aborted if the timeout is exceeded. If this is an alternative version of a statement that you have saved, a reasonable timeout is the duration of the original version because you are unlikely to be interested in slower alternatives.
    7. Check Run in background, if you want to run the statement in the background.
  3. On the Bind variables tab in the Run Statement dialog box, choose the bind variables with which to run the bind set as follows:
    1. Click Choose bind set.
    2. In the Bind Sets dialog box, choose the bind set you want to use to run the statement. see “About bind sets” on page 156 About bind sets.
    3. Click OK.
  4. On the Advanced tab in the Run Statement dialog box, specify the session parameters to be used when running the statement:
    1. Type in a value for each session variable that you want to run the statement with.
    2. Click Add New Row if you want to add a new session parameter. Choose a session parameter name from the list, or enter a new one. Type in the value you want to run the statement with. See “About session parameters” on page 156 About session parameters.
    3. If you want to delete a session variable from the statement run, select the session variable you want to delete and click Delete.
    4. Click OK.
    5. Check the Extended run information (STATIC_LEVEL=ALL) option if you want additional statistics to be collected. These statistics are displayed in the Extended Statistics tab in the Run Alternatives view.
    6. Check the Array fetch option and specify the number of rows you want to be fetched, if you want to use an array fetch operation.
  5. Click Fill Binds.
  6. Click OK.

Anchor
Aboutbindsets
Aboutbindsets
About bind sets

Bind variables are placeholders in a statement. When you use bind variables, you increase the probability that statements will be stored in memory, making them more immediately available to the next operation that needs them. Bind variables obtain their values from the last statement that was run. You can change the values of bind sets and analyze the values used when a statement is run, to help improve your tuning process.

Anchor
Aboutsessionparameters
Aboutsessionparameters
About session parameters

Session variables obtain their values from the last statement run. You can define a new session variable to run the statement with, in the Add Session Parameters dialog box.

...

You can set the parsing user from Settings>General Settings>SQL Settings > General Settings > SQL tab. You can re-explain a statement in the following views:

  • Plan
  • All Plans

See “About About configuring your settings” on page 26 and “Editing settings and Editing the properties of a statement” on page 31statement.

To re-explain a statement, in the Plan or All Plans view, click Actions>ReActions > Re-Explain.

Anchor
Generatingnewalternatives
Generatingnewalternatives
Generating new alternatives

The Generate Alternatives option instructs Precise for Oracle to run the alternative SQL generator to see if it can find ways of re-writing the SQL so that the Oracle Optimizer comes up with a more efficient execution plan. It performs various mathematical transformations to the SQL, such as replacing EXISTS with IN, OR with UNION, but still so as to give an equivalent result set. The alternatives are saved in the same folder as the original and can be viewed in the Related SQL view.

You can control the settings for generating alternatives from Settings>General Settings>SQL Settings > General Settings > SQL tab, such as only generating alternatives that have a different execution plan or indexable columns.

You can generate new statement alternatives in the Run Alternatives view. see “About See About configuring your settings” on page 26settings.

To generate a new alternative, in the Run Alternatives view, click Actions>Generate Actions > Generate Alternative.

Anchor
HowtheSQLtabcanhelpyouidentifyperformanceproblems
HowtheSQLtabcanhelpyouidentifyperformanceproblems
How the SQL tab can help you identify performance problems

...

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.

 

Precise. Performance intelligence from click to storage. Learn more > >

...


Scroll Ignore
scroll-pdftrue
scroll-officetrue
scroll-chmtrue
scroll-docbooktrue
scroll-eclipsehelptrue
scroll-epubtrue
scroll-htmltrue
Newtabfooter
aliasIDERA
urlhttp://www.idera.com
 | 
Newtabfooter
aliasProducts
urlhttps://www.idera.com/productssolutions/sqlserver
 
Newtabfooter
aliasPurchase
urlhttps://www.idera.com/buynow/onlinestore
 | 
Newtabfooter
aliasSupport
urlhttps://idera.secure.force.com/precise/
 | 
Newtabfooter
aliasCommunity
urlhttp://community.idera.com
 
|
 
Newtabfooter
aliasResources
urlhttp://www.idera.com/resourcecentral
 | 
Newtabfooter
aliasAbout Us
urlhttp://www.idera.com/about/aboutus
 
Newtabfooter
aliasLegal
urlhttps://www.idera.com/legal/termsofuse

...