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

Test Run Settings

Use the Test Run Settings dialog to specify test-run options and optimization goals when you test run multiple SQL simultaneously. This dialog opens when you initiate the test-run process in Optimize SQL. The options you specify are used for the current test run only.

When you initiate the test-run process from the SQL Details tab, the Test Run Settings dialog provides two methods for specifying test-run settings:

  • You can allow SQL Optimizer to determine the best test run settings based on your answers to a few questions.
  • Or you can customize the test run settings yourself.

Test Run Different Bind Values

If you initiate the test run process from the Test Run Different Bind Values tab, read Test Run Settings for Multiple Bind Values first.

Allow SQL Optimizer to Determine Settings

If you want SQL Optimizer to choose the best test-run settings for you, simply answer the questions on the first page (Usage and Symptom) of the Test Run Settings dialog. SQL Optimizer will determine your optimization goals based on your answers.

The settings that SQL Optimizer selects are displayed in the Recommended Settings section at the bottom of the Usage and Symptom page.

To allow SQL Optimizer to determine the best test run settings

  1. Answer the questions on the first page (Usage and Symptom) of the Test Run Settings dialog.
  2. After answering the questions, review the test-run settings selected by SQL Optimizer in the Recommended Settings section (bottom of page).
  3. Click Start Test Run.

Customize Test Run Settings

If you want to configure the test-run settings manually, click the Customize Test Run Settings link which opens additional pages in the Test Run Settings dialog.

To customize the test run settings

  1. Click the Customize Test Run Settings link located at the bottom of the first page (Usage and Symptom) of the Test Run Settings dialog.
  2. Specify custom test-run options on the Customized Setup page. Review the following for additional information:

    Execution Method

    Review the following options.

    Execution Method

    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
    Rows Retrieved

    Select one of the following:

    • Retrieve all rows—Select this option to retrieve all rows.
    • Retrieve only this first number of rows from the SQL—Select this option to specify the number of rows to retrieve.

      Purpose: This option is useful for simulating the performance of SQL which normally retrieves only a specific number of rows.

      Tip: You can specify a default value in Options | Optimize SQL | Test Run.

    Run Time Retrieval Method

    Review the following options.

    Dynamically determine the number of times to test run (running at most twice)

    (Not applicable to the Test Run Different Bind Values feature)

    If the run time of your original SQL is less than the threshold you specify here, your original SQL and all alternatives run twice. The second run time is used as the test run result.

    Otherwise, all SQL run once.

    Tip: You can specify a default value for this threshold in Options | Optimize SQL | Test Run.

    Dynamically determine the number of times to test run (may run multiple times)

    (Not applicable to the Test Run Different Bind Values feature)

    Determine how many times to run SQL if the run time of your original SQL is less than the thresholds you specify here.

    In the first statement, specify max run time and number of runs. If the run time of your original SQL is less than this threshold, your original SQL and all alternatives run the number of times you specify, and the average run time is used.

    Tip: You can specify default values for these two editable options in Options | Optimize SQL | Test Run.

    In the second statement, if the run time of the original SQL is less than the threshold you specify here, the original SQL and all alternatives run twice, and the second run time is used.

    Otherwise, all SQL run once.

    Run original (or first executed) SQL twice and all other SQL 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 and measure 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.
    Flush BUFFER_CACHE before running each SQL (Running SQL once)

    Executes all SQL statements once, but flushes the buffer cache prior to running each statement, so each statement starts with a cleared cache.

    Caution: Setting this option results in flushing the entire buffer pool. All users currently connected to the same database will be affected.
    We DO NOT recommend using the flush BUFFER_CACHE option on a production database! Flushing the data buffer cache can impose a serious performance overhead (especially on an Oracle RAC database). The Flush BUFFER_CACHE option is intended for use only on a test database.

    Notes:

    • You must have ALTER SYSTEM privileges to flush the BUFFER_CACHE.
    • This option is only available if you are connected to an Oracle 10g (or later) database.

    Order and Termination

    Review the following options. If testing with different bind values, see Test Run Settings for Multiple Bind Values for exceptions.

    Execution Order

    Select one of the following:

    • Intelligent order—Executes representative SQL statements with various plan costs according to SQL Optimizer's intelligence engine.
    • Plan Cost—Executes SQL statements in order of plan cost.
    SQL Termination Criteria

    Select one of the following:

    • Run time of the fastest SQL - this test run only

      Finds the fastest run time of the SQL in this test run. 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 the fastest SQL - including fastest run time from tested alternatives

      Similar to the previous option, except the fastest run time from tested alternatives is used as the initial termination time for this test run. When a SQL statement runs faster than this time, the faster time is used as the new termination time.

    • This percentage of the original SQL run time—Cancels SQL statements whose total elapsed time is the specified % of the total elapsed time for the original SQL statement. It terminates all SQL statements that run longer than the calculated termination time.
      • If the original SQL has been tested, but is not included in the test run, select whether to use the original SQL's existing run time or to run it again.
      • If the original SQL has not been tested, it will be included in the test run.
    • User-defined time (min:sec)—Cancels SQL statements that run longer than the time you specify here.
      • If the original SQL is included in the test run, specify whether to terminate the original after the specified time or allow original SQL to run until finished.
    • Run without termination—Runs all SQL statements to completion, regardless of run time.

    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.

    Best Alternative Criteria

    If testing with different bind values, see Test Run Settings for Multiple Bind Values for exceptions.

    Best SQL Alternative Selection Criteria

    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.
    • Physical Reads—Uses the number of physical reads to find the best SQL alternative or best index set.
    • Session Logical Reads—Uses the number of logical reads to find the best SQL alternative or best index set.
    • CPU Used by this Session—Uses the execution CPU time to find the best SQL alternative or best index set.

    SQL To Test Run

    (Not available if using the Auto Optimize command or the Test Run Different Bind Values feature)

    Test run criteria

    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.
  3. After specifying custom options, click Start Test Run.

Tip: You can specify some default options for this dialog in Options | Optimize SQL | Test Run. See Test Run Options for more information.

Test Run Settings for Multiple Bind Values

If you initiate the test-run process from the Test Run Different Bind Values tab, the Test Run Settings dialog automatically opens to the custom settings pages. Review the following exceptions. Then return to Customize Test Run Settings to finish specifying custom test-run options.

  • Execution Method: Execution Plan

    When a SQL statement is executed for a second time using different bind values, Oracle might reuse the execution plan from the previous execution (a feature known as Cursor Sharing). To proceed with this, select to “reuse the cached execution plan”. If you want cached execution plans to have no bearing on how Oracle chooses to execute the SQL statement then select to “ignore the cached execution plan”.

    Ignore the cached execution plan

    Hide from Oracle any cached execution plans from previous bind value set executions. When you select this option, cached execution plans will have no bearing on how Oracle chooses to execute the SQL statement. This option allows you to measure the performance of alternative SQL statements in each bind value set without potential interference (and in isolation) from previous executions.

    Reuse the cached execution plan

    This matches the default Oracle behavior. It better simulates the performance of the alternative SQL statements in a real database environment where Cursor Sharing may occur.

  • Order and Termination: SQL Termination Criteria

    When testing multiple bind values, for the following options each SQL is executed using all bind value sets and then the total of all run times is used.

    Note: If you select Let me select what to test run on the Finish Setup page, these options are not available in the Test Run Settings dialog.

    Total run time of the fastest SQL

    Finds the total run time of the fastest SQL in this test run.

    Total run time = Set 1 run time + Set 2 run time, etc.

    Then terminates a SQL if its cumulative run time exceeds the fastest total run time. If a SQL is terminated, any remaining bind value sets are not executed.

    Use this option to shorten the test-run process.

    This percentage of original SQL's total run time

    Finds the total run time of the original SQL and then applies the percentage you specify.

    Similar to the preceding option in that if a SQL is terminated, any remaining bind value sets are not run.

    When testing multiple bind values, the following option uses the individual run time of an alternative with one bind value set.

    User-defined time

    Applies to the run time for individual SQL, not the total.

    Cancels the SQL / bind value sets that run longer than the time you specify.

    The termination of a SQL / bind value set does not affect execution with the remaining bind value sets.

  • Best Alternative Criteria

    Select the performance statistic to use to find the best alternative. When testing multiple bind values, for each alternative, the SQL statement is executed using all bind values and then the average is used.

  

Related Topics

Test Run SQL Alternatives

Test Run Execution Plan Alternatives

Test Run Special Current Settings

Use the Test Run Special Current dialog to specify test-run options when you test run a single SQL or plan alternative. This dialog opens when you select the Test Run Special - Current command to initiate the test-run process. The options you specify are used for the current test run only.

To specify Test Run options for a single alternative

  1. When the Test Run Special Settings dialog opens, specify test-run options. Review the following for additional information:

    Execution Method

    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
    Rows to Retrieve

    Select one of the following:

    • Retrieve all rows—Select this option to retrieve all rows.
    • Retrieve only this first number of rows from the SQL—Select this option to specify the number of rows to retrieve.

      Purpose: This option is useful for simulating the performance of SQL which normally retrieves only a specific number of rows.

      Tip: You can specify a default value in Options | Optimize SQL | Test Run.

    Run Time Retrieval Method

    Select one of the following:

    Run the SQL once—Runs the SQL one time.

    Run the SQL multiple times—Select this option to run the SQL alternative multiple times. Then specify the number of runs.

    Flush BUFFER_CACHE before running each SQL (Running SQL once)—

    Executes all SQL statements once, but flushes the buffer cache prior to running each statement, so each statement starts with a cleared cache.

    Caution: Setting this option results in flushing the entire buffer pool. All users currently connected to the same database will be affected.
    We DO NOT recommend using the flush BUFFER_CACHE option on a production database! Flushing the data buffer cache can impose a serious performance overhead (especially on an Oracle RAC database). The Flush BUFFER_CACHE option is intended for use only on a test database.

    Notes:

    • You must have ALTER SYSTEM privileges to flush the BUFFER_CACHE.
    • This option is only available if you are connected to an Oracle 10g (or later) database.
  2. Click Start Test Run.

  

Related Topics

Test Run SQL Alternatives

Test Run Execution Plan Alternatives

Select an Execution Method

In the Test Run Settings dialog (or Options dialog), SQL Optimizer allows you to choose the best execution method for measuring the performance of your SQL statements in a test run. Select an execution method based on how the SQL is normally used.

If specifying How this SQL is used on the Usage and Symptom page (Test Run Settings dialog), use the following information to help select an option. See Test Run Settings for more information about the Test Run Settings dialog.

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

Determine if SQL is Static or Dynamic

Use the following examples to help determine how your SQL statements are normally executed: as static or dynamic SQL.

PL/SQL Description PL/SQL Example Static/Dynamic SQL
SELECT INTO Statement

DECLARE NUM NUMBER; BEGIN SELECT COUNT (*) INTO NUM FROM GRADE WHERE GRD_ID < 1500; END;

Static SQL
Cursor FOR LOOP Statement

DECLARE TEMP VARCHAR (100); BEGIN FOR REC IN (SELECT GRD_ID FROM GRADE WHERE GRD_ID < 1500) LOOP TEMP := REC.GRD_ID; END LOOP; END;

Static SQL
DML Statement

DECLARE BEGIN INSERT INTO MYTABLE1 SELECT COUNT (*) FROM GRADE WHERE GRD_ID < 1500; END;

Static SQL
Explicit Cursor

DECLARE CURSOR C1 IS SELECT GRD_ID FROM GRADE WHERE GRD_ID < 1500; BEGIN OPEN C1; CLOSE C1; END;

Static SQL
Cursor variable with unquoted select statement

DECLARE C1 SYS_REFCURSOR; BEGIN OPEN C1 FOR SELECT GRD_ID FROM GRADE WHERE GRD_ID < 1500; CLOSE C1; END;

Static SQL
Cursor variable with quoted select statement in string literal, variable, or expression

DECLARE C1 SYS_REFCURSOR; BEGIN OPEN C1 FOR 'SELECT GRD_ID FROM GRADE WHERE GRD_ID < 1500'; CLOSE C1; END;

Dynamic SQL
EXECUTE IMMEDIATE Statement

DECLARE BEGIN EXECUTE IMMEDIATE ('SELECT GRD_ID FROM GRADE WHERE GRD_ID < 1500'); END;

Dynamic SQL

 

  

Set Bind Variables

You need to define the values of SQL statement variables before you optimize the statement. The Set Bind Variables window displays automatically when you use Optimize SQL for a statement with variables.

If you are using Optimize SQL and you want to test run SQL alternatives using multiple bind values, see Test Run Different Bind Values.

Troubleshooting: If the Set Bind Variables window 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.

To set a bind variable

  1. Select the Optimize SQL tab in the main window.

  2. Select a SQL Rewrite or Plan Control Session.
  3. Enter a SQL statement with a bind variable.

  4. Click .

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

 

Related Topic

Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating