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 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 to stop (or interrupt) the test run process. See Stop and Resume the Optimization Process for more information.
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 an index alternative in the Alternatives pane.
Click . A new Analyze Impact session opens.
To view SQL information
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:
|
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:
|
Note: This pane is only available if SQL Optimizer generates alert messages. |
View Statistics Information (SQL Rewrite)
To view schema information
Select the Schema Information tab in the Optimize SQL window.
Tip: Click to retrieve all schema objects.
Click beside the Summary list and select a table for additional information.
Tips:
Click to reorganize indexes.
Click to analyze the selected table.
© 2024 Quest Software Inc. ALL RIGHTS RESERVED. Terms of Use Privacy Cookie Preference Center