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

View Open Optimized SQL Details

The Open Optimized SQL Details window displays information about the optimization process and the resulting query plans. The Open Optimized SQL Details window displays after the SQL statements are reloaded into the SQL Optimizer window. You can review the Open Optimized SQL Details window once it has been closed by selecting View | Show Open Optimized SQL Details when the SQL Optimizer window is active.

Note: The Optimized SQL Details cannot be viewed if the SQL Editor pane is the active pane in the SQL Optimizer window.

The Open Optimized SQL Details window has the following tabs:

Summary Tab

The Summary tab provides detailed information about the optimization of the SQL statements and changes that may have occurred in the query plans of the original SQL or alternative SQL statements since the optimization process was done.

Optimization Information

The optimization information includes the date and time of the optimization, how many alternatives were created, the connection information, and the optimization settings from the Preferences window at the time of this optimization.

Last Saved Query Plan Information

The query plan information includes the date and time of the SQL statements that were saved, how many alternatives were created, the connection information, and if any of the query plans have changed since the time they were saved.

During the process of opening the Save Optimized SQL, the current query plan is retrieved and compared to the saved query plan. The changes are noted in the Current column as follows:

Current column item Description

Cost only

The only change to the query plan was a change in the Estimated I/O cost.

Structure and cost

Both the structure and Estimated I/O cost of the query plan changed.

Structure only

The structure of the query plan changed but the Estimated I/O cost remained the same.

No

The current query plan is the same as the query plan that was saved with the SQL statement.

Refresh Plan Button

The Refresh Plan button enables you to replace old query plans with the current plans. This process retrieves the current query plans, deletes any SQL statement that is now invalid, removes any run time information. You can select to eliminate SQL alternatives that now have duplicate query plans. After the query plans are refreshed, the Refresh Plan Details window displays.

Note: The Refresh Plan button is only enabled when there is a difference between the current query plan and the saved plan.

Changes Tab

The Changes tab displays the text of the SQL statement in the top pane. The bottom pane displays the saved query plan and the current query plan side by side for comparison.

User-Defined Temp Table Tab

The User-Defined Temp Table tab is available only if the SQL statements use a temporary table. When the Optimized SQL statements were saved, the DDL for creating the temporary table is saved with them and when the SQL statements are reloaded the DDL displays.

 

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

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

 

Related Topics

Related Documents