Chat now with support
Chat with Support

SQL Optimizer for Oracle 9.3.3 - User Guide

Welcome to SQL Optimizer
About SQL Optimizer SQL Optimization Workflow New in This Release Additional Resources Database Privileges Database Privileges Script Connect to the Database Windows Layout Customize Toolbars Keyboard Shortcuts Support Bundle Register SQL Optimizer Check for Updates SQL Operations
ALL PARTITION ALTER INDEX AND EQUAL ANTI JOIN BITMAP AND BITMAP COMPACTION BITMAP CONSTRUCTION BITMAP CONVERSION BITMAP INDEX BITMAP JOIN INDEX UPDATE BITMAP JOIN INDEX UPDATE STATEMENT BITMAP KEY ITERATION BITMAP MERGE BITMAP MINUS BITMAP OR BUFFER SORT CARTESIAN JOIN COLLECTION ITERATOR CONCATENATION CONNECT BY CONNECT BY PUMP COUNT COUNT STOPKEY CREATE AS SELECT CUBE SCAN DDL STATEMENT DELETE DOMAIN INDEX FAST FULL INDEX SCAN FILTER FIRST ROWS FIXED INDEX FIXED TABLE FOR UPDATE FULL INDEX SCAN FULL INDEX SCAN DESCENDING FULL INDEX SCAN (MIN/MAX) HASH GROUP BY HASH GROUP BY PIVOT HASH JOIN HASH JOIN BUFFERED HASH PARTITION HASH UNIQUE INDEX INDEX BUILD NON UNIQUE INDEX RANGE SCAN INDEX RANGE SCAN DESCENDING INDEX RANGE SCAN (MIN/MAX) INDEX SAMPLE FAST FULL SCAN INDEX SKIP SCAN INDEX SKIP SCAN DESCENDING INDEX UNIQUE SCAN INLIST ITERATOR INLIST PARTITION INSERT INTERSECTION INTO INVALID PARTITION ITERATOR PARTITION LOAD AS SELECT MAT_VIEW ACCESS MAT_VIEW REWRITE ACCESS MERGE JOIN MINUS MULTI-TABLE INSERT NESTED LOOPS OUTER JOIN PARTITION PARTITION HASH EMPTY PARTITION LIST PARTITION RANGE PROJECTION PX BLOCK ITERATOR PX COORDINATOR PX ITERATOR PX PARTITION PX PARTITION HASH ALL PX PARTITION LIST ALL PX PARTITION RANGE ALL PX RECEIVE PX SEND RANGE PARTITION RECURSIVE EXECUTION RECURSIVE WITH PUMP REFERENCE MODEL REMOTE SELECT SEMI JOIN SEQUENCE SINGLE PARTITION SINGLE RANGE PARTITION SORT SORT AGGREGATE SORT GROUP BY SORT GROUP BY CUBE SORT GROUP BY NOSORT SORT GROUP BY ROLLUP SORT JOIN SORT ORDER BY SORT UNIQUE SQL MODEL TABLE ACCESS TABLE ACCESS BY GLOBAL INDEX ROWID TABLE ACCESS BY INDEX ROWID TABLE ACCESS BY LOCAL INDEX ROWID TABLE ACCESS BY ROWID TABLE ACCESS BY USER ROWID TABLE ACCESS CLUSTER TABLE ACCESS FULL TABLE ACCESS HASH TABLE ACCESS SAMPLE TABLE QUEUE TEMP TABLE GENERATION TEMP TABLE TRANSFORMATION UNION UNION ALL UNION ALL (RECURSIVE WITH) UNPIVOT UPDATE VIEW VIEW PUSHED PREDICATE WINDOW
Optimize SQL
Create Optimize SQL Sessions Open Optimizer SQL Sessions Rewrite SQL Generate Execution Plan Alternatives
Optimize Indexes Batch Optimize SQL Scan SQL Inspect SGA Analyze Impact Manage Plans Configure Options SQL Optimizer Tutorials About Us Legal Notices

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

  1. Click in the main menu.

  2. Select Batch Optimize SQL | Execution | Execution Method.

  3. 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

  1. Click in the main menu.

  2. Select Batch Optimize SQL | Execution | Termination Criteria.

  3. 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

  1. Click in the main menu.

  2. Select Batch Optimize SQL | Execution | SQL to Test Run.

  3. 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

  1. Click in the main menu.

  2. Select Batch Optimize SQL | Execution | Best Alternative Criteria.

  3.  

    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)

 

Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating