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

Preferences Overview

Preferences > Preferences Overview

Preferences allow the alteration of the default settings used in several of the modules.

To open the Preferences window

Click $image\B_Preferences.gif$ .

When you change the settings, your latest settings are always saved and restored each time to exit and restart the program. You can also save several sets of preference settings either by saving settings from one or more tab pages or by saving all the preference settings.

To save specific settings so that you can use them later

Select the Preferences settings.
Click Save.
Enter a filename for the saved settings. Click Save.
From the Save Preferences Profile window, select the tab pages for the setting you want to save. Click OK.

To restore the saved settings

In the Preferences window, click Load.
In the Open File window, select the filename.
Click Open.

Optimization for Pre-ASE 15 Tab

Optimization Settings for Pre-ASE 15

Preferences > Optimization for Pre-ASE 15 Tab > Optimization Settings for Pre-ASE 15

The Optimization for Pre-ASE 15 tab on the Preferences window consists of 5 buttons that allow users to select the level of optimization intelligence and options for optimization while using the SQL Optimizer module when connected to Adaptive Server 12.5.2 or earlier.

Related Topic

SQL Optimizer Overview

Intelligence (Pre-ASE 15)

Preferences > Optimization for Pre-ASE 15 Tab > Intelligence (Pre-ASE 15)

Closed

View Optimization for Pre-ASE 15--Intelligence

The Intelligence page on the Optimization tab of the Preferences window allows you to select your optimization intelligence settings.

Optimization intelligence settings enables you to either choose the settings used during optimization or allows you to select the predefined "Intelligence Level" settings.

Custom

Customize the settings on the Optimization, Forces, and Quota pages.

Predefined

Use the predefined optimization intelligence levels. The items selected on the Forces and Quota pages and the Temp table generation option on the Optimization page change according to the level selected. Levels range from 0 to 10. The higher the level the more intelligent the SQL Optimizer is and the more likely of finding a better SQL alternative.

The Abstract Plan settings on the Abstract Plan page and the Join table and Advanced SQL transformation options on the Optimization page is adjusted independent of the optimization intelligence level.

Related Topics

SQL Optimizer Overview

Optimization Preferences

Abstract Plan Preferences

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