Manage Scan Jobs
SQL Scanner displays scan jobs you create using the Add Scanner Jobs wizard in the Job List pane. You can use the Job List pane to manage scan jobs in a selected group.
Note: This topic focuses on information that may be unfamiliar to you. It does not include all step and field descriptions.
To start a scan job
- Select the Scan SQL tab in the main window.
-
Create a scan job using the Add Scanner Jobs wizard. See Scan SQL for more information.
-
Select a group in the Scanner node of the Task pane to view a scan job.
Tip: Select a scan job in the Job List pane to view job properties in the Job Detail Information pane.
-
Right-click a scan job and select Scan to start the scan job.
-
You can change the database by clicking the entry in the Set Database column and selecting a different database before starting the scan job.
-
You can change the user credentials by clicking the entry in the Set User column and selecting different user credentials before starting the scan job.
-
Select the Eliminate Duplicate SQL checkbox to eliminate duplicate SQL statements while scanning.
-
Click
to stop all scan jobs in the selected group.
Tips:
Note: Right-click a scan job and select Delete to delete the job.
Create Benchmark Factory Import Files
You can create an import file from SQL statements extracted by SQL Scanner for import into Benchmark Factory if it is installed on the client computer.
To create an import file
-
Select the Scan SQL tab in the main window.
-
Create a scan job using the Add Scanner Jobs wizard. See Scan SQL for more information.
-
Select a group in the Scanner node in the Task pane.
-
Select the checkboxes for the jobs you want to include in the Job List pane.
Note: This is only required if you want to include more than one job but not all jobs in the group.
-
Right-click a job and select Create Benchmark Factory Import File.
View SQL Classification
SQL Scanner extracts syntactically correct SQL statements and retrieves their execution plans. SQL Scanner then classifies the extracted statements as Simple, Complex, Problematic, or Invalid. You can view the classification of extracted statements by scan jobs or individually.
To see descriptions of the classification categories, see SQL Classification Categories.
To view SQL classification for a scan job
- Select the Scan SQL tab in the main window.
-
Create a scan job using the Add Scanner Jobs wizard. See Scan SQL for more information.
-
Select a group in the Scanner node in the Task pane.
-
Select a scan job in the Job List pane to view classification information in the SQL Classification pane. Review the following for additional information:
Classification Description Simple SQL statements are classified as simple when the number of tables referenced in the execution plan is less than the lower limit of the complex table scan operations range. Complex SQL statements are classified as complex when the number of tables referenced in the execution plan exceeds the lower limit of the complex table scan operations range. Problematic SQL statements are classified as problematic when SQL Optimizer determines they can be optimized. Problematic SQL statements satisfy one or more of the following criteria:
- The number of tables referenced in the execution plan exceeds the upper limit of the complex table scan operations range.
- A full table scan executed in a nested loop exceeds the user-defined threshold table size.
- A single table scan executed exceeds the user-defined threshold table size.
Invalid SQL statements are classified as invalid for one of the following reasons:
- The current user does not have privileges to use the tables, views, or other database objects referenced in the SQL statement.
- The database and/or user do not match the database and user (object owner) for the SQL statement.
- The SQL statement is dynamically constructed but is not a Single Command Line Dynamic (SCLD) SQL statement. SQL Optimizer can only identify SCLD SQL statements.
- In Scan SQL, the SQL statement uses a temporary table that was not created before starting the Scan SQL session. See Use Temporary Tables in Scan SQL for more information.
Note: You can specify SQL classification rules in the Options dialog. See SQL Classification Options for more information.
To view SQL classification for individual statements
-
Select a group in the Scanner node in the Task pane.
-
Select a scan job from the Group node.
-
Review the list of SQL in the SQL List pane.
Tips:
- Click
to group the SQL by classification category.
- Click
to list the SQL numerically.
- Click
- Select a SQL statement in the list to view SQL text.
SQL Classification Categories
SQL Optimizer classifies the SQL statements extracted in Scan SQL and in Find SQL, as well as the SQL you enter in Optimize SQL. The SQL are classified into one of the following categories: Simple, Complex, Problematic, or Invalid.
For a better understanding of each of these categories, review the following category descriptions.
| Classification | Description |
|---|---|
| Simple | SQL statements are classified as simple when the number of tables referenced in the execution plan is less than the lower limit of the complex table scan operations range. |
| Complex | SQL statements are classified as complex when the number of tables referenced in the execution plan exceeds the lower limit of the complex table scan operations range. |
| Problematic |
SQL statements are classified as problematic when SQL Optimizer determines they can be optimized. Problematic SQL statements satisfy one or more of the following criteria:
|
| Invalid |
SQL statements are classified as invalid for one of the following reasons:
|
|
Note: You can specify SQL classification rules in the Options dialog. See SQL Classification Options for more information. | |