SQL Optimizer for SAP ASE 3.8

Button or Menu	Function
Collector & Right-click Menu $image\B_AddCollector.gif$	Add Collector
Collector & Right-click Menu $image\B_ModifyCollector.gif$	Modify Collector
Monitor Menu $image\B_Monitor.gif$ $image\B_AbortMonitor.gif$	Monitor/Abort Monitor
Right-click Menu	Add/Remove Bookmark
Right-click Menu	Ad-hoc Monitor
Right-click Menu	Delete (Collector)
Right-click Menu	Scan
Right-click Menu	Properties
Right-click Menu	Save
$image\B_FirstSQL.gif$ $image\B_PreviousSQL.gif$ $image\B_NextSQL.gif$ $image\B_LastSQL.gif$	First SQL / Previous SQL / Next SQL / Last SQL
SQL Menu	Find SQL
SQL Menu	Find Next SQL
Edit Menu $image\B_SendToSQLOptimizer.gif$	Send to SQL Optimizer
Edit Menu $image\B_CopytoIndexAdvisor.gif$	Send to Index Advisor
Edit Menu $image\B_CopyToSQLWorksheet.gif$	Copy to SQL Worksheet
File Menu	Save SQL to SQL Repository
View Menu	All SQL
View Menu	Simple SQL
View Menu	Complex SQL
View Menu	Problematic SQL
View Menu	SQL without Plan
Report Menu	Monitored SQL

SQL Scanner

SQL Scanner Overview

SQL Scanner > SQL Scanner Overview

The SQL Scanner is a unique module which extracts SQL statements embedded in applications, database objects or files without any execution of programs. It retrieves and analyzes in batch the query plans for the extracted SQL to categorize the SQL statements according to the complexity of the query plan and suspect levels of performance problems. The SQL Scanner allows you to quickly review SQL in existing code and detect potential SQL performance problems. With this approach, the SQL Scanner allows you to be proactive in the detection of performance problems. The task of extracting, reviewing and analyzing many SQL statements is simplified and automated with the SQL Scanner.

Typically database applications contain thousands of SQL statements. Without the SQL Scanner, you have to extract and review each SQL statement manually. This a very tedious and time consuming task. Once these SQL statements have been extracted, you need to manually analyze each SQL statement’s query plan to see if the query plan represents a potential performance problem. The SQL Scanner does this task for you.

Once you have identified potentially problematic SQL statements you can send the SQL to the SQL Optimizer, Index Advisor, the SQL Worksheet, or save them to the SQL Repository.

SQL Scanner Window

SQL Scanner > SQL Scanner Window

View SQL Scanner Window

The SQL Scanner window has 3 panes, a status bar and a toolbar.

Job List Pane

The grid from the SQL Scanner window stores information on each individual Job in the Group. It displays the following:

Column Heading		Description
File / Database Object	Checkbox	Indicates if the job is selected
	Type of job icon and text	$image\I_DatabaseObject.gif$ [Database] [User] Object type -> Object type
		$image\I_Text-Binary.gif$ [Database] [User] Text/Binary -> Path/File Name
		$image\I_COBOL.gif$ [Database] [User] COBOL -> Path/COBOL File Name
		$image\I_AbstractPlan.gif$ [Database] [User] Group -> [Database] [User] APGroup -> Abstract Plan Group
		$image\I_Monitor.gif$ Collector -> Collector Name
		$image\I_Inspector_blue.gif$ Inspector -> Inspector Name
Status		Displays the current Job status. This column is blank until the Job is scanned. After the Job is scanned it displays the number of SQL statements found.
Valid SQL		Displays the number of valid SQL statements found in the Job. Valid SQL statements are syntactically correct statements recognized by the SQL Scanner, and for which Adaptive Server can provide a query plan. Valid SQL statements are further classified as Simple, Complex and Problematic SQL statements.
Problematic SQL		Displays the number of Problematic SQL statements found. If the Show Checked SQL figures on the SQL Scanner window option is selected within Preferences you will notice that there are two figures, first one indicates the number of Problematic SQL that have not been checked and the other (enclosed in brackets) is the number of Problematic SQL that have been checked. The total of these two figures represents the total number of Problematic SQL statements.
Complex SQL		Displays the number of Complex SQL statements found. If the Show Checked SQL figures on the SQL Scanner window option is selected within Preferences you will notice that there are two figures, first one indicates the number of Complex SQL that have not been checked and the other (enclosed in brackets) is the number of Complex SQL that have been checked. The total of these two figures represents the total number of Complex SQL statements.
Simple SQL		Displays the number of Simple SQL statements found. If the Show Checked SQL figures on the SQL Scanner window option is selected within Preferences you will notice that there are two figures, first one indicates the number of Simple SQL that have not been checked and the other (enclosed in brackets) is the number of Simple SQL that have been checked. The total of these two figures represents the total number of Simple SQL statements.
File Size		Displays the size of the Job in bytes.
Started At		Displays the date and time when the scanning the Job began.
Processing Time		Displays the total time taken to scan the Job. The time displays in the HH24:MI:SS format.

SQL Text Pane

The left pane of the SQL Scanner window displays the scanned SQL statement for a particular scan Job. The SQL statement is formatted according to SQL Formatter’s indentation algorithm. If more than one SQL statement is found in a Job, there are multiple tabs at the bottom of this pane for selecting the SQL. The tabs are color-coded to the SQL type classification if the Use color tabs for SQL classification option is selected in the Preferences window. Problematic SQL is red. Complex SQL is purple. Simple SQL is green. Invalid SQL is blue. Checked SQL is grey.

SQL Information Pane

The SQL Information Pane provides detailed information about the SQL statement.

Status Bar

The Status Bar at the bottom of the SQL Scanner window contents the following information:

Item	Description
Data Directory	Directory path where all the data files produced during scanning are saved. The data directory path can be changed in the Preferences window. While scanning, a progress bar displays to show scanning progress. The upper bar shows the current job progress, while the lower bar shows the total Job status.
Group	Name of the currently opened Scanner Group.
Total Jobs	Total number of Jobs.
Completed	Total number of Jobs already scanned.
Remaining	Total number of Jobs that have not been scanned.

Please select your product:

To serve you better, please complete the Purpose of your Chat:

Recommended Solutions for Your Problem

SQL Optimizer for SAP ASE 3.8 - User Guide

SQL Collector Functions