Chat now with support
Chat with 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

Refresh Plan Details Window

SQL Optimizer > Optimized SQL > Save Optimized SQL > Refresh Plan Details Window

The Refresh Plan Details window tells how many SQL statements were refreshed and it any of the SQL alternatives were eliminated due to duplicate query plans or invalid SQL statements. It also displays the SQL for Cursor setting that was used and tells whether a temporary table was used.

This window is only available after the saved query plans were replaced with the current query plans by clicking Refresh Plan on the Open Optimized SQL Details window.


Batch Run

Retrieve Run Time for a Group of SQL

SQL Optimizer > Optimized SQL > Batch Run > Retrieve Run Time for a Group of SQL

The Batch Run function is used to retrieve the run time of a group of optimized SQL statements.

To start the Batch Run

Click image\B_BatchRun.gif to open the Batch Run Criteria window.

The Batch Run Criteria window is divided into the following tabs:

Select your batch run criteria and click OK. The Batch Run window displays the run time criteria and the run time of the SQL statements as it is retrieved. SQL | Stop Current and SQL | Abort Batch Run functions are available to terminate the currently running SQL statement and stop the batch run process. Each selected SQL statement is execute sequentially retrieving the run time unless terminated.

To terminate the currently running of the SQL statement

Select SQL | Stop Current.

To terminate the batch run process

Click image\B_AbortBatchRun.gif.

Note: For UPDATE, INSERT, and DELETE SQL statements, while retrieving the run time and run result you may encounter the following Adaptive Server error message:

allocate space for object 'syslogs' in database 'sqlexp' because the 'logsegment' segment is full. If you ran out of space in syslogs, dump the transaction log. Otherwise, use ALTER DATABASE or sp_extendsegment to increase the size of the segment  

This is due to the lack of space in system table (syslogs) in which all changes to the database are recorded. Empty the transaction log in the database and re-execute. Use the following command in the SQL Workshop module:




Related Topics

Selected SQL

SQL Optimizer > Optimized SQL > Batch Run > Selected SQL

The Selected SQL tab is used to select or unselect SQL statements to be executed. All SQL statements are selected by default. The selected SQL statements are displayed in blue. You can select or de-select SQL statements you want to execute by clicking a row. The image\I_BlueCheckmark.gif blue checkmark at the left of the row indicates the SQL statement is selected.

Selecting or unselecting all SQL statements

To select or unselect all the SQL statements

  • Right-click and select Unselect All or Select All.

The original SQL can be de-selected from the list only if the Original SQL checkbox is not selected on the SQL Termination and Batch Termination tab and the Always run Original SQL first option in the Selected Index tab is not selected.

Changing the order the SQL statements are executed

The SQL statements are ranked by Est. I/O Cost by default, with the exception for the original SQL which is placed at the top. You can sort either the Est. I/O column or the SQL column by clicking the column heading.

You can change the order of any SQL statement, by clicking the row and then click image\B_MoveUp.gif or image\B_MoveDown.gif.

Always run Original SQL first

Specify to always run the original SQL statement first despite sort order of the Est. I/O cost. This checkbox is dimmed if the Original SQL option is selected in the Termination Criteria or Batch Termination tab of the Batch Run Criteria window.

Apply SQL selection filter

The SQL selection filter unselects the SQL statements that have an Estimated I/O cost greater that a specified value. When you select this option, image\B_SQLSelectionFilter.gif is enabled at the bottom right of the window.


Related Topic

Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating