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

Animate Query Plans

SQL Information and Functions > Query Plan > Animate Query Plans

You can have the steps in the execution plan highlighted one-by-one in the order they are executed.

To animate the plan or cancel the animation

Right-click the Query Plan window and select View Plan | As Tree Plan or As MS Graphic Plan.
Right-click the Query Plan window and select Animate Plan Steps.

Related Topics

Query Plan Overview

Review Query Plans

Query Plan Options

Copy Query Plans

Copy Query Plans

SQL Information and Functions > Query Plan > Copy Query Plans

You can copy the query plan to the clipboard.

To copy a query plan

Right-click the Query Plan window and select Copy.

When the query plan displays as Tree Plan and Plan Text Language the query plan can be pasted as text or as a bitmap picture. Graphic applications, such as MS Paint, only paste the query plan as a bitmap. Text applications, such as Notepad, only paste it in a text format. Applications that handle both text and graphics, such as Microsoft Word, allow you to choose the format if you select the Paste Special option to paste the image. This option is usually found on the Edit menu.

Related Topics

Query Plan Overview

Review Query Plans

Query Plan Options

Animate Query Plans

Plan Detail

SQL Information and Functions > Query Plan > Plan Detail

The Plan Detail window provides detailed information on the selected branch of the query plan. This window is opened in two different ways depending on which display view you have selected for the query plan.

To view the Plan Detail – if you are viewing the query plan as Tree Plan, Plain Language Plan, Graphic Plan, or Microsoft Graphic Plan

Select the query plan step.
Click Plan Detail $image\B_PlanDetail.gif$ .

To view the Plan Detail – if you are viewing the query plan as Text

Click the query plan step.

Note: Help | Show Plan Detail must be selected. If you clear this menu option, then the Plan Detail will not be displayed in the Text view of the query plan.

The Plan Detail provides the following information on each branch of the query plan:

Item	Description
StmtText	Text of the transact-SQL statement.
PhysicalOp	Physical implementation algorithm for the node.
LogicalOp	Relational algebraic operator for the node.
Argument	Supplemental information about the operation being performed, the content depends on the physical operator.
DefinedValues	A list of values introduced by the operator.
EstimateRows	Estimated number of output rows by the operator.
EstimateIO	Estimated I/O cost for the operator.
EstimateCPU	Estimated CPU cost for the operator.
AvgRowSize	Estimated average row size (in bytes) of the row being passed through the operator.
TotalSubtreeCost	Cumulated estimated cost of the operation and all child operation.
OutputList	List of columns being projected by the operation.
Warnings	List of warning messages related to the operation.
Type	Node type.
Parallel	Indicator of whether the operator is running in parallel (0 represents the operator is not running in parallel, while 1 represents the operator is running in parallel).
EstimateExecutions	Estimated number of times the operator will be executed while running the current query.

Table/View Detail

The Plan Detail also provides detailed information about the table and views used by the SQL statement.

To see the table or view details

Click the table or view name in the left pane of the Plan Detail window.
Click one of the five tabs at the bottom of the right pane to see specific information about the definition, columns, indexes, constraints/key or data for the table or view.

Related Topics

Query Plan Overview

Review Query Plans

Query Plan Options

Animate Query Plans

Copy Query Plans

Search Menu

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