About Scanning SQL
Scan SQL helps identify problematic SQL statements in your database environment by automatically extracting SELECT, INSERT, UPDATE, or DELETE statements from your source code. Scan SQL retrieves and analyzes the execution plans for the extracted statements and classifies them according to complexity. You can then optimize statements classified as problematic using Optimize SQL or Batch Optimize SQL.
You can use Scan SQL to extract and analyze statements from the following areas and files:
|
Database Objects |
Database objects with embedded SQL statements which includes stored procedures, triggers, views, rules, and defaults. |
|
Text/Binary Files |
Text and binary files that contain embedded SQL statements. |
|
SQL*Plus Scripts |
SQL*Plus scripts that contain embedded SQL statements. |
|
COBOL Files |
COBOL source code that contains embedded SQL statements. |
|
Inspect SGA Files |
Files with SQL statements from Oracle's System Global Area created in Inspect SGA. |
About SQL Conversion
Scan SQL retrieves execution plans when it extracts SQL statements. If Scan SQL cannot retrieve the execution plan, it attempts to apply a SQL conversion to render the statement as a valid standalone statement. Click the following to display more information for each type of conversion Scan SQL attempts to apply
COBOL ConversionCOBOL conversion searches for three syntax items allowed in COBOL that are not valid SQL syntax. Review the following for additional information:
| Syntax Item | Conversion |
|---|---|
| A dash or minus in a variable name | If a variable name contains a minus sign (-), then it is replaced with an underscore (_). |
| Comments in the middle of a SQL statement | If the seventh column of the line is an asterisk (*), then the complete line is recognized as a line comment. |
| The ]] (double right square bracket) as the concatenate symbol |
If two right brackets (]]) are used to concatenate column names,they are replaced with two vertical bars (||). For example: Original SQL statement SELECT * FROM EMPLOYEE WHERE EMP_ID > :employee-id AND ENAME]]JOB = :name-job
After conversion SELECT * |
|
Notes:
| |
When Scan SQL receives an "Inconsistent datatype" error while retrieving the execution plan for a SQL statement, it checks for a variable performing an operation on a date field. If Scan SQL finds this syntax, it applies the Date Conversion and adds the TO_DATE function to a date variable parameter. For example:
Original SQL Statement
SELECT 1 FROM dual WHERE a – sysdate = 1
After Conversion
SELECT 1 FROM dual WHERE to_date(a) – sysdate = 1
Some source code uses a question mark (?) to define external parameters. Scan SQL adds a number to these external parameters so each parameter in the SQL statement has a unique name. For example:
Original SQL statement
SELECT EMP_ID
FROM EMPLOYEE
WHERE EMP_ID = ?
AND EMP_NAME = ?
After conversion
SELECT EMP_ID
FROM EMPLOYEE
WHERE EMP_ID = ?1
AND EMP_NAME = ?2
Each host variable in Pro*C or similar programming languages can be associated with an optional INDICATOR variable. Scan SQL applies the Indicator conversion for two cases when it encounters this variable.
The first case is when it finds the INDICATOR keyword in an INTO clause. In this case, Scan SQL concatenates the keyword with the variable name. For example:
Original SQL statement
SELECT EMP_ID, EMP_NAME
INTO :v_empid INDICATOR :I_emp_id,
:v_empname INDICATOR :I_empname
FROM EMPLOYEE
WHERE EMP_ID = 100
After conversion
SELECT EMP_ID, EMP_NAME
INTO :v_empid_INDICATOR_:I_emp_id,
:v_empname_INDICATOR_:I_empname
FROM EMPLOYEE
WHERE EMP_ID = 100
The second case is when Scan SQL finds two variables in the INTO clause without a comma separator. In this case, Scan SQL concatenates the two variables together. For example:
Original SQL statement
SELECT EMP_ID, EMP_NAME
INTO :v_empid :I_emp_id,
:v_empname :I_empname
FROM EMPLOYEE
WHERE EMP_ID = 100
After conversion
SELECT EMP_ID, EMP_NAME
INTO :v_empid_:I_emp_id,
:v_empname_:I_empname
FROM EMPLOYEE
WHERE EMP_ID = 100
Scan SQL applies local variable conversion to SQL statements found in the application source code command line that contain one or more local variables replaced by the application. During this conversion, Scan SQL inserts an "&" in front of the variable and removes the concatenated character and quotes around the SQL text. For example:
Original SQL statement
"SELECT " + VEMPID + " FROM EMPLOYEE WHERE EMP_ID > 100"
After conversion
SELECT &VEMPID FROM EMPLOYEE WHERE EMP_ID > 100
Note:Since &[variablename] is not valid SQL syntax, you should replace each local variable in a scanned SQL statement with a value before you optimize the statement. Replacing the variables in the application code allows SQL Scanner to differentiate local variables from host variables.
Scan SQL adds the package header name to a function when it finds a SQL statement that includes the function within a package body. For example:
Package header and body
CREATE OR REPLACE PACKAGE PK_TEST
is
function f_test(aa in number) return number;
function f_test2 return varchar2;
end;
/
CREATE OR REPLACE PACKAGE BODY PK_TEST
is
cursor c_test return t_result is
select 1 from dual;
function f_test(aa in number) return number is
begin
return aa + 10;
end;
function f_test2 return varchar2 is d varchar2(20);
begin
select to_char(sysdate, 'dd-Mon-yyyy hh24:mi:ss')
into d
from dual
where f_test(1) = 11;
return d;
end;
end;
/
Original SQL statement
select to_char(sysdate, 'dd-Mon-yyyy hh24:mi:ss')
into d
from dual
where f_test(1) = 11
After Conversion
select to_char(sysdate, 'dd-Mon-yyyy hh24:mi:ss')
into d
from dual
where "PK_TEST".f_test(1) = 11
PL/SQL conversion simulates the behavior of PL/SQL by adding a /*+CHOOSE*/ hint to the SQL statement when the Oracle optimizer_mode is set to FIRST_ROWS. For example:
Original SQL statement
SELECT EMP_ID
FROM EMPLOYEE
WHERE EMP_ID = 100
After Conversion
SELECT /*+CHOOSE*/EMP_ID
FROM EMPLOYEE
WHERE EMP_ID = 100
Quoted string conversion finds SQL statements in the application source code on the command line. Scan SQL then removes the concatenate character and the quotes surrounding the SQL text.
Original SQL statement
"SELECT * " + "FROM EMPLOYEE WHERE EMP_ID > 100"
After Conversion
SELECT * FROM EMPLOYEE WHERE EMP_ID > 100
Notes:
-
If Scan SQL applies a conversion, it may be necessary to reverse the changes after optimization before you copy an alternative SQL statement back to the original source code.
- You can review the details for conversions Scan SQL applied in the Conversion pane of the SQL Information tab. See View SQL Information (Scan SQL) for more information.
Create Scan SQL Groups
This topic focuses on information that may be unfamiliar to you. It does not include all step and field descriptions.To create a group
-
Click
beside the Group list in the main window.
-
Review the following for additional information:
Group Enter a name for the group. Connection Click
to select an existing connection for the group.
Tip: Click
Tip: Right-click a group and select Delete to delete the group.
Scan SQL
Scan SQL extracts SQL statements embedded in database objects, stored in application source code and binary files, or saved in files created using Inspect SGA. Use the Add Scanner Jobs wizard to create scan jobs using statements from any of these sources.
Note: This topic focuses on information that may be unfamiliar to you. It does not include all step and field descriptions.
To create a scan job
- Select the Scan SQL tab in the main window.
- Click
Tip: Click
-
Click
. The Add Scanner Jobs wizard opens.
-
Complete the wizard to create a scan job. Review the following for additional information:
Page Description Database Objects Page Database objects
Select a schema, database object type, or individual database object, and then click
to add the object.
Tip: Click
Source Code Page Source code type
Select Text/Binary files, Oracle SQL *Plus Script, or COBOL programming source code to indicate the source code type for the file or directory you want to scan. Add by file
Click
and browse to the files you want to add.
Add by directory
Click
Note: Select the Include Sub-directory checkbox to scan sub-directories.
Scan using schema
Click
Inspect SGA Page Group
Select the Inspector group that contains the SQL statements you want to scan. Scan using schema
Click
-
Select a scan job in the Job List pane for more information.
-
Identify problematic SQL statements in the SQL List pane. See View SQL Classification for more information.
-
Select statements to optimize in Optimize SQL or Batch Optimize SQL. See View SQL Text (Scan SQL) for more information.
Tips:
- You can customize a SQL name in the SQL List pane by entering a new name over the auto-generated name.
- Click
in the User Comments column to add a note about the SQL statement.