Execution Method (Batch Optimize)
This topic focuses on information that may be unfamiliar to you. It does not include all step and field descriptions.
To set execution method options
-
Click in the main menu.
-
Select Batch Optimize SQL | Execution | Execution Method.
-
Review the following for additional information:
Execution Method |
Description |
Execution options |
See Select an Execution Method for help determining if SQL is static or dynamic.
Select an execution method:
Run on server—Select one of the following methods to execute on the server as either static or dynamic SQL. The SQL is executed on the server without returning the data to the client. The run time statistics provided when you select these options only include CPU time.
-
Run on server - SQL will be executed inside a PL/SQL block as static SQL
-
Run on server - SQL will be executed inside a PL/SQL block as dynamic SQL
Note: You must have SYS.DBMS_SQL package privileges to retrieve run times from the server when using these options.
Run on client—Select the following method to test run on the client. Executes SQL statements and returns the data to the client. The run time statistics provided when you select this option include CPU time and data transfer time between the server and client.
- Run on client - SQL will be executed as dynamic SQL
|
Run Time Retrieval Method |
Description |
Run SQL options |
Select one of the following:
- Run all SQL twice if original SQL runs faster than (seconds)—Combines the Run original SQL twice and all others once and Run all SQL twice using the second run time options into one option and determines (by the number of seconds a SQL statement runs) which option to use. The original SQL statement always runs twice. The SQL alternatives run twice if the original SQL statement runs in less time than the value specified. Otherwise, the SQL alternatives all run once.
- Run original SQL twice using second run time and all others once—Caches data from a table into memory the first time you access it. The next time you access that data, it is already in memory so the following SQL statements run faster. To provide an accurate comparison, the first SQL statement runs twice but only the time from the second run is compared to the times for the other statements.
- Run all SQL twice using the second run time—Executes all SQL statements twice to eliminate factors that can affect the accuracy of the results. If you recently executed a SQL statement, the information for that statement may be cached and the statement may execute faster. Also, if the SQL statement uses different indexes, one index may be cached while another may not be cached. This option eliminates time variation caused by caching since it runs all SQL statements twice but only uses the second run time for comparison.
- Run all SQL once—Executes all SQL statements once. For long running SQL, you do not need to run any statement twice since the effect from caching diminishes over time.
|
Related Topics
Options (Batch Optimize SQL)
Termination Criteria
SQL to Execute
Best Alternative Criteria (Batch Optimize)
Termination Criteria
To set termination criteria options
-
Click in the main menu.
-
Select Batch Optimize SQL | Execution | Termination Criteria.
-
Review the following for additional information:
Terminate execution of SQL alternative if it runs longer than |
Select one of the following:
- Run time of fastest SQL—Cancels SQL statements that run longer than the current fastest run time. With this option, the first SQL statement runs and the time from that statement is used as the termination time for the next SQL statement. When a SQL statement runs faster than this time, the faster time is used as the new termination time.
- Run time of original SQL—Cancels SQL statements that run longer than the run time of the original SQL statement.
- User-defined time (mins/secs)—Cancels SQL statements that run longer than a time you specify.
|
Termination Delay (seconds) |
Adds a specified time to the termination time. It is important to factor a delay into the overall termination time to account for the time needed to send the SQL statement to the database server. |
Related Topics
Options (Batch Optimize SQL)
Execution Method (Batch Optimize)
SQL to Execute
SQL to Execute
This topic focuses on information that may be unfamiliar to you. It does not include all step and field descriptions.
To set options for SQL to execute
-
Click in the main menu.
-
Select Batch Optimize SQL | Execution | SQL to Test Run.
-
Review the following for additional information:
Use Intelligence Engine to execute best representation of SQL Alternatives |
Number of SQL alternatives to select as the representatives |
Enter the number of SQL alternatives to execute.
Default: 10 |
Select Additional SQL Alternatives to Execute |
Execute these additional SQL alternatives |
Select one of the following:
- % of alternatives with lowest cost—Enter the percentage of SQL alternatives to execute with the lowest Oracle Plan cost. You can also enter the minimum and maximum number of alternatives to execute.
- Number of alternatives with lowest cost—Enter the number of SQL alternatives to execute with the lowest Oracle Plan cost.
- All alternatives with cost less than or equal to original SQL—Select to execute all SQL alternatives with an Oracle Plan cost less than or equal to the cost of the original SQL statement.
- All alternatives with cost less than the average of all alternatives—Select to execute all SQL alternatives with an Oracle Plan cost less than the average cost of all alternatives.
- All alternatives with cost less than the original SQL by percentage—Enter a percentage used to determine the SQL alternatives selected for execution. SQL Optimizer executes alternatives with an Oracle Plan cost that is the specified percentage lower than the cost of the original statement.
- All alternatives with cost less than the original SQL by N times—Enter a value for N used to determine the SQL alternatives selected for execution. SQL Optimizer executes alternatives with an Oracle Plan cost N times lower than the original SQL statement.
- All alternatives—Select to execute all SQL alternatives.
|
Related Topics
Options (Batch Optimize SQL)
Execution Method (Batch Optimize)
Termination Criteria
Best Alternative Criteria (Batch Optimize)
Best Alternative Criteria (Batch Optimize)
To set the best alternative criteria option
-
Click in the main menu.
-
Select Batch Optimize SQL | Execution | Best Alternative Criteria.
-
Best alternative selected based on lowest |
Select one of the following :
- Elapsed Time—Uses the total elapsed run time to find the best SQL alternative or best index set.
- First Row Time—Uses the time to retrieve the first record to find the best SQL alternative or best index set.
Default: Elapsed Time |
Related Topics
Options (Batch Optimize SQL)
Execution Method (Batch Optimize)
Termination Criteria
SQL to Execute
Best Alternative Criteria (Batch Optimize)