|Oracle Enterprise Manager Database Tuning with the Oracle Tuning Pack
Part Number A86647-01
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:
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.
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:
Any of these factors may impact the indexing decisions for the database.
You can access the Index Tuning wizard in the following ways:
If you have the Oracle Expert application installed, you can launch the Index Tuning wizard from the Oracle Expert Tools menu.
The Index Tuning wizard makes two database connections:
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:
Each of these screens is described in the following sections.
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.
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.
From this screen you can:
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.
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.
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.
This screen allows you to implement and save the index tuning recommendations. The possibilities are:
When you click Finish, the recommendations will be saved and/or implemented according to the choices made.