SQL Optimizer for SAP ASE 3.9 - User Guide

Index Advisor Tutorial

The Index Advisor analyzes the syntax of a SQL statement and the database structure and then proposes new index candidates to help improve performance. It provides detailed information on the suggested indexes, such as, space requirements and selectivity. The index recommendations can be benchmarked to identify which index yields the greatest performance gain. It also enables you to create your own indexes for testing.

Create Index candidates

1. Click .

2. In the top pane under the SQL Editor tab, enter the SQL statement for which you want to analyze for index recommendations.

Note: To copy a SQL statement from other windows such as SQL Scanner or SQL Optimizer, click .

3. Click to see the current query plan and get a list of the indexes used in the current query plan. This index information displays in the Used Index tab of the bottom pane.

4. Click .

5. From the Select Tables to Provide Indexes window, select the tables on which you want recommendations for new indexes and specify the sampling size of each table to calculate selectivity. Click OK.

6. Once the advising process is completed, the Index Advising Details window displays detailing the index candidates. Click OK.

7. The index candidates are displayed on the bottom left pane on the tabs labeled Index1, Index2, …IndexN. The Used Index tab displays the DDL for the index(es) currently used by the original SQL statement. The corresponding query plan, abstract plan, SQL classification, trace on and Sort Resource information are displayed in the SQL Information pane at the bottom right.

8. You can add your own index candidates using . This option displays a GUI for you to create indexes for analysis.

Test index candidates

9. To get the actual run time information for the SQL statement under every index scenario, click . Select your benchmarking options in the Batch Run Criteria window. Click OK.

Important Note: This process may impact your database server. Specify the index creation options such as the segment where the index is going to be physically created, and then dropped, and the number of consumers.

10. The Batch Run window opens enabling you to view the results as each statement executes.

11. At the completion of the entire job, the Batch Run Details window replaces the Batch Run window. This window provides greater detail about each SQL statement. Click OK.

12. Click the Time tab in the Index Advisor window to see the results of the Batch Run.

13. To analyze the impact of every index alternative on the query plans of other SQL statements, click .

Related Topic

Index Advisor Overview

Abstract Plan Manager Tutorial

The Abstract Plan Manager provides a window for you to easily view, create, delete and modify your abstract plan groups.

In Adaptive Server version 15 and later, the abstract plan enables you to influence the optimization of a SQL statement without having to modify the SQL statement syntax. If you cannot change the source code that contains your SQL statement, you can use the abstract plan to force Adaptive Server to use a specific query plan for a SQL statement. This is particularly useful if you have third party applications where you do not have access to the source code.

Manage Abstract Plans

1. Click to open the Abstract Plan Manager window.

2. You can navigate within the Abstract Plan Manager to work with abstract plan groups or individual plans within any database, and all functions within this module are available from the right-click menu.

3. To create a new abstract plan group, right-click and select Group | Create.

4. Select under which database you want this group created (or if you want this created in all databases, select the checkbox Create in all databases) and name your abstract plan group.

5. With already created groups, there are many functions you can employ by right-clicking a group including dropping or purging a plan group, performing an import or export of a plan group, or even comparing plan groups.

6. Locating an abstract plan can be done at any level in the tree structure by selecting Find Abstract Plan | Text, or ID.

7. Individually plans can be manipulated by drilling down to a specific Plan ID and specifying whether you would like to drop, copy, or edit an individual abstract plan.

Related Topic

Abstract_Plan_Manager_Overview

SQL Repository Tutorial

The SQL Repository stores the SQL statements that are used in the analysis of database performance. These may be SQL statements that you have identified as critical to the performance of your database application.

Add SQL to the SQL Repository

1. Click to open the SQL Repository window. If no SQL exists in the SQL Repository, then the Add SQL wizard displays automatically. Otherwise, you can open the Add SQL wizard by clicking .

2. In the Add SQL wizard enter the SQL text in the SQL Information page.

The SQL syntax is checked and the query plan retrieved before adding a new node to the SQL tree view with the SQL name. Each SQL statement added to the SQL Repository contains a query plan, SQL classification type (Simple, Complex or Problematic) and the current connection information (login name, server name, database and user). The query plan stored with the SQL statement is important as it indicates the current performance of the SQL.

Related Topic

SQL Repository Overview

Save SQL to the SQL Repository from other modules Tutorial

You can save SQL statements to the SQL Repository from the SQL Inspector, SQL Collector for Monitor Server, SQL Scanner, SQL Optimizer, and SQL Worksheet.

Save SQL to SQL Repository

1. Click .

2. Select the location in which to save the SQL statements and click OK.

Note: If you are using this function from the SQL Scanner or SQL Inspector window you need to select which Job or Inspector to be added first. Only valid SQL statements are saved to the SQL Repository.

Related Topic

SQL_Repository_Overview