This section includes the following topics:
- About the What-If tab
- How the What-If tab is structured
- Evaluating index changes
- How the What-If tab can help you evaluate changes
About the What-If tab
The What-If tab allows you to simulate the effect of creating an index on the performance of all statements that Precise for DB2 has collected, without physically making the change.
The What-If tab is intended to be used in conjunction with the SQL tab. The normal sequence is to examine any index recommendations made on an individual SQL statement on the Recommendation tab in the SQL tab and click the Quick Link to launch the What-If tab, with the index recommendation as the suggested change. The What-If tab may also be used as a standalone tab where you can simulate the effect of your own index changes.
What-If scans statements in the PMDB. Precise for DB2 can accurately calculate the revised cost of a statement by generating a new explain plan. Statements that have a measurable change in cost are marked as definitely affected. However, even if the revised cost is the same as before, there may still be an impact on response time. For example, Precise for DB2 does not change the cost of a DML statement to cater for the overhead of maintaining extra indexes. Such statements are marked as possibly affected because the effect is immeasurable.
As may be expected, adding an index may improve the response times of select statements that can use the new index but decrease the response time of insert, update, and delete statements that have to maintain the extra index. However, the effect will be immeasurable because usually the cost of these affected statements will not be changed. To help you make an informed choice about the desirability of the change, the What-If tab also displays the previous In DB2 resource consumption and execution figures for the statement.
Having selected an impacted statement, you can launch the SQL tab in context, to compare the statement’s execution plan before and after the simulation.
You should bear in mind the following things when using the What-If tab:
- 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 being loaded into the PMDB and explained by the “Explain Statements” PMDB process.
- Until all functions within an application are exercised, Precise for DB2 cannot give a true picture of all affected statements.
- The index recommendation feature in the SQL tab only examines a single statement. 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. It is always a good idea to use both tabs together.
See Getting current application information, Examining performance over time, and Analyzing statement plans.
How the What-If tab is structured
The What-If tab is slightly different in appearance from other tabs. It is split into the following areas: Main area, Summary area, and Association area.
About the Main area in the What-If tab
The main area lists index changes that you would like to make or that were recommended in the SQL tab. The following table describes the different index simulation parameters
Table 1 Index simulation parameters
| Parameter | Description |
|---|---|
| Change type (Simulation) | Indicates the type of the simulation, which can be one of the following: Index Creation, Index Modifications, or Index Deletion. |
| Owner | DB2 owner of the index. |
| Table | Name of the table that the index belongs to. |
| Index | Name of the index. |
| Unique | Indicates whether the index is unique or not. |
| Allow Reverse Scan | Indicates the value of the reverse scan option (Yes/No). |
| Columns | List of columns in the index. |
| Include Columns | List of include columns in the index. |
| Cluster Ratio | The estimated cluster ratio of the index. |
About the Summary area in the What-If tab
The following table describes the information that is shown in the Summary area after a simulation has been completed.
Table 2 Simulation summary
| Category | Item | Description |
|---|---|---|
| Improved statements | Statements whose cost has improved. | The number of statements that are positively impacted. |
| Deteriorated statements | Statements whose cost has deteriorated. | The number statements that are negatively impacted. |
| Unaffected statements | Statements whose cost has not been affected. | The number of statements that are not affected by the change. |
| Affected statements by object type | Statements that were changed categorized by object. | The number of statements that were affected for each object category (a row for each index change). |
About the Association area in the What-If tab
The Association controls in the Association area allow you to choose statements that will definitely be affected and statements that will possibly by affected. You can use the More... option to filter the results.
For statements that will definitely be affected, Precise for DB2 was able to accurately calculate the impact, for example when a change in the explain plan occurred and the cost figures are available. For statements that will possibly be affected, Precise for DB2 did not have enough information to make an accurate assessment, for example because cost information is missing, the rule-based Optimizer is in use, the explain plan is the same but extra indexes must be maintained, or simply because an older version of DB2 is in use.
Selecting either of these options displays a list of impacted statements. They both contain the following tab.
Effect tab
The effect tab displays the following table columns
Table 3 Effect tab
| Column | Description |
|---|---|
 | The Tune icon launches the SQL Tab on the Plan tab. |
| Statement | Name of the impacted statement. |
| In DB2 | Resource consumption of the impacted statement during the selected time frame. The format is: hh:mm:ss.s. Use this column to assess the heavy resource consuming statements that are either positively or negatively impacted. |
| Cost | Current estimated cost of the DB2 Optimizer, as taken from the latest explain plan. |
| Predicted Cost | Predicted cost of the DB2 Optimizer for the new plan. |
| Change Ratio | The ratio between the predicted cost and the current cost, expressed as a percentage. Therefore, 100% represents no change; a value greater than 100% is a degradation, and a value less than 100% is an improvement. If cost information is not available, the cost, predicted cost, and change ratio are all N/A. |
| Executions | Number of times the statement was executed during the selected time frame. |
| Comment | Precise for DB2 may include its own comment on the simulated change, or why, for example, costs are not available. |
Evaluating index changes
The buttons below the Main area let you add and edit index changes for evaluation, simulate an index change, or delete a suggested change from the evaluation table.
Adding or editing an index change for evaluation
If you want to test the consequences of an index change that is not yet listed in the evaluation table in the Main area, you can add a new entry. If you want to modify an index change that has already been suggested, you can edit it.
To add or edit an index change
- Select the Time Frame and Database Partition to work on.
- Click Add or Edit, as required.
- Select an owner.
- Select a table.
- If indexes already exist for this table, select the name of the relevant index, if not, enter an index name.
- If you want to allow a reverse scan, select the relevant check box.
- If you want the index to be a unique index, select the relevant check box.
- In the Cluster Ratio field, enter a number between 0 and 100. This ratio indicates the degree to which the table data is clustered in relation to this index. The higher the number, the better rows are ordered in index key sequence. If table rows are in close to index-key sequence, rows can be read from a data page while the page is in the buffer.
- To add/remove columns to or from the Index Columns table, use the left and right arrows. If the Unique check box is selected, you may also add/remove columns to or from the Include Columns table.
- Click OK to update the table in the Main area.
Simulating an index change
After you add or edit an index change, you should simulate the change to evaluate its effects. To simulate an index change, in the What-If... dialog box, click Evaluate. The simulation takes into account all the changes listed in the Main area. When the simulation is complete, the Summary and Association areas are populated with data.
Deleting a suggested index change
If you decide that a specific index change should not be evaluated, you can remove it from the evaluation table.
Removing an entry from the table only means that the impact of this index change will not be evaluated. It is not the same as dropping an index.
To delete an index change, in the What-If... dialog box, select a table row and click Remove.
How the What-If tab can help you evaluate changes
Adding an index
Simulating an index change is a two-step process. First you need a list of proposed index changes. Typically this is done automatically for you by following the link on the Recommendation tab in the SQL tab. Alternatively, you can manually propose the addition, modification, or deletion of indexes.
Second, you need to run the impact analysis against all the SQL statements in this database partition. To do this, click Evaluate.
The Summary area will show how many statements will be improved by the creation of the recommended index and how many will have their performance deteriorate. In the Association area on the right, you can view which statements will be affected.
The DB2 Optimizer is used to compare the cost of the original execution plan with the cost of the new execution plan. To simulate removing an index, you still need to add a proposed index change to the table in the Main area. This is either done for you on the Advice tab in the SQL tab, or you can click Add to open the What if... dialog box.