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

Run Time Tab Page

Index Advisor > Run Time Tab Page

Run Time Tab

The Run Time tab in the Index Advisor window displays the run time information for the SQL statement using the alternative indexes. The values are filled in after the Show Plan, Batch Run, Run for First Record, or Run for All Records functions are executed.

For Index candidates proposed by the Index Advisor, an icon is added in the far left column to indicate if the proposed index is used in the query plan. A key with a green checkmark image\I_Key_PlanChangedByIndexes.gif indicates that the query plan is changed by the proposed index. A key with a red exclamation mark image\I_Key_PlanChangedIndexNotApplied.gif indicates that the key plan changed, but that the proposed index was not used by the new query plan. A key with a red x image\I_Key_PlanNoChange.gif indicates that the query plan is not changed by the addition of the proposed index.

Item Description

Scenario Name

The index advising process numbers the SQL alternatives.

Estimated I/O cost

This is a cost estimation calculated by Adaptive Server for the query plan.

Actual I/O Cost

(All Records and First Record)  

This is the Actual I/O Cost of the SQL statement after it was executed in the Batch Run or the Run Time functions.

Elapsed Time

(All Records and First Record)  

This is the run time calculated from the clock start and finish times for the SQL statement.

Times of Improvement

(All Records and First Record)  

If the original SQL statement run time is available, this indicates how much faster the SQL statement is under the alternative index(es) than it is under the original index(es).

Records

(All Records and First Record)  

This is the number of records processed by the SQL statement.

Remarks

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

  1. If the SQL was terminated by the termination criteria.

  2. If the SQL was run more than once.

  3. If a database error occurred.

Privileges

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 Topics

Statistics Tab Page

Index Advisor > Statistics Tab Page

Statistics Tab

The Statistics tab of the Run Time pane in the Index Advisor window shows the accumulative totals for the CPU Time, Scan Count, Logical Reads, Physical Reads, and Read-Ahead Reads from the execution of the SQL statement with each index alternative. 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 Index Advisor window.

For Index candidates proposed by the Index Advisor, an icon is added in the far left column to indicate if the proposed index is used in the query plan. A key with a green checkmark image\I_Key_PlanChangedByIndexes.gif indicates that the query plan is changed by the proposed index. A key with a red exclamation mark image\I_Key_PlanChangedIndexNotApplied.gif indicates that the query plan changed, but that the proposed index was not used by the new query plan. A key with a red x image\I_Key_PlanNoChange.gif indicates that the query plan is not changed by the addition of the proposed index. In this case, the SQL statement is not executed by the by the Batch Run.

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

Item Description

Elapsed Time

(All Records and First Record)  

The Elapsed Time is the 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

(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)  

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

Writes

(All Records and First Record)  

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

Scan Count

(All Records and First Record)  

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

Logical Reads

(All Records and First Record)  

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

Physical Reads

(All Records and First Record)  

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

APF IOs used

(All Records and First Record)  

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 Topics

Chart Tab Page

Index Advisor > Chart Tab Page

Chart Tab

Charts are available for all the statistics and run times. The Charts tab of the Run Time pane in the Index Advisor window displays two chart panes showing the run time information and statistics.

To change the information in the chart

Click the Chart Title and select one of the following functions:

Function Description
Performance Projection A performance forecast based on the retrieved Estimated I/O Cost value. If the Performance Projection value is greater then 1, then it is forecasted that the recommended Index candidate 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

 

Elapsed Time

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

Times of Improvement

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

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 disk writes for the SQL statement from the run time statistics.

Scan Count

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

Logical Reads

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

Physical Reads

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 (Asynchronous-PreFetch I/Os) for the SQL statement from the run time statistics.

Other

All Records

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

First Record

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

 

Prints the chart.

Save

 

Saves the selected chart.

Hide Chart

 

Hides the selected chart.

 

Related Topics

Filter the Run Time Results

Index Advisor > Filter the Run Time Results

You can filter the Index Sets displayed on the Time tab and the Statistics tab of the Index Advisor window by any of the run time statistics after you have executed the SQL statement with the Index Sets.

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

To filter the Index Sets

  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.

 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.

 

Related Topics

Documents connexes

The document was helpful.

Sélectionner une évaluation

I easily found the information I needed.

Sélectionner une évaluation