SQL Optimizer for SAP ASE 3.9 - Installation Guide

SQL Scanner Tutorial

Use the SQL Scanner to analyze SQL statements embedded within database objects, text/binary files, Abstract Plan Groups, SQL Collector for Monitor Server files, SQL Inspector files, and application source code. The SQL Scanner extracts each SQL statement embedded within the scanned database objects and files, retrieves their respective query plans from Adaptive Server, and then performs an analysis that determines which of these SQL statements may be a performance bottleneck. You can copy the SQL statements analyzed as problematic (top priority) or complex (second priority) into the SQL Optimizer, or Index Advisor, and/or examine the extracted SQL statements with their query plans.

Open a Scanner Group

1. Click .

2. When you scan the database objects or the application files, you first create a Group to store the items you want to scan.

3. If this is the first time you have used the SQL Scanner, the Create Group window displays. Otherwise, click Create in the Group Manager window.

4. Enter a new Group name, e.g. "Test." Click OK to close the Create Group window.

5. Check that your new Group name is highlighted in the list field. Click Open.

Add Scanner Jobs

6. The selected group is opened in the SQL Scanner window. For a new Group, the Add Jobs wizard is automatically opened so you can select what files or database objects you want to scan.

Note: If you are using an existing Group, click .

7. In the Add Jobs wizard, select the page for the item that you want to scan. You can select Database Objects, Abstract Plan Groups, SQL Collector for Monitor Server files, SQL Inspector files, and application Source Code in text or binary format. Click Finish.

Scan Jobs

8. Click .

9. Details are filled in the Job List as the scanning process completes each job. It will show you how many SQL statements found in the Job and how each SQL statement is classified.

View scanning results

10. To view the scanned SQL statements, highlight the job by clicking the row.

11. The first SQL statement found is shown in the SQL Text pane. Click the tabs, e.g. SQL1, SQL2, SQL3, etc., at the bottom left of the pane to view the other SQL statements.

12. Notice the buttons on the top of the SQL Information pane. These buttons display in the, the query plan, the abstract plan, Trace On information, the SQL classification and connection information, the DDL for temporary tables used by the SQL, and the Checked SQL information about SQL statement that you are reviewing.

13. You can narrow the number of original SQL statements to view only the problematic and/or complex statements with View | Problematic SQL and/or View | Complex SQL.

14. Select one SQL statement you want to analyze for performance improvement. Click to copy the SQL statement to the SQL Optimizer window and start the optimization process. Alternatively, you can also send the SQL statement to the Index Advisor. Click to copy the SQL statement to the Index Advisor window and generate index options.

Related Topic

SQL Scanner Overview