Test Run Index Alternatives
Once SQL Optimizer generates index alternatives, you can test each alternative. When you test run alternatives, indexes are physically created on the database and dropped after SQL Optimizer executes the statement. Use of this function requires you to select a tablespace for the indexes.
Important: Since indexes are physically created on the database, this process may impact database performance.
To test run an index alternative
- Select the Optimize SQL tab in the main window.
- Select a SQL Rewrite session.
-
Select an index alternative in the Alternatives pane.
-
Click
.
Tip: Click the drop-down arrow beside
and select Test Run - All to test run all index alternatives.
-
Review the following for additional information:
Index Information Description Index Shows SQL text of index selected. Tablespace Click
and select a tablespace for index creation.
Note: Review the information in the DDL Script pane.
Tip: Click 
Analyze Index Impact (SQL Rewrite)
You can use Analyze Impact to simulate creation of the index alternatives Optimize SQL generates. The simulation process uses virtual indexes to determine the impact of creating the new indexes on your database environment.
Note: This topic focuses on information that may be unfamiliar to you. It does not include all step and field descriptions.
To analyze index impact
- Select the Optimize SQL tab in the main window.
- Select a SQL Rewrite session.
-
Select an index alternative in the Alternatives pane.
-
Click
. A new Analyze Impact session opens.
- The Indexing Change Details page is populated with the new indexes that you sent from the Optimize SQL session. Use this page to modify the index, if necessary. See Analyze the Impact of Additional Indexes for more information.
- When you have finished modifying the indexes, click
to start collecting the SQL to evaluate. See Collect SQL and Run an Impact Analysis.
View SQL Information (SQL Rewrite)
This topic focuses on information that may be unfamiliar to you. It does not include all step and field descriptions.To view SQL information
- Select the Optimize SQL tab in the main window.
-
Select a SQL Rewrite session.
-
Select the SQL Information tab in the Optimize SQL window.
-
Click the following to view the information that displays for each pane.
SQL Information Description Classification
Statement type as classified by SQL Optimizer. Types include Simple, Complex, or Problematic. Classification Rules Detail Description Details
Applicable SQL Classification Rules. Rules used for the classification process are set on the SQL Classification Options page. See SQL Classification for more information. Note: SQL Classification information only displays for the original SQL statement.
Optimization Details Description Intelligence Level
Intelligence level used for optimization. You can specify the Intelligence level used for this session by clicking the Optimizer Intelligence Level button. To set the default level, use the Options dialog. See Intelligence (Optimize SQL) for more information. Semantically equivalent SQL statements
Number of semantically equivalent SQL statements generated by SQL Optimizer. Alternative execution plans produced
Number of alternative execution plans produced for the original SQL statement. SQL statements eliminated due to identical execution plans
Number of statement alternatives eliminated with the same execution plan as the original SQL statement.
Note: SQL statements with the same execution plan produce the same run time because the database executes these statements in the same way. Therefore, SQL Optimizer eliminates alternatives with equivalent execution plans.
Optimization Time Details Description Optimization started at
Time optimization started. Optimization finished at
Time optimization finished. Total optimization time
Total time to complete optimization. Average optimization time
Average time to generate each statement alternative. Notes:
- This information is only available after you generate SQL alternatives.
- Optimization details only display for the original SQL statement.
Index Details Description Intelligence level
Intelligence level used for index generation. This option is set in the Index Generation Intelligence options menu. See Intelligence (Index Generation) for more information. Indexes have been generated
Number of indexes generated. Index sets were composed to generate different execution plans
Number of index sets produced to generate alternative execution plans. Index sets eliminated due to identical execution plans
Number of index sets eliminated because Database Engine Tuning Advisor information indicated they are not used by the original SQL statement. Indexes have been used in index sets
Number of indexes used in the selected index set. Index Time Details Description Index started at
Time index generation started. Index finished at
Time index generation finished. Total index time
Total time to complete index generation. Average index time
Average time to generate each index set. Note: This information is only available after you generate index alternatives.
Alert Details Alert messages about one or more of the following:
- Errors SQL Optimizer encountered during the optimization process.
- SQL classification details.
Note: This pane is only available if SQL Optimizer generates alert messages.
Classification
View Statistics Information (SQL Rewrite)
View Schema Information (SQL Rewrite)
This topic focuses on information that may be unfamiliar to you. It does not include all step and field descriptions.To view schema information
- Select the Optimize SQL tab in the main window.
- Select a SQL Rewrite session.
-
Select the Schema Information tab in the Optimize SQL window.
Tip: Click
to retrieve all schema objects.
-
Click
Tips:
-
Click
to reorganize indexes.
-
Click
to analyze the selected table.
-