Abstract Plan (SQL Scanner Tab)
The Abstract Plan page on the SQL Scanner tab of the Preferences window allows you to include the retrieval of the abstract plan for the SQL statements. This requires Adaptive Server 15 or later.
Dump abstract plan (Default = cleared)
Specify whether to retrieve the abstract plan for the SQL statement whenever the query plan is retrieved. The abstract plan displays in the SQL Scanner window. The abstract plan is not saved on the database.
When this checkbox is selected, the selected abstract plan icon 
Note: For third-party package applications, you are able to optimize SQL statements without changing the source code by saving the abstract plan.
Group name (Default = blank)
Specify the abstract plan group name where the abstract plans are saved. The default groups names in Adaptive Server are: ap_stdout and ap_stdin. These groups are usually used by the Database Administrator to enable server-wide abstract plan capturing and retrieving.
ap_stdout is used by default to capture an abstract plan.
ap_stdin is used by default to retrieve the abstract plan associated with a SQL statement during the execution of the SQL statement.
Abstract Plan Manager button
Opens the Abstract Plan Manager window to view, create, and modify abstract plan group.
General (SQL Scanner Tab)
View SQL Scanner - General page)
Interface
Use color tabs for SQL classification (Default = checked)
When checked, the SQL statements are displayed with color-coded tabs representing the SQL's classification. Red for Problematic, purple for Complex, green for Simple, and blue for Invalid. If the SQL statement is checked, the tab is grey.
Show Checked SQL figures on the SQL Scanner window (Default = cleared)
When checked, an additional column of numbers is added to the SQL classification columns to indicate how many SQL statements have been checked for each Job. When a SQL statement is added to the Checked SQL list, it has a value in the appropriate SQL classification column in the SQL Scanner window.
Checked List
Action when performing Send to SQL Optimizer and Send to Index Advisor
Prompt user for action
If selected, the user is prompted to be added to the Checked List.
If selected, all SQL sent is added to the Checked List.
If selected, all SQL sent is not automatically added to the Checked List.
Do not remove Check SQL information when rescanning (Default = cleared)
Specify to keep the checked SQL information when the Job is rescanned. If unchecked, the checked SQL information is removed when the Job is scanned again.
Compare the current SQL with the rescanned SQL using
SQL text (Default)
Specify to match the SQL statements from the previous time the Job was scanned with the current scanning using only the SQL text as a comparison for the match. The checked SQL information is preserved for those SQL statements where the SQL text matches.
SQL text and query plan
Specify to match the SQL statements from the previous time the Job was scanned with the current scanning using the SQL text and the query plan as a comparison for the match. The checked SQL information is only preserved for those SQL statements where the query plan and the SQL text match.
SQL Classification Tab
SQL Classification
Based on the query plan, a SQL statement is classified according to the characteristics of the query plan. If the query plan is not retrieved successfully, then these SQL statements are classified as Invalid SQL. A SQL statement can be invalid if the database object it references does not exist, the database user does not have privileges to access it, or the incorrect database and user are used to scan the Job.
All valid SQL statements are further classified as Simple, Complex, or Problematic SQL statements.
Problematic SQL Statements
Problematic SQL statements are potentially under performing SQL statements that should be optimized. Problematic SQL satisfy one of the following criteria:
-
The number of tables referenced in the query plan of a SQL statement exceeds the upper limit of the Complex table scan operations range.
-
Single full table scan with table size larger than the threshold size.
-
Single full table scan in a nested loop with table size larger than the threshold size.
-
The number of worktables is greater than or equal to the defined value if the With Worktable checkbox is selected in the Preferences window.
-
The number of reformatting.
Complex SQL Statement
Complex SQL statements are complicated SQL statements where there is room for improvement. Complex SQL satisfy one of the following criteria:
-
If the number of tables referenced in the query plan of an SQL statement falls into the Complex table scan operations range defined in the Preferences window.
-
SQL statements with full index scan.
Simple SQL Statement
Simple SQL statements are direct and straightforward SQL statements with minimal probability of improvement. SQL statements are defined as Simple SQL statements when number of tables referenced in the query plan is less than the lower limit of the Complex table scan operations range; the default value is 1 table.