Page History
...
Table 8-18 All Plans table
Column description
Launches to the SQL tab with the selected plan in context.
...
The following information is displayed for each bind set in the Bind Variables table in the left pane:
Table 8-19 Bind 19 Bind set
Bind Set Description
...
Duplicated Sets The number of times this specific bind set was collected.
Viewing information displayed for a particular bind set
...
(right pane) of the Bind Variables view. You can view the following text for a bind set:
Table 8-20 Bind 20 Bind set
Bind Set Description
...
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 replaced tab.
Note: You can select and copy this text to another tool for further analysis
To obtain an estimate of the bind set's text 1
- 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 21 Findings table
Column Description
...
■ Other findings: yellow
Note: 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 Launches to a tab in context with the chosen object.
...
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 Expanded view is divided into two areas:
■ Highlights
...
- 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.
To investigate a finding
...
- 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
- 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.
Note: 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
...
The following table describes the information displayed on the Details tab.
Table 8-22 Details 22 Details tab information
Column Description
...
Sorted Rows Number of sorted rows.
Table 8-22 Details tab information
Column Description
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.
...
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 23 Tuning actions
Action Description
...
Edit Properties Edit the properties on an existing statement.
see See “Editing the properties of a statement” on page 31.
...
Get Best Plan Get the best plan according to collected bind sets.
Opening an existing statement
The Open option lets you open an existing statement in the PMDB that was previously created by using the New button, collected by the Precise for Oracle Collector, or imported from the Activity tab. The SQL tab is populated with
the explain plan of your chosen statement. Alternatively, you can open a statement by selecting it in another tab and clicking the SQL tab button or the Tune icon.
You can open existing statements in the following views:
- Plan
■ Plan
■ Recommend
...
- Recommend
- Run Alternatives
...
- History
■ History
■ Compare
■ All Plans
■ Bind Variables
- Compare
- All Plans
- Bind Variables
Note: 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 See “Editing the properties of a statement” on page 31.
To open an existing statement1 Click
- Click Actions>Open.
...
- 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.
...
...
- Click OK.
...
...
Creating a new statement
The Create option lets you create a new statement and save it in the PMDB in a logical cabinet and folder hierarchy. However, you cannot create a new statement with the same combination of cabinet, folder, and name as an existing statement. You cannot create a statement with exactly the same text as an existing statement.
You can create a new statement in the following views:
...
- Plan
...
- Recommend
...
...
- Run Alternatives
...
- History
...
...
- Compare
...
...
- All Plans
...
...
- Bind Variables
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 See “Editing the properties of a statement” on page 31.
To create a new statement1 Click
- Click Actions>New.
...
- 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.
...
- In the Text field, enter the text for the statement.
- In the Comment field, type a tuning comment that is associated with the statement, if required.
...
- Click OK.
...
...
- The new statement is saved in the PMDB in the cabinet and folder hierarchy.
...
...
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.
Note: You cannot save a statement with the same combination of cabinet, folder, and name as an existing statement.
You can edit existing statements in the following views:
■ Plan
■ Run Alternatives
■ All Plans
- Plan
- Run Alternatives
- All Plans
See see “Editing the properties of a statement” on page 31.
To edit an existing statement1 Click
- Click Actions>Edit Text. The Edit Statement dialog box opens.
...
- 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” on page 151.
...
- In the Text box, edit the SQL text for the statement.
...
- Click Save.
...
- The edited statement is saved in the same cabinet and folder as the original.
...
...
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 the properties of a statement” on page 31.
To run a statement1 Click
- Click Actions>Run.
...
...
- On the General tab in the Run Statement dialog box, do the following:
...
- 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.
...
- In
...
- the Description box, type a short description of the run.
...
- Enter the Oracle user name and password with which to log in to Oracle.
...
- Choose one of the following options: Fetch all rows or Fetch first n rows
...
- 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.
...
- 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.
...
- Check Run in background, if you want to run the statement in the background.
...
- On the Bind variables tab in the Run Statement dialog box, choose the bind variables with which to run the bind set as follows:
...
- Click Choose bind set.
...
- 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.
...
- Click OK.
...
- On the Advanced tab in the Run Statement dialog box, specify the session parameters to be used when running the statement:
...
- Type in a value for each session variable that you want to run the statement with.
...
- 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.
...
- If you want to delete a session variable from the statement run, select the session variable you want to delete and click Delete.
...
- Click OK.
...
- 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.
...
- 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.
...
- Click Fill Binds.
...
- Click OK.
...
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.
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.
Re-explaining a statement
The Re-Explain option instructs Precise for Oracle to re-explain a statement. Precise for Oracle generates a new execution plan if one or more of the following conditions are met:
...
- The execution plan has changed—one or more of its steps or their order has changed.
...
- The cost has changed.
...
- One of the underlying objects has changed.
If the access plan is unchanged, the last explain time is updated.
You can set the parsing user from Settings>General Settings>SQL tab. You can re-explain a statement in the following views:
■ Plan
...
- Plan
- All Plans
see See “About configuring your settings” on page 26 .see and “Editing the properties of a statement” on page 31.
To re-explain a statement1 In , in the Plan or All Plans view, click Actions>Re-Explain.
Generating new alternatives
...
You can identify a performance problem by doing one or more of the following:
...
- Identifying problematic steps in the access plan
...
- Getting advice on a selected statement
...
- Creating a related SQL
...
- Comparing a related SQL
...
...
Identifying problematic steps in the access plan
...
To identify problematic steps in the access plan
...
- In the Time Frame list, choose the period of time you want to analyze.
...
- Do one of the following:
...
- Launch to the SQL tab with a statement in context, for tuning.
...
- In the SQL tab, open the statement you want to analyze.
...
- The SQL tab shows SQL text on the bottom left, and the execution plan on the top left of the SQL tab. For an example, we see that the SQL is accessing the ORDERS table using a full table scan. The top table on the right will show that the ORDERS table has 30000 rows in it. This is the problematic step.
Getting advice on a selected statement
In our example, we can identify in the statement text three conditions in the WHERE clause of the statement that restrict the amount of data required from the ORDERS table. However, the Oracle Optimizer has determined that every record in the ORDERS table must be read. There are several reasons for this occurrence. We can investigate this problem further by clicking the Recommend button.
Creating a related SQL
The Related SQL view lists the original statement along with any related statements that have been created. Initially there will only be the original statement.
For our example, the SQL tab shows the Related SQL view with just the original statement. We want to tune this statement because no alternate statements have been created yet either manually or automatically by Precise for Oracle.
To create a related SQL
...
- In the Time Frame list, choose the period of time you want to analyze.
...
- Do one of the following:
...
- Launch to the SQL tab with a statement in context, for tuning.
...
- In the SQL tab, open the statement you want to analyze.
...
- Click Run Alternatives. It instructs Precise for Oracle to automatically suggest alternate ways of re-writing the
...
- SQL.
...
- For our example, two alternatives have been suggested for this statement. The first alternative has a lower cost than the original.
...
- Click the Text tab to see a second alternative.
...
- The second alternative has no cost because it is using a /*+ RULE */ hint to force the use of Oracle's rule-based Optimizer, rather than the cost-based Optimizer.
...
...
Comparing a related SQL
You can carry your analysis of the change further by selecting the Compare view. The Compare view compares two access plans for the same statements.
To compare a related SQL
...
- In the Time Frame list, choose the period of time you want to analyze.
...
- Do one of the following:
...
- Launch to the SQL tab with a statement in context, for tuning.
...
- In the SQL tab, open the statement you want to analyze.
...
- Click the Compare icon. The alternative statement opens both the original and the alternative SQL statement in the Compare view.
For our example, we can see that the alternate SQL is now able to use an index to access the CUSTOMER table, whereas before it was doing a Full Table Scan. The re-written SQL will return exactly the same records as the original, but Oracle can use a different access path to the data.
...
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 > >
...