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

Scan

To prepare a Job for scanning

Mark the Job on the SQL Scanner window by clicking the row to be scanned. The image\I_BlackCheckmark.gif black checkmark prefixes the File / Database Object column. Multiple Jobs can be marked.

To start scanning

Click image\B_Scan.gif.

During scanning, information is updated on the SQL Scanner window. The time required to scan a Job depends on the processor speed of your computer, the file size, the number of valid and invalid SQL statements identified, and the time it takes to retrieve the query plan for each SQL statement.

To abort the scan

Click image\B_AbortScan.gif.

 

Related Topics

Scan Source Code with Temp Tables

During scanning, if the scanned SQL statement is used to create or modify a temp table these SQL statements are automatically executed if the Create Scanner Temp Table option in the Preferences is selected. The created Scanner Temp Tables are dropped after the Job finishes scanning.

For example:

Source Code

select EMP_ID,

EMP_NAME

into #a

from EMPLOYEE

 

select *

from #a

After scanning

SQL1

select EMP_ID,

EMP_NAME

into #a

from EMPLOYEE

SQL2

select *

from #2

The DDL for creating the temporary tables displays under Scanner Temp Table in the bottom pane. This includes the DDL found by the SQL Scanner or the DDL used to create the User-Defined Temp Table.

select EMP_ID,

EMP_NAME

into #a

from EMPLOYEE

 

Related Topics

Review Scanned SQL

The SQL Text pane and the SQL Information pane in SQL Scanner window enables the viewing and analysis of the SQL statements scanned. The following information is provided:

  • Formatted scanned SQL statement

  • Query plan

  • Abstract plan

  • Trace on

  • SQL Information

  • SQL statement type classification: Problematic, Complex, Simple, or Invalid.
  • Database error message if SQL is classified as Invalid.
  • Information about any SQL conversion the SQL Scanner applied to the SQL statement in order for it to generate a query plan.
  • Line and column where the SQL statement was found in the source (for database object and source code files only)
  • SQL statement used to create and populate any temporary tables the SQL Scanner created

  • Checked SQL Information (if the SQL has been marked as checked)

  • Date time of when the SQL statement was checked
  • Name of who checked the SQL
  • Status
  • Checked description are displayed

Note: When the SQL Scanner finds a SQL statement that contains a variable, it will assign the BINARY data type to the variable as it retrieves the execution plan.

To assign a different data type to the variable

  1. Copy the SQL statement to the SQL Editor pane in the SQL Optimizer window.

  2. Click Show Plan image\B_ShowPlan.gif.

  3. Select the data type from the Parameters window which automatically displays when you have a variable in a SQL statement.

  4. Click OK.

 

Related Topic

Find Jobs

In the SQL Scanner window, you can find a contain that contains a specific text string,

To search for a text string in all Jobs

  1. Select Job | Find Job.

  2. Enter the text to be located.

  3. Click Find.

All Jobs and SQL number that satisfy the search criteria are listed.

 

Related Topic

Related Documents