Chat now with support
Chat with Support

SQL Optimizer for SAP ASE 3.9.1 - User Guide

Introduction Tutorials Preferences Editor Functions SQL Information and Functions Performance Monitor SQL Inspector SQL Collector for Monitor Server SQL Scanner Index Advisor SQL Optimizer
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 Optimized SQL Activity Log
SQL Worksheet SQL Formatter Database Explorer Code Finder Object Extractor SQL Repository Index Impact Analyzer Index Usage Analyzer Configuration Analyzer Migration Analyzer Abstract Plan Manager User-Defined Temp Tables SQL History Legal Information

Analyze Chart Results

Charts are available for all the statistics and run times. Three chart panes display the run time information.

To change the information in the chart

Click the Chart Title and select the following functionality:

Performance Projection

A performance forecast based on the retrieved Estimated I/O Cost value. If the Performance Projection value is greater then one, then it is forecasted that the SQL alternative may give a better performance.

Estimated I/O cost

Charts the estimated I/0 cost from the SQL statement with the corresponding indexes.

All Records and First Record

Charts the following statistics for All Records or First Record after the run time is retrieved for a SQL statement using the Batch Run, Run for All Records, or Run for First Record.

Statistics Description
Elapsed Time Charts the execution time (in clock time) that it takes the SQL statement to select all records or the first record. This figure may include time that Adaptive Server spent on processing other tasks or waiting for disk or network I/O to complete.

Times of Improvement

How many times faster the alternative SQL statement is compared to the original SQL.

CPU Time

Charts the execution time (in CPU time) that it takes the SQL statement to select all records or the first record.

Actual I/O Cost

Charts the Actual I/O Cost of executing the SQL statement from Adaptive Server.

Elapsed Time + CPU Time

Charts side by side the Elapsed Time and the CPU Time.

Writes

Charts the accumulative number of disks writes for the SQL statement run time statistics.

Scan Count

Charts the accumulative number of scans for the SQL statement from the run time statistics.

Logical Read

Charts the accumulative number of logical reads for the SQL statement from the run time statistics.

Physical Read

Charts the accumulative number of physical reads for the SQL statement from the run time statistics.

Apf IOs used

Charts the accumulative number of Apf IOs for the SQL statement from the run time statistics.

Other

Charts the following statistics.

Statistics

Description

All Records Charts the Elapsed Time + CPU Time, Actual I/O Cost, and Times of Improvement for all records.

First Records

Charts the Elapsed Time + CPU Time, Actual I/O Cost, and Times of Improvement for the first record.

All Records vs. First Record

Charts the Elapsed Time for all records and the first record.

All Records vs. First Record with Cost

Charts the Estimated I/O Cost, the Elapsed Time + CPU Time (for all records), and Elapsed Time + CPU Time (for the first record).

Print

Print the chart.

Save

Save the selected chart.

Hide Chart

Hide the selected chart.

 

Related Topic

Filter Run Time Results

You can filter the SQL statements displayed on the Time tab and the Statistics tab of the SQL Optimizer window by any of the run time statistics after you have executed the SQL statements so that the run time statistics are displayed on the Time tab and the Statistics tab.

The current filter displays in the bottom left corner of the Time or Statistics pane.

To filter the SQL statements

  1. Click the downward pointing arrow to the right of a column heading.

  2. Select one of the following:

Item Description

All

Displays all the SQL statements.

Custom…

Opens the Custom AutoFilter window where you can select the options for filtering the SQL statements based on specific values for the selected column.

Blanks

Select all the SQL statements which do not have a value in the selected column.

NonBlanks

Select all the SQL statements which do have a value in the selected column.

Specific Value

Select all SQL statements that have one specific value.

  1. You can repeat steps 1 and 2 to add as many filters as you would like.

To clear the filter

Click image\B_ClearFilter.gif at the bottom-left corner of the Time or Statistics pane.

 

Find SQL Using a Text String

The Find SQL function is available in the SQL Optimizer window. This enables the location of SQL statements that contain a specified text string.

To find text in a SQL statement

  1. Select SQL | Find SQL to open the Find SQL window.

  2. Enter the text string to be found and click Find.

  3. To continue searching for the same text, select SQL | Find Next SQL [Ctrl + F3].

 

Related Topic

Generate a Report for Optimized SQL

The details of the SQL statements and run time results in the SQL Optimizer window can be generated into a report.

To generate the Optimized SQL report

  1. Select Report | Optimized SQL to open the Optimized SQL Report Criteria window.

  2. Select the components for the report.

  3. Select All SQL or Select SQL and enter the specific SQL statement numbers.

Note: A few minutes may be needed to generate a long report.

 

Related Topics

Related Documents