You are viewing an old version of this page. View the current version.

Compare with Current View Page History

« Previous Version 7 Next »

This section includes the following topics:

About the What-If tab

The What-If tab lets you simulate the effect of creating, modifying, or dropping a B*Tree or bitmap index on the performance of all statements that Precise for Oracle has collected in the selected context, without physically making the change.

You can either manually enter the list of recommendations or populate it when activating the Oracle advisor/Precise advisor as specified in the Recommend button section.

What-If scans statements, J2EE Caller Services, and Web Transactions in the PMDB. If the cost-based Optimizer is being used, Precise for Oracle can accurately calculate the revised cost of a statements, J2EE Caller Services, and Web Transactions by generating a new explain plan. Statements, J2EE Caller Services, and Web Transactions 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, Oracle does not change the cost of a DML statements, J2EE Caller Services, and Web Transactions to cater for the overhead of maintaining extra indexes. Such statements, J2EE Caller Services, and Web Transactions are marked as possibly affected because the effect is immeasurable. If the rule-based Optimizer is being used, Precise for Oracle assesses the likelihood of a change in execution plan and marks statements, J2EE Caller Services, and Web Transactions it finds as possibly affected.

As may be expected, adding an index may improve the response times of selected statements, J2EE Caller Services, and Web Transactions that can use the new index but decrease the response time of insert, update, and delete statements, J2EE Caller Services, and Web Transactions that have to maintain the extra index. The opposite effect is seen if an index is dropped. However, the effect will be immeasurable because usually the cost of these affected statements, J2EE Caller Services, and Web Transactions 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 Oracle resource consumption and execution figures for the statements, J2EE Caller Services, and Web Transactions.

Having selected impacted statements, J2EE Caller Services, and Web Transactions, you can launch the SQL tab in context, to compare the statements, J2EE Caller Services, and Web Transactions’ execution plan before and after the simulation. The statements, J2EE Caller Services, and Web Transactions and their simulated indexes will be saved as alternative, in the same folder.

You should bear in mind the following things when using the What-If tab:

  • 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 the Current tab” on page 51, “About the Activity tab” on page 61, and “About the SQL tab” on page 131.

How the What-If tab is structured

The What-If tab is slightly different in appearance from other tabs. It is divided into the following areas: Main area, Summary area, and Grid area.

About the Main area

The main area in the What-If tab lists index changes that you would like to make or that were recommended by the Oracle or Precise Advisor.

The following table describes the different index change parameters.

Table 1 Index change parameters

ParameterDescription
EvaluateCheck box to select the table row for manipulation.
RemoveButton to remove its table row from the Index list.
EditButton to edit the table row.
ActionIndicates the type of the change, which can be one of the following: Index Creation, Index Modifications, Index Deletion, Gather Statistics, Accept SQL Profile, or Rewrite Query.
OwnerOracle owner.
TableName of the table that the index belongs to.
ObjectRecommended Object name.
DDL/CommentDDL command for recommendation implementation or comment regarding the recommendation.
ColumnsList of columns in the index.
TypeNormal (B*Tree) or Bitmap.
UniqueIndicates whether the index is unique or not.
Leaf BlocksAnalysis of statistics information. This column is blank until you click Evaluate. If an index is deleted, this column will show N/A.
Distinct KeysAnalysis of statistics information. This column is blank until you click Evaluate. If an index is deleted, this column will show N/A.
Clustering FactorAnalysis of statistics information. This column is blank until you click Evaluate. If an index is deleted, this column will show N/A.
PartitionedIndicates whether the index is partitioned or not.

About the Summary area

The Summary area in the What-If tab displays whether or not a statement, J2EE Caller Service, or Web Transaction would be effected by the change after clicking Evaluate, as follows:

  • Statements, J2EE Caller Services, and Web Transactions whose performance improve. The number of statements that are positively impacted.

About launching to SQL tab in context

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:

Table 2 Icons to launch in context

IconDescription
If the statement was evaluated, launches the SQL tab on the Plan tab.
Launches the SQL tab on the Compare tab so that you can compare the explain plan for this version with the original.

Evaluating index changes

The buttons below the Main area let you add and simulate an index change. The icons on the table rove let you edit index changes for evaluation 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

  1. Click Add or Edit, as required.
  2. In the What-If... dialog box, select an owner and a table.
  3. Select an operation: Index Creation, Index Modifications, or Index Deletion.
  4. If indexes already exist for this table, select the name of the relevant index.
  5. If you want the index to be a bitmap index or a unique index, select the relevant check box.
  6. To add or remove columns to or from the index, use the left and right arrows.
  7. 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.

For a partitioned table, Precise for Oracle can only simulate global B*Tree indexes.

To simulate an index change

  1. To simulate an index change, select the Evaluate check box of the relevant table rows and click Evaluate.
  2. 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.

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 drop an index, use the What if... dialog box.

To delete an index change, click the Remove button of the relevant table row.

About the Grid area

After evaluating one or more indices, the Summary and the Grid area will be shown. As mentioned before, the Summary will show the performance improvement of the statements, J2EE Caller Services, and Web Transactions.

The Summary and Grid area will only show statement information if J2EE is not installed.

The number of statements that will be evaluated is limited to Top n. A message with information about this will be displayed.

If J2EE is installed, the Grid area will show two tables:

  • The left table contains a list of J2EE Caller Services and Web Transactions. If a J2EE Interpoint is installed, the left table also contains statements.
  • The right table contains a list of statements that are related to the selected J2EE Caller Service or Web Transaction in the left table.

When a statement is chosen in the left table, the right table is removed.

The following tables describe the columns in the tables:

Table 3 Columns for J2EE Caller Service and Web Transaction table

ColumnDescription
J2EE Caller Service or Web TransactionNames of either the J2EE Caller Services or Web Transactions.
In OracleResource 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 ExecutionsThe amount of statement executions.
Affected TransactionThe 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

ColumnDescription
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 launching to SQL tab in context” on page 161.
TextPortion of the SQL text.
In OracleResource 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.
CostCurrent estimated cost of the Oracle Optimizer, as taken from the latest explain plan.
Predicted CostPredicted cost of the Oracle Optimizer for the new plan.
Gain (%)The amount in gain in percentage when performing the suggested index change.
ExecutionsNumber of times the statement was executed during the selected time frame.
StatementName of the impacted statement.
Statement IDThe ID of the statement.
statement-usernameThe user name which executed the statement.
statement-explain-typeDisplay the explain type used for the recommendations.
CommentPrecise for Oracle may include its own comment on the simulated change, or why, for example, costs are not available.
Affected TransactionThe number of transactions for the specific statement that are affected by the index change.

How the What-If tab can help you identify performance enhancements

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

  • Adding an index
  • Removing an index

Adding an index

Simulating an index change is a two-step process.

To simulate an index change

  1. Obtain a list of proposed index changes.
    A list of proposed index changes can be retrieved by one of the following options:
    • The list is produced automatically by following the link on the Advice tab in the SQL tab.
    • 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.
  2. Click Evaluate to run the impact analysis against all the SQL statements in this instance.
  3. 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, 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 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.


IDERA |  Products | Purchase | Support |  Community |  Resources |  About Us  | Legal
  • No labels