Page History

...

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.

...

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 the Dashboard tab, About the Current tab, About the Activity tab, and About the Objects tab.

...

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

...

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.

...

Column	Description
	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 #number.
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.

...

...

...

...

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

...

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

...

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.

...

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.

...

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.

...

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 a statement's different versions.
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.

...

You can run a statement in the Run Alternatives view. See Editing the properties of a statement.

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

...

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:

...

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

Anchor
Generatingnewalternatives Generatingnewalternatives

Generating new alternatives

...

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.

...

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

...