To filter a database object
Click in the Database Objects page of the Add Batch Optimize Jobs wizard.
Review the following for additional information:
User | Click and select the database object owner. |
Object Type | Click and select a database object type. |
Filter |
Enter the filtering criteria using the % wildcard and click . Important: The filtering criteria is case sensitive so you must match the uppercase or lowercase characters of the database object name. Notes:
|
Select the database objects to add.
In Batch Optimize SQL, when SQL containing bind variables is extracted, the bind values must be defined before SQL Optimizer can optimize the SQL. If an extracted SQL statement requires a bind value, a prompt displays in the Status column for the statement in the SQL List pane for the batch. A bind value must be provided before the SQL alternatives can be test run and the optimization process can finish.
You can also instruct SQL Optimizer to automatically search for bind values captured by Oracle and (if found) automatically run the SQL.
Troubleshooting: If an "Input bind variables" message displays when you optimize a statement without variables, make sure you spelled the column and table names correctly, you selected the correct database or user, and you selected a table or column that exists in the database.
You can instruct SQL Optimizer to automatically search for the last bind value captured by Oracle. If found, SQL Optimizer will automatically test run the SQL using this value. If a value is not found, you are prompted to enter a value before the optimization process can finish.
To specify this option, go to Options | Batch Optimize SQL | Options. See Options (Batch Optimize SQL) for more information.
You can view the bind values for SQL statements in the Bind Variables pane.
To view the bind values used in SQL
If the Wait for me to input the bind values before test run option is selected in the Options dialog or if SQL Optimizer is unable to find a bind value in Oracle, you must manually enter a value. Review the following procedure.
To set a bind variable
Select the job from the batch node with the SQL statement containing the bind variable.
Click the Input bind variables message in the SQL Status column of the SQL List pane and select Input Bind Variables.
Specify a bind value and data type in the Setting Bind Variables dialog. To browse data for a value, use the Data Browser pane. To find values captured by Oracle, click Auto Fill. Review the following for additional information:
Bind Variables Form | |
Datatype |
Click and select the variable datatype. |
Variable Value |
Enter a value for the variable. Leave the field blank to specify NULL. |
Data Browser | |
SELECT |
Click and select column references for the variables. |
FROM |
Click and select table references for the variables. Tip: Click to browse to tables in a different database. |
WHERE |
Enter a WHERE clause or click to select a previously entered clause. |
ORDER BY |
Enter an ORDER BY clause or click to select a previously entered clause. |
Auto Fill |
Click the Auto Fill button to find the last bind values captured by Oracle. |
To change schema
Click the entry in the Schema column in the Job List pane and select a schema.
Tips:
Click the entry in the Execution Schema column and select a schema to change the execution schema.
Select a job from the batch node to change the schema for an individual SQL statement.
Important: Batch Optimize SQL deletes SQL statements extracted from scanned jobs when you change the schema.
To manage a batch
Select the Batch Optimize SQL tab in the main window.
Select the Batch List node in the Batch Job List pane.
Tip: Right-click a Batch and select Add Jobs | option to add scan jobs to the selected batch.
Review the information displayed for each batch in the Batch List pane.
Tips:
Click the arrow beside and select an option to pause the batch optimization process.
Select a batch to display a chart of optimization results in the Jobs Improved pane.
© 2024 Quest Software Inc. ALL RIGHTS RESERVED. 使用条款 隐私 Cookie Preference Center