Tchater maintenant avec le support
Tchattez avec un ingénieur du support

SQL Optimizer for SAP ASE 3.8 - 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 Time and Statistics Results

SQL Optimizer > Optimized SQL > Analyze Time and Statistics Results

The results from the Run for All Records, the Run for First Record, and the Batch Run are placed in the bottom Pane of the SQL Optimizer window for you to analyze and pick the SQL statement that is the best performing in your database environment. These results are displayed in the Time, Statistics, and Charts tab.

 

Analyze Run Time Results

SQL Optimizer > Optimized SQL > Analyze Run Time Results

Scenario Name

The Optimization process numbers the SQL alternatives according to the Estimated I/O cost.

Estimated I/O cost

This is a cost estimation calculated by Adaptive Server. It is only an estimation and should not be used to determine the best performing SQL.

AP Compatibility

This shows the SQL statements that have abstract plans that are compatible with the original SQL statement.

Elapsed Time (All Records and First Record)

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. This is the accumulative run time for the SQL statement. The individual values for the Elapsed Time can be view by clicking All Records or First Record on the top right pane of the SQL Optimizer window.

Times of Improvement (All Records and First Record)

If the original SQL statement run time is available, the Times of Improvement indicates how much faster the alternative SQL is than the original SQL.

CPU Time (All Records and First Record)

The CPU Time is accumulative total for the CPU time to execute the SQL statement. The individual values for the CPU time can be view by clicking All Records or First Record on the top right pane of the SQL Optimizer window.

Actual I/O Cost (All Records and First Record)

The Actual I/O Cost is a calculation from Adapter Server after the SQL statement is executed in the Batch Run or the Run Time function.

Records (All Records and First Record)

The Records column shows the number of records processed by the SQL statement. This number should be constant. It is an indication that the alternative SQL statements are semantically equivalent to the original SQL statement.

Remarks

Information from the Batch Run is included in this column. It includes:

  • If the SQL was terminated by the termination criteria.

  • If the SQL was run more than once.

  • If a database error occurred.

Note: Estimated I/O cost, All Records Actual I/O Cost and First Record Actual I/O Cost are not available for logon users without sa_role privileges or with the "allow resource limits" configuration parameter turned off for the Adaptive Server. To retrieve the Estimated I/O Cost, All Records Actual I/O Cost, and First Record Actual I/O Cost estimations, you should either grant yourself sa_role, logon as another user with sa_role privileges, or turn on the "allow resource limits" parameter:

sp_configure "allow resource limits", 1

 

Related Topic

Analyze Statistics Results

SQL Optimizer > Optimized SQL > Analyze Statistics Results

The Statistics tab of the Run Time pane in the SQL Optimizer window shows the statistics the accumulative totals for the CPU Time, Scan Count, Logical Reads, Physical Reads, and APF I/Os from the execution of the SQL statement. To see the individual values for the statistics by table, click All Records or First Record on the top right pane or the Statistics tab at the bottom of the SQL Optimizer window.

For more information about this statistics see the Adaptive Server Performance Tuning: Monitor and Analyzing manual, Chapter 4.

Statistics

(All Records and First Record)

Description

Elapsed Time

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. This is the accumulative run time for the SQL statement. The individual values for the Elapsed Time can be view by clicking All Records or First Record on the top right pane of the SQL Optimizer window.

CPU Time

The CPU Time is accumulative total for the CPU time to execute the SQL statement. The individual values for the CPU time can be view by clicking All Records or First Record on the top right pane of the SQL Optimizer window.

Actual I/O Cost

This Actual I/O Cost is a calculate combining the Logical and Physical I/O statistics after it was executed in the Batch Run or the Run Time function.

Writes

The Writes is the total number of buffers written to the disk.

Scan Count

The Scan Count represents the number of times a query accessed a particular table.

Logical Reads

The Logical Reads represents the accumulative total for logical read for each table and index used in the SQL statement.

Physical Reads

The Physical Reads represents the accumulative total for logical read for each table and index used in the SQL statement. 

APF IOs used

The APF (Asynchronous-PreFetch) IOs used is the I/O the server does in advance anticipating which pages will need to be read next, so that the pages will be in cache when the process actually tries to access them.

 

Related Topic

Analyze Chart Results

SQL Optimizer > Optimized SQL > 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

Documents connexes

The document was helpful.

Sélectionner une évaluation

I easily found the information I needed.

Sélectionner une évaluation