SQL Optimizer for SAP ASE 3.8 - User Guide

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

Configuration Analyzer Tutorial

The Configuration Analyzer evaluates the effect on SQL performance when changing Adaptive Server parameter settings. It enables you to analyze whether the database performance may improve before you make configuration parameter changes permanent.

1. Click .

2. Click . If this is your first time in the Configuration Analyzer, the New Analysis wizard automatically opens.

Analyzer Page

3. In the New Analysis wizard, under the Analyzer page, specify if you want to check the effects of configuration changes by either Creating a new Analyzer or Continuing an existing Analyzer.

4. Give the analysis a name and description for easy reference.

5. If you would like to create a folder for better organization of your analysis, click .

Select SQL Page

6. Select the source of the SQL statements for Analysis: SQL Repository or SQL Scanner.

7. Select the SQL statement(s) to add to the Analysis from your predefined SQL statements, or you may add a statement to this Analysis by clicking .

8. Under the SQL Query Plans will be analyzed section, select the options for retrieving your query plan.

• Using existing query plan saved with the SQL
  This option uses the query plan that was saved with the SQL statement at the time that statement was saved to the SQL Repository, or scanned in the SQL Scanner.
• Obtaining a new query plan under the current connection
  This option retrieves the query plan with the current database logon. This current query plan is compared to the query plan that is retrieved after executing the configuration changes.

Configuration Page

9. Select to view the configuration parameters for the various options within Adaptive Server.

10. Make any parameter changes by inserting a value in the New Value column.

11. Click Finish to perform the analysis.

Reviewing Configuration Analyzer Results

12. After the analysis, you can see the overall results by clicking the Analyzer, and its related information, in the tree structure in the left pane and viewing the corresponding information in the panes to the right.

13. In the right pane for the Scenarios, click Prognosis to see overall performance changes and SQL details.

Related Topic

Configuration_Analyzer_Overview