Chat now with support
Chat with Support

SQL Optimizer for SAP ASE 3.9.1 - 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

Add SQL Wizard: General Page

The General page of the Add SQL wizard is used to name the SQL statement and to select the folder where you want the SQL statement saved.

Item Description
Name

Enter the name for the SQL to be saved in the SQL Repository.

Description

Enter the description for the SQL.

Last modified

Displays the last modified date and time.

Login name

Displays the connected login name.

Server name

Displays the connected server name.

Database

Displays the connected database.

User

Displays the connected user name.

Save SQL to location

Displays the tree location in which the SQL will be saved. In the bottom pane, select the folder where you want to save the SQL. To create a new folder, use image\B_AddFolder.gif.

 

Related Topic

Add SQL Wizard: SQL Information Page

The SQL Information page of the Add SQL wizard is where to enter the SQL text and view various information about the SQL statement, such as the query and abstract plans.

Left pane

Enter the SQL text.

Right Pane

At the top of the right pane are buttons that display the query plan, the abstract plan, Trace On information, the SQL classification and connection information, and the DDL for creating any temporary tables used by the SQL statement.

Check SQL button

Checks the SQL syntax using the current database connection and retrieves the query plan, SQL type classification and other information relating to the SQL statement. To get the query plan most appropriate for your SQL statement, make sure that you have made the corresponding selections from the Settings tab before retrieving the checking the SQL.

 

Related Topic

Add SQL Wizard: Settings Page

The Settings page of the Add SQL wizard is used to set various parameters that affect the retrieval of the query plan for the SQL statement.

SQL Settings section

SQL for Cursor

Adaptive Server uses a difference query plan for a SQL statement that is embedded in a cursor declaration from the query plan when the SQL statement is not embedded in a cursor. This needs to be taken into account when retrieving the query plan or run time and also when generating SQL alternatives.

Therefore, if the original SQL statement comes from or will be embedded in a cursor declaration then you need to select the SQL for Cursor checkbox. This enables cursor simulation when retrieving the query plan.

Use Default Plan

This option uses the BINARY data type when executing the Show Plan function for all variables in the SQL statement. This is useful when you want to quickly investigate the query plan of the original SQL statement without having to select the data type for each variable.

Abstract Plan section

Dump abstract plan

Specify whether to retrieve the abstract plan for the SQL statement whenever the query plan is retrieved. The abstract plan is not saved on the database until you deliberately save it. 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.

Group name

Specify the abstract plan group name where the abstract plan for this SQL statement is saved.

Abstract Plan Manager button

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

Database Settings section

Set Ansinull On

Specify the option with regards to comparison of NULL values.

Set Quoted_identifier On

Specify whether to allows the use of delimited identifier (" ") for table names.

Set Statistics Simulate On SAP ASE 15 or later)

Specify whether to load simulated statistics into the database. Simulated statistics can be generated using optdiag command and can be used to optimize SQL statements using the simulated statistics rather than the actual statistics.

dbcc traceon (3604, 302, 310) (sa_role privilege only)

Specify whether to retrieve the trace on information which displays the reasons why the Adaptive Server optimizer chooses to resolve the SQL statement in a particular way. This option is applicable only if you have sa_role privileges.

 

Related Topic

Refresh SQL Query Plan

If you feel the query plan stored with the SQL statement in the SQL Repository does not represent the current indication of performance due to database changes, you can retrieve the current query plan from the database.

To refresh the query plan in the SQL Repository

  1. Select the SQL.

  2. Select SQL | Refresh Plan or right-click the tree view and select Refresh Plan to open the Refresh Plan window.

  3. The original query plan displays on the left pane while the newly retrieved query plan is displayed on the right pane for comparison. You can view and analyze the newly retrieved query plan, abstract plan, and SQL classification before saving the new query plan with the SQL statement.

  4. Click Save to replace the original query plan with the newly retrieved one.

 

Related Topic

Related Documents