The Analysis tab provides detailed information about statements and cases selected from the Overview tab, after a tuning job has been executed. It also shows filter ratio, and table and join sizes.
The Analysis tab contains information about the statement or case, its full SQL code, a diagram of the SQL statement, and Index Analysis.
Additionally, for the Oracle and SQL Server platforms there are Table Statistics, Column Statistics and Histograms, and Outlines/Plan Guides tabs. For more information, see Using platform-specific features.
Statement analysis is performed when you click Perform detail analysis on the Overview tab and then click Run Job or when you click the Analysis tab. In order to view and analyze statement statistics, select the tab (Index Analysis, Table Statistics, Column Statistics and Histograms, or Outline) and the statements whose statistics you want to analyze.
Next to the Select statement of interest list at the top, you choose to see an analysis of the >ROOT statement, or you can click the list and see an analysis of any one of the generated cases produced by running the tuning job from the Overview tab.
For more information, see Visual SQL tuning.
Implementing index analysis recommendations
Once you have added tuning candidates to a tuning job, DB Optimizer can analyze the effectiveness of the indexes in the database and recommend the creation of new indexes where the new indexes can increase performance.
In the Collect and create indexes table, any indexes DB Optimizer recommends you create are marked in orange.
To accept the suggestion and have tuning automatically generate an index
- For any recommended index, click the checkbox to the left of the index.
Optionally, modify the Index type by clicking in the Index Type column and then selecting a type from the list. - Click the Create Indexes button.
The Index Analysis dialog appears.
To view the index SQL in an editor for later implementation, click the statement and then click Open in a SQL editor.
To run the index SQL and create the index on the selected database, click Execute.