Chat now with support
Chat mit Support

SQL Optimizer for SAP ASE 3.8 - User Guide

Introduction Tutorials Preferences Editor Functions SQL Information and Functions Performance Monitor SQL Inspector SQL Collector for Monitor Server SQL Scanner Index Advisor SQL Optimizer
SQL Optimizer Overview Optimization Engine Common Coding Errors in SQL Statements What Function Should l Use to Retrieve the Run Time? Unsatisfactory Performance Results SQL Optimizer Functions SQL Editor Optimized SQL Activity Log
SQL Worksheet SQL Formatter Database Explorer Code Finder Object Extractor SQL Repository Index Impact Analyzer Index Usage Analyzer Configuration Analyzer Migration Analyzer Abstract Plan Manager User-Defined Temp Tables SQL History Legal Information

New Analysis Wizard: Select SQL Page

Index Impact Analyzer > Create an Index Impact Analyzer > New Analysis Wizard: Select SQL Page

In the New Analysis wizard, the Select SQL page is used to select the SQL statements whose query plans you want to analyze in order to review the impact on their performance that the creation of new indexes would have.

Select SQL to check for performance changes:

Select SQL from

The SQL statements used in an analysis can be taken from the SQL Repository or the SQL Scanner. If you select SQL statements from the SQL Scanner, these statements are added to the SQL Repository.

Select the SQL statements for the analysis. If you select SQL statements from the SQL Scanner, the bottom portion of this section displays the SQL Repository folder so that you can select the folder where they will be stored.

SQL query plans to be analyzed:

In order to analyze the impact the proposed indexes might have on the performance of the SQL statements, the Index Impact Analyzer compares the query before the indexes are created to the plans after the indexes are created. The query plans from before the analysis can be obtained two ways:

Using existing query plan saved with the SQL

This option uses the query plan that was saved with the SQL statement when it was saved in the SQL Repository or SQL Scanner.

Obtaining a new query plan under the current connection

This option retrieves the query plan with the current logon and the current database settings. This current query plan is compared to the query plan that is retrieved after the new indexes are created.

 

Related Topic

New Analysis Wizard: Index Page

Index Impact Analyzer > Create an Index Impact Analyzer > New Analysis Wizard: Index Page

In the New Analysis wizard, the Index page is used to specific the index(es) that are used for this analysis.

Query plans will be grouped under a Scenario

An individual Index Impact Analyzer consists of a group of stored SQL statements with the query plans called the "Baseline Plan." The actual Impact Analysis displays in a "Scenario." A Scenario shows the comparison information for the stored SQL statements identifying the impact on the query plan that the creation of new index will have.

Name

Enter a name for the Scenario.

Description

Enter a description.

Enter and select indexes to be analyzed

Use Add Index to add the indexes for this Scenario.

Segment

Select the segment where you would like the index created from the drop-down list.

Consumer (Default 1 Range 1-20)

Specify the number of consumer processes that should perform the sort operation for creating the index. The actual number of consumer processes used to sort the index may be smaller than the specified number, if fewer worker processes are available when Adaptive Server executes the sort.

To add an index

Click Add Indeximage\B_AddIndex.gif to bring up the Add Index wizard.

To remove an index

Select the index and click Remove Index image\B_RemoveIndex.gif.

After you have created the first Scenario, you can add more Scenarios.

 

Related Topic

Add Index Wizard

Index Impact Analyzer > Create an Index Impact Analyzer > Add Index Wizard

The Add Index wizard is used to create the DDL for creating of the indexes for the Index Impact Analysis. This wizard is available on the Index page in the Index Impact Analyzer's New Analysis wizard. 

To open this Add Index wizard

Click image\B_AddIndex.gif.

Item Description
Index name Enter a name for the index.

Database

Select the database where the index is to be created from the drop-down list.

Owner

Select the owner of the index from the drop-down list.

Table

Select the table where the index is to be created from the drop-down list.

Table columns

List of all the columns from the selected table.

Index columns

Move the columns to be indexed from the Table columns list by either double-clicking the column or highlighting the column and clicking image\B_AddDatabaseObjects.gif.

Unique

Specify to create a unique index.

Clustered/Nonclustered

Select to create either a clustered or a nonclustered index

 

Related Topic

New Analysis Wizard: Abstract Plan Page

Index Impact Analyzer > Create an Index Impact Analyzer > New Analysis Wizard: Abstract Plan Page

In the New Analysis wizard, the Abstract Plan page is used to include the abstract plan with SQL statements.

Baseline Plan

Dump abstract plan

Specify whether to retrieve the abstract plan for the SQL statement for the before new indexes are created. The abstract plan displays in the right pane for SQL statements. This retrieves a new abstract plan even if one was saved with the original SQL. It is important to use this option if you originally saved the SQL statement and abstract plans in version 12.5.0.3 or earlier of Adaptive Server and are now using Adaptive Server 15.0 or later since the format of the abstract plan has changed.

Note: This option is disabled if you have selected Using existing query plan saved with the SQL on the Select SQL page.

Group name

Specify the abstract plan group name where the abstract plans are saved. The default group names in Adaptive Server are: ap_stdout and ap_stdin. These groups are usually used by the Database Administrator to enable server-wide abstract plan capturing and retrieving.

ap_stdout is used by default to capture an abstract plan.

ap_stdin is used by default to retrieve the abstract plan associated with a SQL statement during the execution of the SQL statement.

Abstract Plan Manager button

Opens the Abstract Plan Manager window to view, create, and modify abstract plan group.

Scenario

Dump abstract plan

Specify whether to retrieve the abstract plan for the SQL statement after the indexes are created. The abstract plan is displayed in the right pane for SQL statements.

Group name

Specify the abstract plan group name where the abstract plans are saved. The default group names in Adaptive Server are: ap_stdout and ap_stdin. These groups are usually used by the Database Administrator to enable server-wide abstract plan capturing and retrieving.

ap_stdout is used by default to capture an abstract plan.

ap_stdin is used by default to retrieve the abstract plan associated with a SQL statement during the execution of the SQL statement.

Abstract Plan Manager button

Opens the Abstract Plan Manager window to view, create, and modify abstract plan group.

 

Related Topic

Verwandte Dokumente

The document was helpful.

Bewertung auswählen

I easily found the information I needed.

Bewertung auswählen