Page History
...
- If you are using the cost-based Optimizer, it is important that statistics are up to date so that the simulation is as accurate as possible.
- What-If is only as good as the data it has to work on. It is dependent on statements, J2EE Caller Services, and Web Transactions being loaded into the PMDB and explained by the Explain Statements PMDB process.
- Until all functions within an application are exercised, Precise for Oracle cannot give a true picture of all affected statements, J2EE Caller Services, and Web Transactions.
- The index recommendation feature in the SQL tab only examines a single statement, J2EE Caller Service, and Web Transaction. It may be the case that the What-If tab suggests that this recommendation should not be implemented because of its impact on other statements, J2EE Caller Services, and Web Transactions. It is always a good idea to use both tabs together.
See “About About the Current tab” on page 51, “About tab, About the Activity tab” on page 61tab, and “About About the SQL tab” on page 131tab.
| Anchor | ||||
|---|---|---|---|---|
|
...
- Statements, J2EE Caller Services, and Web Transactions whose performance improve. The number of statements that are positively impacted.
| Anchor | ||||
|---|---|---|---|---|
|
Depending upon whether or not a statement was evaluated, you can launch to either the Plan tab or the Compare tab in the SQL tab. The following icons indicate to which tab in the SQL tab a statement will be launched, in context:
...
To add or edit an index change
- Click Add or Edit, as required.
- In the What-If... dialog box, select an owner and a table.
- Select an operation: Index Creation, Index Modifications, or Index Deletion.
- If indexes already exist for this table, select the name of the relevant index.
- If you want the index to be a bitmap index or a unique index, select the relevant check box.
- To add or remove columns to or from the index, use the left and right arrows.
- Click OK to update the table in the Main area.
...
- To simulate an index change, select the Evaluate check box of check Evaluate in the relevant table rows, and then click Evaluate.
- If the number of statements to evaluate exceeds the default setting (100 statements), you will be prompted to do one of the following:
- Click OK to start the analysis.
- Click Top Statements to evaluate the top 100 statements experiencing In Oracle activity only.
- Click Cancel to cancel the operation.
The simulation takes into account all the changes listed in the Main area. When the simulation is complete, the Summary and Grid areas are populated with data.
...
| Column | Description |
|---|---|
| J2EE Caller Service or Web Transaction | Names of either the J2EE Caller Services or Web Transactions. |
| In Oracle | Resource consumption of the impacted J2EE Caller Service or Web Transaction during the selected time frame. The format is: hh:mm:ss. Use this column to assess the heavy resource consuming statements that are either positively or negatively impacted. |
| Gain (%) | The amount in gain in percentage when performing the suggested index change. |
| Statement Executions | The amount of statement executions. |
| Affected Transaction | The number of transactions for the specific J2EE Caller Service that are affected by the index change (for J2EE Caller Services only). |
Table 4 Columns for the Statement table
| Column | Description |
|---|---|
 | Lets you launch to the SQL tab on the plan tab, if the statement was not evaluated, or the SQL tab on Compare tab, to compare the explain plan for this version of the statement with the original. See “About context” on page 161 |
| Text | Portion of the SQL text. |
| In Oracle | Resource consumption of the impacted statement during the selected time frame. The format is: hh:mm:ss. Use this column to assess the heavy resource consuming statements that are either positively or negatively impacted. |
| Cost | Current estimated cost of the Oracle Optimizer, as taken from the latest explain plan. |
| Predicted Cost | Predicted cost of the Oracle Optimizer for the new plan. |
| Gain (%) | The amount in gain in percentage when performing the suggested index change. |
| Executions | Number of times the statement was executed during the selected time frame. |
| Statement | Name of the impacted statement. |
| Statement ID | The ID of the statement. |
| statement-username | The user name which executed the statement. |
| statement-explain-type | Display the explain type used for the recommendations. |
| Comment | Precise for Oracle may include its own comment on the simulated change, or why, for example, costs are not available. |
| Affected Transaction | The number of transactions for the specific statement that are affected by the index change. |
...
You can identify a performance problem by doing one or more of the following:
| Anchor | ||||
|---|---|---|---|---|
|
Simulating an index change is a two-step process.
...
In the Association area on the right, you can see the two statements that will be affected. The Oracle Optimizer is used to compare the cost of the original execution plan with the cost of the new execution plan. In our example, the cost will be reduced and the Change Ratio is less than 100 for both cases. The execution time of these statements and the resources they consume should therefore be reduced.
| Anchor | ||||
|---|---|---|---|---|
|
To simulate removing an index, you first need to add a proposed index change to the table in the Main area.
...