SQL Optimizer for SAP ASE 3.8

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:

DUMP TRANSACTION database_name WITH TRUNCATE_ONLY

go

Selected SQL

SQL Optimizer > Optimized SQL > Batch Run > Selected SQL

View Batch Run Criteria Window - 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.