Page History
...
- 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 Getting current application information” on page 30, “Examining information, Examining performance over time” on page 39time, and “Analyzing Statement plans” on page 73 Analyzing statement plans.
| Anchor | ||||
|---|---|---|---|---|
|
...
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 8- 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 8- 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 effect tab displays the following table columns
Table 8- 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. |
| Anchor | ||||
|---|---|---|---|---|
|
...
| Anchor | ||||
|---|---|---|---|---|
|
You can evaluate changes by doing the following:
...
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.
...
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.
...
Precise. Performance intelligence from click to storage. Learn more > >
...
| Newtabfooter |
Newtabfooter |
Newtabfooter |
Newtabfooter |
Newtabfooter |
| |||||||||||||||||||||||||||||||
| Newtabfooter | ||||||||||||||||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
|
...