-
Title
How to scan and optimize SQL statements in procedures, packages, func -
Description
Suggestions on how to scan for SQL statements inside PL/SQL codes like procedures, packages, functions.
-
Resolution
Go to Batch Optimize Tab | right-click "New Batch 1" | Add Jobs | Database Objects | add procedures with SQL in it | go through and finish the wizard | click the added database object | and SQL Optimizer will be able to scan the SQL statements.
You can also choose Batch Optimize Tab | right-click "New Batch 1" | Source Code | this will allow you to add Text/Binary files, Oracle SQL Plus scripts, or COBOL programming source code.
Also try Scan SQL Tab | add a new Group | click ‘Add Scanner Jobs’ icon | the wizard will allow you to add database objects like procedures, functions, etc from your database | and it will scan for SQL statements in it. However, there where no option to add TXT or SQL files.
-
Additional Information
If using Toad for Oracle Xpert, then launch SQL Optimizer using Database | Optimize | Advanced SQL Optimization