Chat now with support
Chat with Support

SQL Optimizer for SAP ASE 3.9.1 - Release Notes

SQL Information Pane

The SQL Information pane displays in several modules. It provides a variety of information about the SQL statement like the query plan, abstract plan, trace on and run time information. The specific information is dependent upon the function of the module.

The SQL Information pane displays SQL information according to the button you select from the button bar image\BB_SQLInformation--Sybase.gif at the top of this pane. The exact buttons that you find at the top of this pane varies from one module to the next. These buttons include: query plan, abstract plan, trace on, other information (SQL classification, connection information, warning messages, etc), all records statistics, first record statistics, Scanner temp table and checked details.

Query Plan

Displays the SQL query plan. When two SQL Information panes are displayed side-by-side, as in the SQL Comparer module and the Analyzer modules, the differences between the operations are highlighted in green.

Abstract Plan

The Abstract Plan button is only available for Adaptive Server 15 or later and you have selected Dump abstract plan option in the Preferences window.

Displays the abstract plan which describes the query plan using a language created for that purpose. This language contains operators to specify the choices and actions that can be generated by the optimizer.

Trace On

The Trace On button is only available if you have selected dbcc traceon (3604, 302, 310) option in the Preferences window and have sa_role privilege.

Trace on shows the reason why the Adaptive Server optimizer chose a particular way of executing the original SQL statement, displaying the reasons for index and table joins selection.

Information

The exact information depends on the module. It always displays the SQL classification type. This classification is dependent on the parameters set in the Preferences window to identify whether the original SQL statement is potentially Problematic. It may also display:

  • Warning or alert information about the SQL statement.

  • SQL statement type classification: Problematic, Complex, Simple, or Invalid.

  • Database error message if SQL is classified as Invalid.

  • SQL conversion applied, if conversions have been added to the scanned SQL text in order for it to generate a query plan, this information is also displayed.

  • Start position of the SQL statement (SQL Scanner only, for database object, TXT and SQL files).

  • Connection information (Login name, Server name, Database and User)

  • Database Settings from the Preferences window.

  • Session settings for the optimization timeout limit and optimization goals. (Available in Adaptive Server 15 or later) The optimization timeout limit is an Adaptive Server configuration parameter that specifies the amount of time Adaptive Server can spend optimizing a query as a fraction of the total time spent processing the query. The optimization goals are selected by you in the Preferences settings.

All Records

Displays the run time information that is collected when retrieving all records of the query.

First Record

Displays the run time information that is collected when retrieving the first record of the query.

Both All Records and First Record buttons display the run time statistical information:

  • The amount of disk activities needed to execute the SQL statement.

    Number of scans performed

    Number of pages read from the data cache

    Number of pages placed into the cache

    Number of pages read from disk

 Number of scans performed

  • Number of milliseconds required to parse, compile, and execute each SQL statement.

  • SQL script used to test run the SQL statement.

Scanner Temp Table (SQL Scanner)

Displays the temporary table SQL statement assumed to create or modify the temporary table used on the scanned SQL statement if the SQL Scanner finds it in the source.

Checked Details (SQL Scanner)

If the selected SQL statement is checked, then this page displays the date time of when the SQL statement was checked, the name of person who checked the SQL, the status, and the checked description.

Index Advisor Window (Index Advisor)

Displays information about how the index will be created and the estimated amount of space required for the index.

 

Related Documents