Chat now with support

Get Live Help
Complete Registration

Sign In

Request Pricing

Contact Sales

SQL Optimizer for SAP ASE 3.8 - 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

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

Start and Stop the Performance Monitor

Performance Monitor > Start and Stop the Performance Monitor

The Performance Monitor provides three ways to monitor the database performance: immediate monitoring, scheduled monitoring, and manual refresh.

The settings on the General tab in Preferences window determine whether the monitoring process begins immediately or at a scheduled time. The monitoring continues to collect the statistics at the refresh interval until you stop the monitoring or until the scheduled end time.

To start the monitoring processing for collecting the database performance statistics

Click $image\B_Monitor_PM.gif$ .

To stop the monitoring process

Click $image\B_AbortMonitor_PM.gif$ .

The manual refresh of the statistics collects the statistics immediately and only collects them once.

Related Topic

Performance Monitor Overview

Manual Refresh Data

Performance Monitor > Manual Refresh Data

The Manual Refresh Data option is used to check the database performance on ad-hoc basis. The performance snapshot is taken once. This function provides a more flexible statistics retrieval method for troubleshooting a performance problem that is currently happening on the database. It only retrieves the statistics from the monitoring tables when you specifically do the refresh. It retrieves the statistics once and then waits until you manual refresh the data again.

To manually refresh data

Click $image\B_ManualRefreshData.gif$ .

You can set up the monitoring to automatically poll the monitoring tables at regular intervals.

The collection of the Open Database, Open Objects, and the Process statistics are not collected unless the checkbox in front of the statistic is checked in the left pane. The enabling of the retrieval of the statistics for these monitoring options may result in the capture of a large amount of information from the database. Therefore it is not recommended to turn it on when you are using Auto Refresh to continuously monitor the activity of Adaptive Server. But when you need to troubleshoot a current database performance problem, enabling the retrieval of these statistics can help you to quickly identify the problem. Using Manual Refresh Data to take a snapshot can provide good insight on processes running, such as Top N CPU intensive processes information. Processes that are blocked by other processes are marked with a special icon and the related blocking processes can also be displayed.

Related Topic

Performance Monitor Overview

Hide and Show Console Tree

Performance Monitor > Hide and Show Console Tree

The display of the Console Tree [left pane] in the Performance Monitor window can be toggled between show and hide.

To hide/show the Console Tree

Click .

Chart Information

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