Modifying a SQL Query Execution Plan

Why Modify an Execution Plan?

You evaluate and modify an execution plan if it is ill-suited for your application or if it is not optimal in performance.

The plan is optimally fast but is ill-suited for the application. The optimizer may select the fastest query processing path, but this path may not be desirable from the application's point of view. For example, if the optimizer chooses to use certain indexes, these choices may prevent other operations-such as certain update or delete operations-from occurring simultaneously on the indexed tables. In this case, an application can prevent the use of those indexes.

The plan chosen by the optimizer may also consume more memory than is available or than the application wants to allocate. For example, this may happen if the plan stores intermediate results or requires the creation of temporary indexes.

The plan is not optimally performant. The query optimizer chooses the plan that it estimates will run the fastest based on its knowledge of the tables' contents, available indexes, statistics, and the relative costs of various internal operations. The optimizer often has to make estimates or generalizations when evaluating this information, so there can be instances where it does not choose the fastest plan. In this case, an application can adjust the optimizer's behavior to try to produce a better plan.

How Hints Can Influence an Execution Plan

You can apply hints to pass instructions to the TimesTen query optimizer. The optimizer considers these hints when choosing the best execution plan for your query.

Transaction level hints are in effect for all calls to the ODBC SQLPrepare function or JDBC PreparedStatement objects in the transaction.

If a command is prepared with certain hints in effect, those hints continue to apply if the command is re-prepared automatically, even when this happens outside the initial prepare transaction. This can happen when a table is altered, or an index is dropped or created, or when statistics are modified, as described in Using the Query Optimizer to Choose Optimal Plan.
If a command is prepared without hints, subsequent hints do not affect the command if it is re-prepared automatically. An application must call the ODBC SQLPrepare function or JDBC Connection.prepareStatement() method a second time so that hints have an effect.

Tuning a Join When Using ODBC

A developer can tune a join when using ODBC.

When using ODBC, a developer tuning a join on T1 and T2 might go through the steps shown in the following figure.

Description of the illustration tune_join.gif

During processing, the application may then go through the steps shown in the following figure.

Description of execution_steps.gif follows

Description of the illustration execution_steps.gif

Tuning a Join When Using JDBC

A developer can tune a join when using JDBC.

When using JDBC, a developer tuning a join on T1 and T2 might go through the steps shown in the following figure.

Description of the illustration jdbc_steps.gif

During processing, the application may then go through the steps shown in the following figure.

Description of jdbc_exec_steps.gif follows

Description of the illustration jdbc_exec_steps.gif

Use Optimizer Hints to Modify the Execution Plan

You can apply hints to pass instructions to the TimesTen query optimizer.

To apply a hint only for a particular SQL statement, use a statement level optimizer hint.
To apply a hint for an entire transaction, use a transaction level optimizer hint with the appropriate TimesTen built-in procedure.
To apply a hint for an entire TimesTen connection, use a connection level optimizer hint.

The order of precedence for optimizer hints is statement level hints, transaction level hints and then connection level hints.

Note:

TimesTen concurrently processes read and write operations optimally. Your read operations can be optimized for read-only concurrency when you use transaction level optimizer hints such as ttOptSetFlag ('tblLock',1) or statement level optimizer hints such as /*+ tt_tbllock(1) tt_rowlock(0) */. Write operations that operate concurrently with read optimized operations may result in contention.

You can control read optimization during periods of concurrent write operations with the ttDbWriteConcurrencyModeSet built-in procedure. For more information, see Control Read Optimization During Concurrent Write Operations.

Directions for applying hints are described in the following sections:

Apply Statement Level Optimizer Hints for a SQL Statement

A statement level optimizer hint is a specially formatted SQL comment containing instructions for the SQL optimizer.

A statement level optimizer hint can be specified within the SQL statement that it is to be applied against with one of the following methods:

/*+ */ The hints can be defined over multiple lines. The hints must be enclosed in the comment syntax. The plus sign (+) denotes the start of a hint.
--+ The hint must be defined on a single line after the plus sign (+).

The statement level optimizer hint can be specified in the SELECT, INSERT, UPDATE, MERGE, DELETE, CREATE TABLE AS SELECT, or INSERT ... SELECT statements. You must specify the hint within comment syntax immediately following the SQL VERB.

If you specify any statement level optimizer hints incorrectly, TimesTen ignores these hints and does not provide an error. If you define conflicting hints, the rightmost hint overrides any conflicting hints for the statement.

See Statement Level Optimizer Hints in the Oracle TimesTen In-Memory Database SQL Reference for information specific to statement level optimizer hints. See Using Optimizer Hints in the Oracle TimesTen In-Memory Database Scaleout User's Guide for optimizer hints that are specific to TimesTen Scaleout.

Apply Transaction Level Optimizer Hints for a Transaction

To change the query optimizer behavior for all statements in a transaction, an application calls the ttOptSetFlag built-in procedure using the ODBC procedure call interface.

Note:

Make sure autocommit is off for transaction level optimizer hints. All optimizer flags are reset to their default values when the transaction has been committed or rolled back. If optimizer flags are set while autocommit is on, the optimizer flags are ignored because each statement runs within its own transaction.

ttOptSetFlag—Sets certain optimizer parameters. Provides the optimizer with transaction level optimizer hints with a recommendation on how to best optimize a particular query.
ttOptGetFlag—View the existing transaction level hints set for a database.
ttOptSetOrder—Enables an application to specify the table join order.
ttOptUseIndex—Enables an application to specify that an index be used or to disable the use of certain indexes; that is, to specify which indexes should be considered for each correlation in a query.
ttOptClearStats, ttOptEstimateStats, ttOptSetColIntvlStats, ttOptSetTblStats, ttOptUpdateStats—Manipulate statistics that TimesTen maintains on the application's data that are used by the query optimizer to estimate costs of various operations.

Some of these built-in procedures require that the user have privileges to the objects on which the utility runs. See Built-In Procedures in the Oracle TimesTen In-Memory Database Reference.

The following examples provide an ODBC and JDBC method on how to use the ttOptSetFlag built-in procedure:

Note:

You can also experiment with optimizer settings using the ttIsql utility. The commands that start with "try" control transaction level optimizer hints. To view current transaction level optimizer hint settings, use the optprofile command.

Example: Using ttOptSetFlag in JDBC

This JDBC example illustrates the use of ttOptSetFlag to prevent the optimizer from choosing a merge join.

import java.sql.*; 
class Example 
{ 
 public void myMethod() { 
    CallableStatement cStmt; 
    PreparedStatement pStmt;
     . . . . . 
    try {
         . . . . . . . 
        // Prevent the optimizer from choosing Merge Join 
        cStmt = con.prepareCall("{ 
            CALL ttOptSetFlag('MergeJoin', 0)}"); 
        cStmt.execute();
        // Next prepared query 
        pStmt=con.prepareStatement( 
        "SELECT * FROM Tbl1, Tbl2 WHERE Tbl1.ssn=Tbl2.ssn");
        . . . . . . . 
        catch (SQLException ex) { 
            ex.printStackTrace(); 
        } 
    } 
    . . . . . . .
}

Example: Using ttOptSetFlag in ODBC

This ODBC example illustrates the use of ttOptSetFlag to prevent the optimizer from choosing a merge join.

#include <sql.h>
SQLRETURN rc;
SQLHSTMT hstmt; fetchStmt;
....
rc = SQLExecDirect (hstmt, (SQLCHAR *)
     "{CALL ttOptSetFlag (MergeJoin, 0)}",
     SQL_NTS)
/* check return value */
...
rc = SQLPrepare (fetchStmt, ...)
/* check return value */
...

Apply Connection Level Optimizer Hints for a TimesTen Connection

To change the query optimizer behavior for all statements of a specific connection, define the OptimizerHint connection attribute.

The value of the OptimizerHint connection attribute is a string that uses the same format as a statement level optimizer hint, but without the delimiters of /*+, */, and --+.

You cannot include comments with connection level optimizer hints.

Transaction level optimizer hints overwrite connection level optimizer hints for the current transaction. After a commit, the transaction level optimizer hints are lost and the connection level optimizer hints take effect. Statement level optimizer hints overwrite transaction level optimizer hints and connection level optimizer hints for the scope of the statement. Since this is a connection attribute, the ttConfiguration utility shows the connection level optimizer hints.

Note:

In a client server setting, the client connection setting of this connection attribute overwrites the Server DSN setting of this attribute.

This example illustrates how to use connection level optimizer hints. This example uses TT_RowLock, TT_TblLock, and TT_MergeJoin to enable row locking, disable table locking, and disable merge joins. Note that /disk1/timesten is the timesten_home.

...
[database1]
Driver=/disk1/timesten/install/lib/libtten.so
DataStore=/disk1/timesten/info/DemoDataStore/database1
PermSize=128
TempSize=64
DatabaseCharacterSet=AL32UTF8
OptimizerHint= TT_RowLock (1) TT_TblLock (0) TT_MergeJoin (0)
...

TimesTen does not support the use of the semicolon (;) character in connection attributes. Therefore, when you want to use more than one TT_INDEX optimizer hint at the connection level, you cannot use the same syntax that you use for the statement level.

For example, TT_INDEX(EMPLOYEES,EMP_NAME_IX,1;EMPLOYEES,EMP_MANAGER_IX,1) is a valid statement level optimizer hint, but it is an invalid connection level optimizer hint. TimesTen merges multiple TT_INDEX optimizer hints if they are specified on the same line. Therefore, TT_INDEX(EMPLOYEES,EMP_NAME_IX,1) TT_INDEX(EMPLOYEES,EMP_MANAGER_IX,1)is equivalent to TT_INDEX(EMPLOYEES,EMP_NAME_IX,1;EMPLOYEES,EMP_MANAGER_IX,1).

This example illustrates the use of multiple TT_INDEX optimizer hints at the connection level. Note that /disk1/timesten is the timesten_home.

...
[database1]
Driver=/disk1/timesten/install/lib/libtten.so
DataStore=/disk1/databases/info/DemoDataStore/database1
PermSize=128
TempSize=64
DatabaseCharacterSet=AL32UTF8
OptimizerHint= TT_INDEX(EMPLOYEES,EMP_NAME_IX,1) TT_INDEX(EMPLOYEES,EMP_MANAGER_IX,1)
...

See OptimizerHint in the Oracle TimesTen In-Memory Database Reference.