Page History

...

You can identify a performance problem by doing one or more of the following:■    Adding

• Adding an index

...

• Removing an index

Adding an index

Simulating an index change is a two-step process.

To simulate an index change1    Obtain

1. Obtain a list of proposed index changes.
   A list of proposed index changes can be retrieved by one of the following options:

...

1. • The list is produced automatically by following the link on the Advice tab in the SQL tab.

...

1. • You can manually propose the addition, modification, or deletion of indexes.
     For our example, the What-If tab shows a proposed index addition to the ORDERS table. The change was recommended in the SQL tab, but it could just as easily have been entered manually by clicking Add.

...

1. Click Evaluate to run the impact analysis against all the SQL statements in this instance.

...

1. Click the Explore icon, next to the Statement name. You are taken to the SQL tab in context, and the original and the new execution plans are shown side-by-side on the Compare tab.

For our example, the What-If tab shows in the Summary area that two statements will be improved by the creation of the recommended index and none will have their performance deteriorate.

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.

Removing an index

To simulate removing an index, you first need to add a proposed index change to the table in the Main area.

To simulate removing an index
1    Add , add a proposed index change to the table in the Main area.
A list of proposed index changes can be retrieved by one of the following options:■    The

• The list is produced automatically by following the link on the Advice tab in the SQL tab.

...

• You can click Add to open the What-If dialog box.

For our example, the What-If tab proposes to remove the index IS_PWTR_01 from the table
PS_PWTR_TABLE_RANGE. (This suggestion is not a good idea because it belongs to the PMDB.)

Having evaluated the proposal for our example, you can see in the Summary area that 31 statements will definitely be impacted, all having their performance worsened. No statements have their performance improved. You can also see that only two statements could not be evaluated.

Further information can be gathered from the change ratio, which varies from 113%—only slightly worse—to up to
700%. It is therefore unlikely that we implement the proposed change.

Precise. Performance intelligence from click to storage. Learn more > >

...