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

User Logon Privileges

Adaptive Server has database privileges that either allow or restrict the access and authority of each user. Some of the modules in SQL Optimizer need specific privileges. When you logon to SQL Optimizer, it checks to see if the logon satisfies the privileges. If your user account does not have a specific privilege, you can still use all the other modules in the program.

To see if your logon satisfies the privilege requirements for each module

Select Database | View Connection Information.

Privilege/Setting Program Module Operation Description

"select on syscomments.text",1

Database Explorer and SQL Scanner

Retrieve the database objects' SQL text.

You must have privilege to access the system catalog table, syscomments, therefore the "select on syscomments.text" configuration parameter must be turned on.

sa_role

Adaptive Server 15 or later

 

"event buffers per engine", 2000

"max SQL text monitored", 4096

SQL Collector for Monitor Server

Capture currently running SQL statements

The SQL Monitor is not available if you do not have sa_role privileges. In addition, the following Adaptive Server parameters need to be set to use the Adaptive Server Enterprise Monitor Server. They are:

sa_role

SQL Optimizer

Abort Run Result for SQL statements with result set

If you do not have sa_role privileges, you will not be able to abort the Run Result operation.

Note: The sa_role is only required for Run Result operation. You can terminate SQL statements during a Batch Run without the sa_role.

For SQL statements without a result set (for example, INSERT, UPDATE, DELETE and SELECT with INTO clause) can be terminated without having the sa_role.

sa_role

Adaptive Server 15.0 or later

"allow resource limits", 1

 

SQL Optimizer,

Migration Analyzer,

Configuration Analyzer,

Index Impact Analyzer,

Index Advisor

 

Retrieve Estimated I/O Cost

The estimated I/O cost is not available for SQL statements during optimization under the following conditions:

a. Your logon account does not have sa_role privilege.

b. You are using Adaptive Server 15.0 or later and "allow resource limits" configuration parameter is turned off.

If you do not have the privilege to retrieve the estimated I/O cost, then "N/A" is shown for the cost.

sa_role

Turn on dbcc traceon (3604,302,310) option under the Database Settings tab of the Preferences setting.

 

SQL Information Panel

Trace on information

The “Trace on” feature shows the reason why the Adaptive Server optimizer has chosen a particular way to execute the source or alternative SQL statement and displaying the reasons for index and table joins selection. This information is located in the SQL Optimizer window or the SQL Scanner window. “Trace on” details are not available if you do not have sa_role privilege. In addition, the dbcc traceon option needs to be selected from the Preferences settings.

a. Select Preferences from the File menu.

b. Select the Database Settings tab page.

c. Check ON the dbcc traceon (3604,302,310) option.

 

Create Procedure

SQL Optimizer

Optimize for cursor

The CREATE PROCEDURE privilege is required to optimize a SQL statement that runs inside a cursor.

A procedure is created when the SQL for Cursor checkbox is selected in the SQL Optimizer window. The stored procedure is created to retrieve the query plan and run time. The stored procedure is executed and then dropped.

Create/Drop Index

Index Adviser and Index Impact Analyzer

 

Retrieve query plan and in Index Advisor to retrieve the run time.

You must have the privilege to create and drop indexes when retrieving run time (Index Adviser only) and query plan.

"abstract plan cache",0

"abstract plan replace",0

Adaptive Server 15 and later

SQL Optimizer

Abstract Plans

Required for ASE 15.0 or later to generate and retrieve abstract plans.

mon_role

Adaptive Server 15.0 or later

 

“enable monitoring”, 1 

”wait event timing”, 1 

”process wait events”, 1 

”max SQL text monitored”, 4096 

”sql text pipe active”, 1 

”sql text pipe max messages”, 1000

”statement pipe active”, 1 

”statement pipe max messages”, 5000

”SQL batch capture”, 1 

 

SQL Inspector

Retrieve SQL information from the Performance tables

You must have privilege to access the Adaptive Server monitoring tables, monSysSQLText and monSysStatement, therefore the mon_role is required.

 

You must also have several configuration parameters set to enable the monitoring

 

mon_role

Adaptive Server 15.0 or later

 

“enable monitoring", 1

"sql text pipe active", 1

"sql text pipe max messages", 100

"plan text pipe active", 1

"plan text pipe max messages", 100

"statement pipe active", 1

"statement pipe max messages", 5000

"errorlog pipe active", 1

"errorlog pipe max messages", 1024

"deadlock pipe active", 1

"deadlock pipe max messages", 1024

"wait event timing", 1

"process wait events", 1

"object lockwait timing", 1

"SQL batch capture", 1

"statement statistics active", 1

"per object statistics active", 1

 

Performance Monitor

Retrieve information from the Performance tables

You must have privilege to access the several of the Adaptive Server monitoring tables, therefore the mon_role is required.

 

You must also have several configuration parameters set to enable the monitoring.

 

 

Related Topic

Adaptive Server Configuration

Adaptive Server has database privileges that restrict the access and authority of each user. Although it is not required that the user logon have all of these privileges, certain functionality will not be available to the user without specific privileges.

When you logon to Quest SQL Optimizer for SAP ASE, it checks to see if the logon satisfies the privileges below.

To see if your logon satisfies the privilege requirements for each module

  1. Select Database | View Connection Information.
  2. Determine which of the following database setup and user privileges are needed for your environment and follow the steps under the following sections.

  • SELECT Permission on database objects

  • sa_role

  • CREATE PROCEDURE

  • Adaptive Server Monitor Tables

  • mon_role

  • Adaptive Server Configuration Parameters

SELECT permission on database objects

If the user is not the owner of the database objects, the user logon account must have the SELECT permission on the objects. If the user logon account does not have access to database objects in Adaptive Server, then these objects cannot be used or viewed in Quest SQL Optimizer for SAP ASE.

sa_role privilege

The sa_role privilege is needed for a few functions in Quest SQL Optimizer for SAP ASE. If the logon does not have this privilege, most of the functionality of Quest SQL Optimizer for SAP ASE is still available.

Assign 'sa_role' to the logins needing the following functionality:

  • Use the SQL Collector for Monitor Server module.

  • Abort Run Result function for SQL statements that return data.

  • Retrieve Trace On (dbcc) information

Grant sa_role to the logon using the following command in the SQL Worksheet or ISQL:

sp_role 'grant', sa_role, logon_name

Create privileges

The CREATE PROCEDURE privilege is required to optimize a SQL statement that runs inside a cursor. A procedure is created when the SQL for Cursor checkbox is selected in the SQL Optimizer window (in the SQL Optimizer module) or when the SQL Scanner finds a SQL statement that is executed within a cursor declaration. The stored procedure is created to retrieve the query plan and run time. The stored procedure is executed and then dropped.

If the user logon does not have sa_role which includes this privilege, then grant this privilege to the logon using the following command in the SQL Worksheet or ISQL:

grant create procedure to logon_name

Adaptive Server monitoring tables

The SQL Inspector and Performance Monitor require installation of the Adaptive Server monitoring table. Later versions of Adaptive Server create the monitoring tables are by default when you install Adaptive Server. So depending on the version of Adaptive Server you are running, you may find these tables already installed. 

The tables are creating using the installmontables script which is included in the Adaptive Server install and is located in the $SYBASE/ASE-version/scripts directory (%SYBASE%/ASE-version/scripts for NT). This script requires that a server named "loopback" be included in sysservers.

If the monitoring tables are not installed yet in your Adaptive Server, follow these steps to for installing the tables.

  1. Create a loopback server entry either using ISQL or SQL Optimizer.

    In SQL Optimizer:

  1. Logon to Quest SQL Optimizer for SAP ASE with sa.

  2. In the bottom left corner of the window, set the database to Master.

  3. In the SQL Worksheet module, execute: sp_addserver 'loopback', null, server_name

In ISQL

  1. Logon to Adaptive Server with sa.

  2. Execute: sp_addserver 'loopback', null, serve_name go
  1. Execute the installmontables script located in the $SYBASE/ASE-version/scripts directory (%SYBASE%/ASE-version/scripts for NT).

isql -Usa -P<sa password> -Sserver_name
-n -i%SYBASE%/%SYBASE_ASE%/scripts/installmontables

mon_role privilege

The mon_role privilege is needed to run the SQL Inspector and Performance Monitor modules. The sa logon does not have this role by default, so it needs to be added to the sa logon and any other user logon. Assign 'mon_role' to the logins using the SQL Worksheet module or ISQL with this command.

sp_role 'grant', mon_role, logon_name

Configuration Parameters

The following modules require specific settings for Adaptive Server configuration parameters. (You can copy, paste and execute the following script into the SQL Worksheet module).

/* parameters for SQL Inspector and Performance Monitor modules using monitoring tables */

/* requires Adaptive Server 12.5.0.3 and later */

sp_configure "enable monitoring",1

go

sp_configure "sql text pipe active",1

go

sp_configure "sql text pipe max messages",1024

go

sp_configure "plan text pipe active",1

go

sp_configure "plan text pipe max messages",5000

go

sp_configure "statement pipe active",1

go

sp_configure "statement pipe max messages",5000

go

sp_configure "errorlog pipe active",1

go

sp_configure "errorlog pipe max messages",1024

go

sp_configure "deadlock pipe active",1

go

sp_configure "deadlock pipe max messages",1024

go

sp_configure "wait event timing",1

go

sp_configure "process wait events",1

go

sp_configure "object lockwait timing",1

go

sp_configure "SQL batch capture",1

go

sp_configure "statement statistics active",1

go

sp_configure "per object statistics active",1

go

sp_configure "max SQL text monitored",4096

go

/* parameters for SQL Inspector module using the QP Metrics */

/* requires Adaptive Server 15.0 and later */

sp_configure 'enable metrics capture',1

go

/* parameters for Abstract Plan Manager module */

/* requires Adaptive Server 12.0 and later */

sp_configure "abstract plan cache",0

go

sp_configure "abstract plan replace",0

go

/* parameters for retrieving Estimated I/O Cost for query plans */

sp_configure "allow resource limits",1

go

/* parameters for SQL Collector for Monitor Server module*/

sp_configure "event buffers per engine",2000

go

sp_configure "max SQL text monitored",4096

go

/* parameter to access syscomments system table to retrieve DDL for database objects. */
/*Access to the system catalog table, syscomments, is needed to view SQL text for procedures, triggers, views, default and rules objects. If you do not have access to the syscomments system table, a message <SQL Text unavailable> will be presented in the Text tab of the Database Explorer window, and you will be unable to scan database object in the SQL Scanner module. */

sp_configure "select on syscomments.text",1

go

Enter Authorization Key

SQL Optimizer requires an authorization key to use the program. When it is first installed it has a trial key that is good for thirty days.

To enter another trial key or to enter the production key

  1. Select Help | Register.

  2. Enter the key.

  3. When you enter a production key after purchasing the product, you must also enter the Site Message information.

  4. Click OK.

 

Connect to the Database

When SQL Optimizer starts, a User Logon dialog field displays for you to connect to the Adaptive Server database.

Item Description
Login name Enter the login name required to connect to the database.

Password

Enter the password associated with the login name.

Server name

Enter the Adaptive Server name as defined in the client configuration.

 

The Server list contains the database servers taken from the SQL.INI file.

Item Description
Host name Enter the name of the computer where the database resides.

Port number

Enter the port number that was assigned to Adaptive Server.

After selecting and entering the connection information, click Connect.

The Loading Data Dictionary window may be shown while connecting to the database to indicate that information from the data dictionary is being retrieved and loaded in the memory of the computer. This information is used when the SQL statement is parsed for functions such as scanning, optimization, and index generation. Depending on the number of objects in your database, the loading of all the information from the data dictionary may take considerable time. Therefore you may want to use the option in the General Preferences with does not load the data dictionary when the connection to the database is made, but does load the specific information that is needed when SQL statements are parsed. This saves time when the connection is made and adds a little more time to the parsing process.

Note: The first time the program is launched, the Language dialog field displays a list of available interface and character set languages. The character set allows you to view data in the chosen language.

Related Documents