Chat now with support

Get Live Help
Complete Registration

Sign In

Request Pricing

Contact Sales

SQL Optimizer for SAP ASE 3.9.1 - User Guide

Table of Contents

Introduction to SQL Optimizer for SAP ASE New in This Release Database Versions Supported User Logon Privileges Adaptive Server Configuration Enter Authorization Key Connect to the Database Synchronize the Data Dictionary About Product Improvement Program

SQL Inspector Tutorial SQL Collector for Monitor Server Tutorial SQL Scanner Tutorial Performance Monitor Tutorial SQL Optimizer Tutorial Index Advisor Tutorial Abstract Plan Manager Tutorial SQL Repository Tutorial Save SQL to the SQL Repository from other modules Tutorial Configuration Analyzer Tutorial Migration Analyzer Tutorial Index Impact Analyzer Tutorial Index Usage Analyzer Tutorial Database Explorer Tutorial Object Extractor Tutorial SQL Worksheet Tutorial Code Finder Tutorial SQL Formatter Tutorial User-Defined Temp Tables Tutorial SQL History Tutorial

Preferences Overview Optimization for Pre-ASE 15 Tab

Optimization Settings for Pre-ASE 15 Intelligence (Pre-ASE 15) Optimization Forces (Pre-ASE 15) Quota (Pre-ASE 15) Abstract Plan

Optimization for ASE 15 Tab

Optimization Settings for ASE 15 Intelligence (ASE 15) Optimization Forces/Goal (ASE 15) Criteria Quota (ASE 15) Abstract Plan

Index Advisor Tab

Index Advisor

Directory Setup Tab

Directory Setup Directory (1) Directory (2)

Activity Log Tab

Activity Log

SQL History Tab

SQL History

Plan

SQL Worksheet Tab

SQL Worksheet

SQL Scanner Tab

SQL Scanner Options (SQL Scanner Tab) Abstract Plan (SQL Scanner Tab) General (SQL Scanner Tab)

SQL Classification Tab

SQL Classification Problematic SQL Complex SQL Simple SQL Invalid SQL

General (General Tab) Message (General Tab) Result Set (General Tab) Batch Run (General Tab)

Editor

Database Setting Tab

Database Setting

DDL

Performance Monitor

Performance Monitor Preferences General Tab Chart Setting Tab Information Tab Top-N Display Tab

Editor Functions

Syntax Highlight Auto-Correction Member and Argument Lookup Indent and Outdent Text Comment and Uncomment Text Change Text to Uppercase and Lowercase Bracket Pairing Customize Editable Panes Editor Functions Editor Panes Shortcuts

SQL Information and Functions

Send SQL Statement to SQL Optimizer Copy to SQL Worksheet Send to Index Advisor Open SQL from SQL Repository Save SQL to SQL Repository Create Benchmark Factory Import File SQL Navigation Modify Data Copy Data from a Single Cell Comments Parameters Parameters Window Filter Database Objects SQL Information Pane Query Plan

Query Plan Overview Review Query Plans Query Plan Options Animate Query Plans Copy Query Plans Plan Detail

Quick Find Find Find Next Replace Goto Line

Performance Monitor

Performance Monitor Overview Performance Monitor Privileges Supported Monitoring Tables Performance Monitor Window

Performance Monitor Window Right Pane for Database Statistics Summary Right Pane for Monitoring Statistics Right Pane for Information Status Bar

Open the Performance Monitor Connect and Disconnect from Database Start and Stop the Performance Monitor Manual Refresh Data Hide and Show Console Tree Chart Information

Display View for Performance Monitor Charts Chart Drill Down Lock and Unlock Chart Chart Zoom Copy Chart Print Chart Save Chart

Performance Monitor Functions Performance Monitor Preferences

Performance Monitor Preferences General Tab Chart Setting Tab Information Tab Top-N Display Tab

SQL Inspector Overview SQL Inspector Privileges Performance Statistics - Monitoring Tables Performance Statistics - QP Metrics Data Directory Setting Open SQL Inspector SQL Inspector Window Add/Modify Inspector

Add/Modify Inspector General Information Monitor Process (Monitoring Tables) Retrieval Criteria (Monitoring Tables) Schedule Settings (Monitoring tables) Retrieval Criteria (QP Metrics) Schedule Time (QP Metrics)

Inspect Ad-hoc Inspect Abort Inspect Scan Inspector Find Inspector Delete Marked Inspector View Inspector Properties Place Bookmarks in SQL Inspector Window Create Benchmark Factory Import File View and Add Charts Scan Inspected SQL Sum Statistics for a Stored Procedure SQL Inspector Functions

SQL Collector for Monitor Server

SQL Collector for Monitor Server Overview Adaptive Server Configuration Parameters for SQL Collector Data Directory Setting SQL Collector Window Open SQL Collector for Monitor Server Add or Modify a Collector

Add or Modify a Collector General Information Monitor Process Collecting Criteria Schedule Settings

Query Plan Monitored SQL Statement Types Monitor Ad-hoc Monitoring Abort Monitor Why use the SQL Scanner to Identify Problematic SQL Statements Scan Collectors Place Bookmarks in SQL Collector View Particular Types of SQL Statement Find Specific Information in Collectors Find SQL Statement Generate a Report for Monitored SQL Statements Collector Properties SQL Collector Functions

SQL Scanner Overview SQL Scanner Window Group Manager Window View Group Summary View Group Properties Embedded and Dynamic SQL Statements What files does SQL Scanner scan? Data Directory Setting Open SQL Scanner Add Scanner Jobs

Add Jobs to SQL Scanner Add Database Objects (Add Jobs Wizard) Add Abstract Plan Groups (Add Jobs Wizard) Add SQL Collectors from the SQL Collector (Add Jobs Wizard) Add SQL Inspectors (Add Jobs Wizard) Add Source Code (Add Jobs Wizard) Summary (Add Jobs Wizard)

Scan Scan Source Code with Temp Tables Review Scanned SQL Find Jobs Find SQL Using a Text String View Job Properties Open Group Manager while SQL Scanner is Open Check Scanned SQL Mark and Unmark All Jobs Move or Copy Jobs to Other Group Switch Database and User Modify a Job Placing Bookmarks in SQL Scanner Open in SQL Collector Open in SQL Inspector Create Benchmark Factory Import File View a Particular SQL Type Generate Report for Scanned SQL Save Abstract Plan Create Benchmark Factory Import File SQL Scanner Functions SQL Conversion

SQL Conversion Overview Trigger Conversion External Parameter Conversion Local Variable Conversion Cursor Query Plan Conversion Into Clause Conversion COBOL Conversion

Index Advisor Overview Index Advisor Window SQL for Cursor Checkbox Run Time Tab Page Statistics Tab Page Chart Tab Page Filter the Run Time Results Advise Index Alternatives Index Advising Details Add Index Alternatives Batch Show Plan Create Indexes Analyze Impact of New Indexes Clear All Index Alternatives Index Advisor Functions Batch Run

Determine Best Performing Index Alternative Create Indexes for Batch Run Selected Index Set SQL Termination (Options) SQL Termination (Percentage Delay) Batch Termination Run Time Mode Batch Run Schedule View Batch Run Details

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

SQL Optimizer Window Enter Original SQL Statement Retrieve Query plan Show Default Plan Optimize Original SQL Statement SQL for Cursor Checkbox Optimize Using Abstract Plan Only Insert User-Defined SQL Open Optimized SQL Create Benchmark Factory Import File

Optimized SQL Optimization Details Window Input Parameter Values Analyze Time and Statistics Results Analyze Run Time Results Analyze Statistics Results Analyze Chart Results Filter Run Time Results Find SQL Using a Text String Generate a Report for Optimized SQL Verify Correctness of Optimized SQL Statements Insert User-Defined SQL Check Abstract Plan Compatibility Save Abstract Plan Test for Scalability Save Optimized SQL

Save Optimized SQL Open Optimized SQL View Open Optimized SQL Details Refresh Plan Details Window

Retrieve Run Time for a Group of SQL Selected SQL SQL Termination (Options) SQL Termination (Percentage Delay) Batch Termination Run Time Mode Batch Run Schedule Commit or Rollback View Batch Run Details

Run Time and Run Result

Run Time Retrieve Run Time Terminate a Run Time SQL Statement Retrieve Run Result Commit or Rollback Terminate Retrieval of Run Result

SQL Comparer Overview SQL Comparer Window Open SQL Comparer Window Start and Stop Comparison Switch View in SQL Comparer Window SQL Comparer Functions

Activity Log Overview Start Recording Activities to the Activity Log View Activity Log Report Purge Activity Log Data Stop Recording Activities

SQL Worksheet Overview SQL Worksheet Window Execute SQL or Transact SQL Retrieve Description of Tables and Views Modify the Data New Database Session Command List Bookmarks SQL Worksheet Functions

SQL Formatter Overview SQL Formatter Window Format a SQL statement Parameters within SQL Formatter SQL Formatter Functions

Database Explorer

Database Explorer Overview Database Explorer Window Database Explorer Privileges Open Database Explorer Modify Data Copy column names to another window Database Explorer Functions

Code Finder Overview Code Finder Window Search Database Objects and Files Regular Expression Open Database Object or File in SQL Worksheet Extract Object DDL to File Save Search Results Code Finder Functions

Object Extractor

Object Extractor Overview Object Extractor Window Extract Object DDL and Dependencies Extraction Criteria Object Extractor Functions

SQL Repository Overview SQL Repository Window Add SQL to SQL Repository

Add SQL to SQL Repository Add SQL Wizard: General Page Add SQL Wizard: SQL Information Page Add SQL Wizard: Settings Page

Refresh SQL Query Plan Modify SQL Rename SQL Delete SQL Create Benchmark Factory Import File SQL Repository Functions

Index Impact Analyzer

Index Impact Analyzer Overview Index Impact Analyzer Window

Index Impact Analyzer Window Right Pane for Analyzers Right Pane for Scenarios Analyzed Folder Right Pane for Scenario Right Pane for SQL Analyzed Folder Right Pane for SQL Statements

Create an Index Impact Analyzer

Create an Index Impact Analyzer New Analysis Wizard: Analyzer Page New Analysis Wizard: Select SQL Page New Analysis Wizard: Index Page Add Index Wizard New Analysis Wizard: Abstract Plan Page

Drop Indexes View Index Impact Analysis Details Add SQL to an Index Impact Analysis Add a Scenario Delete an Index Impact Analysis Modify an Index Impact Analyzer Regenerate Rename an Index Impact Analyzer Index Impact Analyzer Functions

Index Usage Analyzer

Index Usage Analyzer Overview Index Usage Analyzer Window

Index Usage Analyzer Window Right Pane for Analyzers Right Pane for Tables Analyzed Right Pane for Analyzed Table Information Right Pane for SQL Analyzed Folder Right Pane for SQL Statements

Create an Index Usage Analysis

Create an Index Usage Analysis New Analysis Wizard: Analyzer Page New Analysis Wizard: Select SQL Page Update an Index Usage Analysis

View Index Usage Analysis Details Modify an Index Usage Analysis Rename an Index Usage Analysis Delete an Index Usage Analysis Index Usage Analyzer Functions

Configuration Analyzer

Configuration Analyzer Overview Configuration Analyzer Window

Configuration Analyzer Window Right Pane for Analyzers Right Pane for Scenarios Analyzed Right Pane for Scenarios Right Pane for SQL Analyzed Folder Right Pane for SQL Statement

Create a Configuration Analysis

Create a Configuration Analysis New Analysis Wizard: Analyzer Page New Analysis Wizard: Select SQL page New Analysis Wizard: Configuration Page New Analysis Wizard: Abstract Plan

Regenerate a Configuration Scenario Add a Scenario View Configuration Analysis Details Roll Back Changes to Configuration Parameters Add SQL to a Configuration Analysis Modify a Configuration Analysis Rename a Configuration Analysis or Scenario Delete a Configuration Analysis or Scenario Modify a Configuration Scenario Configuration Analyzer Functions

Migration Analyzer

Migration Analyzer Overview Migration Analyzer Window

Migration Analyzer Window Right Pane for Analyzers Right Pane for Scenarios Analyzed Right Pane for Scenarios Right Pane for SQL Analyzed Folder Right Pane for SQL Statement

Create a Migration Analysis

Create a Migration Analysis New Analysis Wizard: Analyzer Page New Analysis Wizard: Select SQL Page New Analysis Wizard: Migration Page New Analysis Wizard: Destination Configuration Page New Analysis Wizard: Abstract Plan

View Migration Analysis Details Migration Analysis Results Add a Scenario Roll Back Changes to Configuration Parameters Add SQL to a Migration Analysis Modify a Migration Analysis Regenerate a Migration Scenario Delete a Migration Analysis or Scenario Rename a Migration Analysis or Scenario Modify a Migration Scenario Migration Analyzer Functions

Abstract Plan Manager

Abstract Plan Manager Overview Abstract Plan Group Why Save the Abstract Plan? Abstract Plan Compatibility with Original SQL Abstract Plan Manager Window Open the Abstract Plan Manager Abstract Plan Group Functions Export a Group to a Table Import an Abstract Plan for each User Abstract Plan ID Functions Use Saved Abstract Plans

User-Defined Temp Tables

User-Defined Temp Table Overview Create Temporary Tables Parameters in Temporary Table SQL View SQL scripts of temporary tables Drop Temporary Tables Example of Temporary Tables in SQL Scanner Copy SQL with Temporary Tables to SQL Optimizer Create Alternative SQL Which Uses Temporary Tables Preference Settings for Handling Temporary Tables

SQL History Overview SQL History Window Filter SQL Statements Recall SQL Statements SQL History Functions

Legal Information

Copyright Third-party contributions

Embedded and Dynamic SQL Statements

SQL Scanner > Embedded and Dynamic SQL Statements

Generally speaking, there are two types of SQL statements found in source code.

Embedded SQL Statements

Embedded SQL statements refer to SQL statements that are placed within the source programs and are constructed at compilation time.

Dynamic SQL Statements

Dynamic SQL statements are SQL statements constructed at run time.

Related Topic

SQL Scanner Overview

What files does SQL Scanner scan?

SQL Scanner > What files does SQL Scanner scan?

The SQL Scanner extracts SQL statements from the following:

Database Objects

Database objects with embedded SQL statements. This includes: stored procedures, triggers, views, rules and defaults. Depending on your database privileges, the available database objects can be selected by object name, object type, or user name.

Abstract Plan Groups

SQL statements saved in abstract plans.

SQL Collector for Monitor Server Files

SQL statements captured in the SQL Collector for Monitor Server module during execution.

SQL Inspector Files

SQL statements retrieved in the SQL Inspector module from the Adaptive Server monitoring tables.

Text/Binary Files

Text and binary files that contain embedded SQL statements.

COBOL Files

COBOL source code that contain embedded SQL statements.

Note: If your SQL statements are dynamic SQL statement generated on the fly or at run time, you can use the SQL Collector for Monitor Server to capture the SQL statements. Then the SQL Scanner can be used to analyze the captured SQL statements.

Related Topics

SQL Scanner Overview

Add Jobs to SQL Scanner

Data Directory Setting

SQL Scanner > Data Directory Setting

The SQL Scanner data directory is used to store the data files created while executing the Scan function. The data directory path is stored in the Preferences window.

To change the data directory

Click $image\B_Preferences.gif$ .
Select the Directory Setup tab.

The default setting for this directory is:

C:\Documents and Settings\User\Application Data\Quest Software\SQL Optimizer\DATA

Changes to this directory cannot be made while the SQL Scanner is active.

Note: It is advisable not to change the data directory after selection, as files created during scanning are kept in this directory.

Related Topic

SQL Scanner Overview

Open SQL Scanner

SQL Scanner > Open SQL Scanner

To open the SQL Scanner

Click $image\B_SQLScanner.gif$ .
After creating a new Group or selecting an existing Group, click Open.

The SQL Scanner window displays after opening the working Group. If it is an empty group, the Add Jobs wizard also displays.

Related Topics

SQL Scanner Overview

SQL Scanner Window

Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating

© ALL RIGHTS RESERVED. Feedback Terms of Use Privacy Cookie Preference Center