4
Introduction to Oracle Index Tuning Wizard

The Oracle Index Tuning wizard is a software application that identifies tables with inefficient indexes and makes recommendations which will improve access to those tables.

The Index Tuning wizard:

• Identifies tables in need of index changes
• Presents its findings in reports
• Implements the recommendations for you

The Index Tuning wizard is intended for use with the Oracle cost-based optimizer. The recommendations made by the Index Tuning wizard will optimize index usage for the Oracle cost-based optimizer. Therefore, you should not use the Index Tuning wizard for those schemas where rule-based optimization is used.

This chapter describes when to use the Index Tuning wizard, how to access the Index Tuning wizard, and the Index Tuning wizard interface.

When to Use the Index Tuning Wizard

You can use the Index Tuning wizard to proactively maintain optimal indexes for your database. You should run the Index Tuning wizard regularly to evaluate whether index changes should be made to improve SQL query performance. The Index Tuning wizard may recommend adding new indexes, changing existing indexes, or changing the type of an index.

You should also use the Index Tuning wizard when one of the following situations occurs:

• A user has reported unacceptable response times for a query
• New applications have been added to the database environment
• Existing application SQL has been modified
• The database server has been upgraded to a new version
• Table sizes within the database have increased substantially

Any of these factors may impact the indexing decisions for the database.

Accessing the Index Tuning Wizard

You can access the Index Tuning wizard in the following ways:

• From the Oracle Enterprise Manager Tuning Pack tool drawer
• Through the Oracle Expert application

  If you have the Oracle Expert application installed, you can launch the Index Tuning wizard from the Oracle Expert Tools menu.

  Note: In either case, you must first select a database in the navigator tree before launching the Index Tuning wizard.

The Index Tuning wizard makes two database connections:

1. The Index Tuning wizard connects to its repository to store and analyze data required for index tuning.
2. The Index Tuning wizard uses the database credentials defined in Oracle Enterprise Manager console to connect to the target database for index tuning.

Index Tuning Wizard Interface

When you first access the Index Tuning wizard, you are greeted with a Welcome screen that provides some of the advantages of using the Index Tuning wizard.

You will be lead through the following screens:

• Application Type
• Schema Selection
• Index Recommendations
• Analysis Report and Script
• Finish

Each of these screens is described in the following sections.

Application Type

On this screen, you choose the type of application that is primarily being used for the target database being tuned: Online Transaction Processing (OLTP), data warehousing, or multipurpose.

Schema Selection

On this screen, you have the opportunity to select the schemas you wish to evaluate. The Any Schema option tells the Index Tuning Wizard to select any schema which has a table referenced by one of the worst performing SQL statements. Select this option if you want an overview of the database's overall performance problems.

The purpose of the Selected Schemas option is to hide recommendations for schemas that you are not responsible for tuning or you do not wish to tune at the current time. Select this option if you only want recommendations for schemas you control. The schemas you select may or may not be the worst performers so you may or may not get tuning recommendations for all of the selected schemas.

When you first use the Index Tuning wizard, it is best to let the wizard select any schema so you will see an overall view of the problem areas. You can then focus on specific areas of the database for which you have control.

Index Recommendations

From this screen you can:

• Have the Index Tuning wizard generate the index recommendations, that is, collect and analyze the data to provide the index recommendations.
• Choose the index recommendations to be implemented.

The Index Tuning wizard provides a work-in-progress dialog box.

You can stop the generation at any time. The Index Tuning wizard deletes all the created files in preparation for the next generation.

Once the recommendations are generated, use this screen to choose the index recommendations you want to implement. The Details button provides additional information about each recommendation. To activate the Details button, select a recommendation.

Analysis Report and Script

The Analysis Report and Script page displays the Analysis Report and Script tabs to view all of the recommendations for a tuning session, as well as the rationale for those recommendations.

Note: The Analysis report for a tuning session is available only after the Index Tuning Wizard has analyzed the collected data.

During an analysis, the Index Tuning Wizard sifts through the collected data, uses its rules to generate tuning recommendations, then stores the information for the Analysis report in the repository. The Analysis report information stays in the repository until another analysis is performed for the tuning session.

The Script tab displays the SQL script needed to implement the recommendations that you selected on the Index Recommendations page. You can view the SQL before choosing to implement the recommended changes.

You can print and save the report and script from this page.

Finish

This screen allows you to implement and save the index tuning recommendations. The possibilities are:

• Implement the selected recommendations immediately
• Save the recommendations as an implementation script (Provides you with another opportunity to save the analysis report and script if you have not already done so.)
• Save the recommendations to an Oracle Expert Tuning Session (Standard Management Pack users must have the Oracle Tuning Pack installed for this option to work.)

When you click Finish, the recommendations will be saved and/or implemented according to the choices made.