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

Test for Scalability

SQL Optimizer > Optimized SQL > Test for Scalability

You can test a single SQL statement to find out how it will perform under different amounts of data in your database in Quest's Benchmark Factory program (version 4.6 or later).

To send SQL statement to Benchmark Factory:

Optimize the original SQL statement.
Select the original or one of the SQL alternatives
Click $image\B_TestForScalability.gif$ .

Save Optimized SQL

Save Optimized SQL

SQL Optimizer > Optimized SQL > Save Optimized SQL > Save Optimized SQL

You can save the Optimized SQL statements. Unlike a report, this file is used to reload the SQL back to the SQL Optimizer window using the Open Optimized SQL function.

To save the original SQL and the SQL alternatives

Select SQL | Save Optimized SQL.

Related Topics

Open Optimized SQL

Open Optimized SQL

SQL Optimizer > Optimized SQL > Save Optimized SQL > Open Optimized SQL

After you have saved the optimized SQL statements to a file, you can load them back through the SQL Optimizer window:

To load a saved optimized SQL statements and alternatives

Select SQL | Open Optimized SQL.
Select the file you want to load and click Open. This loads the saved SQL statements to the SQL Optimizer window. The Open Optimized SQL Details window displays the following:

Summary tab

Optimization Information

Displays the original connection and optimization settings information.

Last Saved Query plan Information

Displays the saved and current connection information and whether there are any changes in SQL structure and query plans.

User-Defined Temp Table Tab

If the SQL statement uses a temporary table, the User-Defined Temp Table tab displays in this window. It displays the DDL used to create the temporary table.

Changes Tab

If there are any changes to the SQL structure or the access plans, the Changes tab displays inI this window. It displays the SQL text along with the saved and current query plan.

If there are any changes in either SQL structure or query plans it is advisable that you refresh the query plans so that the reloaded image is a truth reflection of the current environment before any further testing is done. Click Refresh Plan from the Open Optimized SQL Details window. If there are changes in the query plan the corresponding SQL statements run time and statistics information are deleted. All invalid SQL statements are removed, except for the original SQL statement. You have an option to eliminate SQL statements with duplicate query plans.

After refresh, the Refresh Plan Details window can be displayed. This window displays the number of query plans refreshed, the total eliminated, and invalid plans.

The Open Optimized SQL Details and Refresh Plan Details windows can be reviewed at a later stage.

To view the details

Select View | Show Open Optimized SQL Details and View | Refresh Plan Details.

Related Topics

View Open Optimized SQL Details

Refresh Plan Details Window

Save Optimized SQL

Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating

© 2024 Quest Software Inc. ALL RIGHTS RESERVED. Feedback Terms of Use Privacy Cookie Preference Center