10 The TimesTen Query Optimizer

The TimesTen cost-based query optimizer uses information about an application's tables and their available indexes to choose a fast path to the data. Application developers can examine the plan chosen by the optimizer to check that indexes are used appropriately. If necessary, application developers can also modify the optimizer's behavior so that it chooses a different plan.

This chapter includes the following topics:

When optimization occurs

It is useful to understand when TimesTen performs query optimization, since a single command may be optimized several times.

TimesTen invokes the optimizer whenever a SELECT, UPDATE, DELETE, INSERT SELECT or CREATE MATERIALIZED VIEW statement is prepared through an ODBC SQLPrepare or SQLExecDirect function or any of the JDBC execute methods. The resulting plan persists until an invalidating event occurs, or the command is dropped by the application. A command is invalidated under the following circumstances:

  • A table it uses is dropped

  • A table it uses is altered

  • An index on a table it references is dropped

  • An index is created on a table it references

  • Statistics are recomputed with the invalidation option set to 1 in either the ttIsql statsupdate command, the ttOptUpdateStats built-in procedure, or the ttOptEstimateStats built-in procedures.

    Note:

    For complete details on when to calculate statistics, see "Compute exact or estimated statistics". In addition, see "ttIsql," "ttOptUpdateStats," or "ttOptEstimateStats" in the Oracle TimesTen In-Memory Database Reference.

    If you choose to not invalidate the commands in the SQL command cache, you can manually initiate an invalidation of all commands with the ttOptCmdCacheInvalidate built-in procedure. For more information, see "Control the invalidation of commands in the SQL Command Cache".

An invalid command is usually reprepared automatically just before it is re-executed. This means that the optimizer is invoked again at this time, possibly resulting in a new plan. Thus, a single command may be prepared several times.

Note:

When using JDBC, you must manually reprepare commands when a table has been altered.

A command may have to be prepared manually if, for example, the table that the command referenced was dropped and a new table with the same name was created. When you prepare a statement manually, you should commit the prepare statement so it can be shared. If the command is recompiled because it was invalid, and if recompilation involves DDL on one of the referenced tables, then the prepared statement must be committed to release the command lock.

For example, in ODBC a command joining tables T1 and T2 may undergo the following changes:

Action Description
SQLPrepare Command is prepared.
SQLExecute Command is executed.
SQLExecute Command is re-executed.
Create Index on T1 Command is invalidated.
SQLExecute Command is reprepared, then executed.
SQLExecute Command is re-executed.
ttOptUpdateStats on T1 Command is invalidated if the invalidate flag is passed to the ttOptUpdateStats procedure.
SQLExecute Command is reprepared, then executed.
SQLExecute Command is re-executed.
SQLFreeStmt Command is dropped.

In JDBC, a command joining tables T1 and T2 may undergo the following changes:

Action Description
Connection.prepareStatement Command is prepared.
PreparedStatement.execute Command is executed.
PreparedStatement.execute Command is re-executed.
Create Index on T1 Command is invalidated.
PreparedStatement.execute Command is reprepared, then executed.
PreparedStatement.execute Command is re-executed.
ttOptUpdateStats on T1 Command is invalidated if the invalidate flag is passed to the ttOptUpdateStats procedure.
PreparedStatement.execute Command is reprepared, then executed.
PreparedStatement.execute Command is re-executed.
PreparedStatement.close Command is dropped.

As illustrated, optimization is generally performed at prepare time, but it may also be performed later when indexes are dropped or created, or when statistics are modified. Optimization does not occur if a prepare can use a command in the cache.

If a command was prepared with the genPlan flag set, it will be recompiled with the same flag set. Thus, the plan is generated even though the plan for another query was found in the SYS.PLAN table.

If an application specifies hints to modify the optimizer's behavior, these hints persist until the command is deleted. See "Modifying plan generation"" for more information. For example, when the ODBC SQLPrepare function or JDBC Connection.prepareStatement method is called again on the same handle or when the SQLFreeStmt function or PreparedStatement.close method is called. This means that any intermediate reprepare operations that occur because of invalidations will use those same hints.

Viewing SQL commands stored in the SQL Command Cache

All commands executed—SQL statements, built-in procedures, and so on—are stored in the SQL Command Cache, which uses temporary memory. The commands are stored up until the limit of the SQL Command Cache is reached, then the new commands are stored after the last used commands are removed. You can retrieve one or more of these commands that are stored in the SQL Command Cache.

Note:

This section describes viewing the commands stored in the SQL Command Cache. For details on how to view the query plans associated with these commands, see "Viewing query plans associated with commands stored in the SQL Command Cache".

The following sections describe how to view commands cached in the SQL Command Cache:

Managing performance and troubleshooting commands

You can view all one or more of the SQL commands or details of their query plans with the ttSqlCmdCacheInfo and ttSqlCmdQueryPlan built-in procedures. Use the query plan information to monitor and troubleshoot your queries.

Viewing the SQL commands and query plans can help you perform the following:

  • Detect updates or deletes that are not using an index scan.

  • Monitor query plans of executing queries to ensure all plans are optimized.

  • Detect applications that do not prepare SQL statements or that re-prepare the same statement multiple times.

  • Discover the percentage of space used in the command cache for performance evaluation.

Displaying commands stored in the SQL Command Cache

The commands executed against the TimesTen database are cached in the SQL command cache. The ttSqlCmdCacheInfo built-in procedure displays a specific or all cached commands in the TimesTen SQL command cache. By default, all commands are displayed; if you specify a command id, then only this command is retrieved for display.

The command data is saved in the following format:

  • Command identifier, which is used to retrieve a specific command or its associated query plan.

  • Private connection identifier.

  • Counter for the number of executions.

  • Counter for the number of times the user prepares this statement.

  • Counter for the number of times the user re-prepares this statement.

  • Freeable status, where if the value is one, then the subdaemon can free the space with the garbage collector. A value of zero determines that the space is not able to be freed.

  • Total size in bytes allocated for this command in the cache.

  • User who created the command.

  • Query text up to 1024 characters.

At the end of the list of all SQL commands, a status is printed of how many commands were in the cache.

The following examples show how to display all or a single SQL command from the SQL Command Cache using the ttSqlCmdCacheInfo built-in utility:

Example 10-1 Displaying all SQL commands in the SQL Command Cache

This example executes within ttIsql the ttSqlCmdCacheInfo built-in procedure without arguments to show all cached SQL commands. The SQL commands are displayed in terse format. To display the information where each column is prepended with the column name, execute vertical on before executing the ttsqlCmdCacheInfo procedure.

Command> call ttsqlCmdCacheInfo;
< 528079360, 2048, 0, 1, 0, 1, 2168, PAT                        , select * from 
t7 where x7 is not null or exists (select 1 from t2,t3 where not 'tuf' like 
'abc') >
< 527609108, 2048, 0, 1, 0, 1, 2960, PAT                        , select * from 
t1 where x1 = (select x2 from t2 where z2 in (1,3) and y1=y2) order by 1, 2, 3 >
< 528054656, 2048, 0, 1, 0, 1, 1216, PAT                        , create table 
t2(x2 int,y2 int, z2 int) >
< 528066648, 2048, 0, 1, 0, 1, 1176, PAT                        , insert into t2 
select * from t1 >
< 528013192, 2048, 0, 1, 0, 1, 1848, PAT                        , select * from 
t1 where exists (select * from t2 where x1=x2) or y1=1 >
< 527582620, 2048, 0, 1, 0, 1, 1240, PAT                        , insert into t2 
select * from t1 >
< 527614292, 2048, 0, 1, 0, 1, 2248, PAT                        , select * from 
t1 where exists (select x2 from t2 where x1=x2) order by 1, 2, 3 >
< 528061248, 2048, 0, 1, 0, 1, 696, PAT                        , create index i1 
on t3(y3) >
< 528070368, 2048, 0, 1, 0, 1, 824, PAT                        , call 
ttOptSetOrder('t3 t4 t2 t1') >
< 528018856, 2048, 0, 1, 0, 1, 984, PAT                        , insert into t2 
select * from t1 >
< 527606460, 2048, 0, 1, 0, 1, 2624, PAT                        , select * from 
t1 where x1 = (select x2 from t2 where y1=y2) order by 1, 2, 3 >
< 528123000, 2048, 0, 1, 0, 1, 3616, PAT                        , select * from 
t1 where x1 = 1 or x1 = (select x2 from t2,t3 where z2=t3.x3) >
< 528074624, 2048, 0, 1, 0, 1, 856, PAT                        , call 
ttOptSetOrder('t4 t2 t3 t1') >
< 527973892, 2048, 0, 1, 0, 1, 2872, PAT                        , select * from 
t1 where x1 in (select x2 from t2) or x1 in (select x3 from t3) order by 1, 2, 3 >
< 527953876, 2048, 0, 1, 0, 1, 3000, PAT                        , select * from 
t1 where x1 = (select x2 from t2) order by 1, 2, 3 >
< 527603900, 2048, 0, 1, 0, 1, 2440, PAT                        , select * from 
t1 where x1 in (select x2 from t2 where y1=y2) order by 1, 2, 3 >
< 528093308, 2048, 0, 1, 0, 1, 3608, PAT                        , select * from 
t1 where x1 = 1 or x1 = (select x2 from t2,t3 where z2=t3.x3 and t3.z3=1) >
< 528060608, 2048, 0, 1, 0, 1, 696, PAT                        , create index i1 
on t2 (y2) >

Example 10-2 Displaying a single SQL command

If you provide a command id as the input for the ttSqlCmdCacheInfo, the single SQL command is displayed from within the SQL Command Cache. You can discover the command id from executing this built-in without input. The command id is the first column displayed.

The following example displays the SQL command identified by Command ID of 527973892. It is displayed in terse format; to view with the column headings prepended, execute vertical on before executing the ttSqlCmdCacheInfo built-in.

Command> call ttsqlCmdCacheInfo(527973892);
< 527973892, 2048, 0, 1, 0, 1, 2872, PAT                        , select * from 
t1 where x1 in (select x2 from t2) or x1 in (select x3 from t3) order by 1, 2, 3 >
1 row found.

Viewing SQL query plans

You can view the query plan for a SQL command in one of two ways: storing the latest query plan into the system PLAN table or viewing all cached SQL commands and their query plans in the SQL command cache. Both methods are described in the following sections:

Viewing a query plan from the system PLAN table

The optimizer prepares the query plans. For the last SQL command to be executed—such as a prepared SELECT, UPDATE, DELETE, INSERT SELECT, CREATE TABLE, CREATE MATERIALIZED VIEW and so on—you can instruct that the plan be stored in the system PLAN table:

  1. Instruct TimesTen to generate the plan and store it in the system PLAN table.

  2. Prepare the statement means calling the ODBC SQLPrepare function or JDBC Connection.prepareStatement method on the statement. TimesTen stores the plan into the PLAN table.

  3. Read the generated plan within the SYS.PLAN table.

The stored plan is updated automatically whenever the command is reprepared. Re-preparation occurs automatically if one ore more of the following occurs:

  • A table in the statement is altered.

  • If indexes are created or dropped.

  • The application invalidates commands when statistics are updated with the invalidate option in the ttOptUpdateStats built-in procedure.

  • The user invalidates commands with the ttOptCmdCacheInvalidate built-in procedure.

Note:

For more information, see "Control the invalidation of commands in the SQL Command Cache". For more information on the built-in procedures, see ttOptUpdateStats and ttOptCmdCacheInvalidate in the Oracle TimesTen In-Memory Database Reference.

For these cases, read the PLAN table to view how the plan has been modified.

Instruct TimesTen to store the plan in the system PLAN table

Before you can view the plan in the system PLAN table, call the built-in ttOptSetFlag procedure with the GenPlan flag. This call informs TimesTen that all subsequent calls to the ODBC SQLPrepare function or JDBC Connection.prepareStatement method in the transaction should store the resulting plan in the current SYS.PLAN table.

Note:

Make sure AUTOCOMMIT is not set. If it is, the current transaction completes after the processing of the command and prepares in the next transaction are not affected.

The SYS.PLAN table only stores one plan, so each call to the ODBC SQLPrepare function or JDBC Connection.prepareStatement method overwrites any plan currently stored in the table.

If a command is prepared with the genPlan flag set, it is recompiled with this flag. Thus, the plan is generated even though the plan for another query was found in the SYS.PLAN table.

For example, try the query and optimizer hints with the ttIsql utility. To display optimizer plans, issue the following commands:

autocommit 0;
showplan 1;

Reading query plan from the PLAN table

Once plan generation has been turned on and a command has been prepared, one or more rows in the SYS.PLAN table store the plan for the command. The number of rows in the table depends on the complexity of the command. Each row has seven columns, as described in "System Tables" in the Oracle TimesTen In-Memory Database System Tables and Limits Reference.

Example 10-3 Generating a query plan

This example uses the following query:

SELECT COUNT(*)
FROM T1, T2, T3
WHERE T3.B/T1.B > 1
AND T2.B <> 0
AND T1.A = -T2.A
AND T2.A = T3.A

The optimizer generates the five SYS.PLAN rows shown in the following table. Each row is one step in the plan and reflects an operation that is performed during query execution.

Step Level Operation TblNames IXName Pred Other Pred
1 3 TblLkTtreeScan T1 IX1    
2 3 TblLkTtreeScan T2 IX2(D)   T2.B <> 0
3 2 MergeJoin     T1.A = -T2.A  
4 2 TblLkTtreeScan T3 IX3(D)    
5 1 MergeJoin     T2.A = T3.A T3.B / T1.B > 1

For details about each column in the SYS.PLAN table, see "Describing the PLAN table columns".

Describing the PLAN table columns

The SYS.PLAN table has seven columns.

Column 1 (Step)

Indicates the order of operation, which always starts with one. Example 10-3 uses a table lock range scan in the following order:

  1. Table locking range scan of IX1 on table T1.

  2. Table locking range scan of IX2 on T2.

  3. Merge join of T1 and T2 and so forth.

Column 2 (Level)

Indicates the position of the operation in the join-tree diagram that describes the execution. For Example 10-3, the join tree is as follows:

Join tree example
Description of the illustration jointree.gif

Column 3 (Operation)

Indicates the type of operation being executed. For a description of the potential values in this field and the type of table scan each represents, see SYS.PLAN in "System Tables" in the Oracle TimesTen In-Memory Database System Tables and Limits Reference.

Not all operations the optimizer performs are visible to the user. Only operations significant to performance analysis are shown in the SYS.PLAN table. TblLk is an optimizer hint that is honored at execution time in Serializable or Read Committed isolation. Table locks are used during a scan only if row locks are disabled during preparation.

Column 4 (TblNames)

Indicates the table that is being scanned. This column is used only when the operation is a scan. In all other cases, this column is NULL.

Column 5 (IXName)

Indicates the index that is being used. This column is used only when the operation is an index scan using an existing index—such as a hash or range scan. In all other cases, this column is NULL. Names of range indexes are followed with "(D)" if the scan is descending—from large to small rather than from small to large.

Column 6 (Pred)

Indicates the predicate that participates in the operation, if there is one. Predicates are used only with index scan and MergeJoin operations. The predicate character string is limited to 1,024 characters.

This column may be NULL—indicating no predicate—for a range scan. The optimizer may choose a range scan over a table scan because, in addition to filtering, it has two useful properties:

  • Rows are returned in sorted order, on index key.

  • Rows may be returned faster, especially if the table is sparse.

In Example 10-3, the range scans are used for their sorting capability; none of them evaluates a predicate.

Column 7 (Other Pred)

Indicates any other predicate that is applied while the operation is being executed. These predicates do not participate directly in the scan or join but are evaluated on each row returned by the scan or join.

For example, at step two of the plan generated for Example 10-3, a range scan is performed on table T2. When that scan is performed, the predicate T2.B <> 0 is also evaluated. Similarly, once the final merge-join has been performed, it is then possible to evaluate the predicate T3.B / T1.B > 1.

Viewing query plans associated with commands stored in the SQL Command Cache

Use the query plan information to monitor and troubleshoot your queries.

Note:

For more reasons why to use the ttSqlCmdQueryPlan built-in procedure, see "Managing performance and troubleshooting commands".

The ttSqlCmdQueryPlan built-in procedure displays the query plan of a specific statement or all statements in the command cache. It displays the detailed run-time query plans for the cached SQL queries. By default, all query plans are displayed; if you specify the command id taken from the SQL command output, only the query plan for the specified SQL command is displayed.

Note:

If you want to display a query plan for a specific command, you must provide the command identifier, which is displayed with the ttSqlCmdCacheInfo built-in procedure. See "Displaying commands stored in the SQL Command Cache" for full details.

The plan data displayed when you invoke this built-in procedure is as follows:

  • Command identifier

  • Query text up to 1024 characters

  • Step number of the current operation in the run-time query plan

  • Level number of the current operation in the query plan tree

  • Operation name of current step

  • Name of table used

  • Owner of the table

  • Name of index used

  • If used and available, the index predicate

  • If used and available, the non-indexed predicate

Note:

For more information on how to view this information, see "Reading query plan from the PLAN table". The source of the data may be different, but the mapping and understanding of the material is the same as the query plan in the system PLAN table.

The ttSqlCmdQueryPlan built-in process displays the query plan in a raw data format. Alternatively, you can execute the ttIsql explain command for a formatted version of this output. For more information, see "Display query plan for statement in SQL Command Cache".

The following examples show how to display all or a single SQL query plan from the SQL Command Cache using the ttSqlCmdQueryPlan built-in procedure:

Example 10-4 Displaying all SQL query plans

You can display all SQL query plans associated with commands stored in the command cache with the ttSqlCmdQuery plan built-in procedure within the ttIsql utility.

The following example shows the output when executing the ttSqlCmdQueryPlan built-in procedure without arguments, which displays detailed run-time query plans for all valid queries. For invalid queries, there is no query plan; instead, the query text is displayed.

The query plans are displayed in terse format; to view with the column headings prepended, execute vertical on before executing the ttSqlCmdQueryPlan built-in procedure.

Note: For complex expressions, there may be some difficulties in printing out the original expressions.

Command> call ttSqlCmdQueryPlan();

< 528079360, select * from t7 where x7 is not null or exists (select 1 from t2,t3 
where not 'tuf' like 'abc'), <NULL>, <NULL>, <NULL>, <NULL>, <NULL>, <NULL>, 
<NULL>, <NULL> >
< 528079360, <NULL>, 0, 2, RowLkSerialScan                , T7                             
, PAT                        ,                                , ,  >
< 528079360, <NULL>, 1, 3, RowLkTtreeScan                 , T2                             
, PAT                        , I2                             , , NOT(LIKE( tuf 
,abc ,NULL ))  >
< 528079360, <NULL>, 2, 3, RowLkTtreeScan                 , T3                             
, PAT                        , I2                             , ,  >
< 528079360, <NULL>, 3, 2, NestedLoop                     ,                                
,                                ,                                , ,  >
< 528079360, <NULL>, 4, 1, NestedLoop(Left OuterJoin)     ,                                
,                                ,                                , ,  >
< 528079360, <NULL>, 5, 0, Filter                         ,                                
,                                ,                                , , X7 >
< 527576540, call ttSqlCmdQueryPlan(527973892), <NULL>, <NULL>, <NULL>, <NULL>, 
<NULL>, <NULL>, <NULL>, <NULL> >
< 527576540, <NULL>, 0, 0, Procedure Call                 ,                                
,                                ,                                , ,  >
< 528054656, create table t2(x2 int,y2 int, z2 int), <NULL>, <NULL>, <NULL>, 
<NULL>, <NULL>, <NULL>, <NULL>, <NULL> >
< 528066648, insert into t2 select * from t1, <NULL>, <NULL>, <NULL>, <NULL>, 
<NULL>, <NULL>, <NULL>, <NULL> >
< 528066648, <NULL>, 0, 0, Insert                         , T2                             
, PAT                        ,                                , ,  >
< 528013192, select * from t1 where exists (select * from t2 where x1=x2) or 
y1=1, <NULL>, <NULL>, <NULL>, <NULL>, <NULL>, <NULL>, <NULL>, <NULL> >
< 528061248, create index i1 on t3(y3), <NULL>, <NULL>, <NULL>, <NULL>, <NULL>, 
<NULL>, <NULL>, <NULL> >
< 528070368, call ttOptSetOrder('t3 t4 t2 t1'), <NULL>, <NULL>, <NULL>, <NULL>, 
<NULL>, <NULL>, <NULL>, <NULL> >
< 528070368, <NULL>, 0, 0, Procedure Call                 ,                                
,                                ,                                , ,  >
< 528018856, insert into t2 select * from t1, <NULL>, <NULL>, <NULL>, <NULL>, 
<NULL>, <NULL>, <NULL>, <NULL> >
< 527573452, call ttsqlCmdCacheInfo(527973892), <NULL>, <NULL>, <NULL>, <NULL>, 
<NULL>, <NULL>, <NULL>, <NULL> >
< 527573452, <NULL>, 0, 0, Procedure Call                 ,                                
,                                ,                                , ,  >
< 528123000, select * from t1 where x1 = 1 or x1 = (select x2 from t2,t3 where 
z2=t3.x3), <NULL>, <NULL>, <NULL>, <NULL>, <NULL>, <NULL>, <NULL>, <NULL> >
< 528123000, <NULL>, 0, 2, RowLkSerialScan                , T1                             
, PAT                        ,                                , ,  >
< 528123000, <NULL>, 1, 6, RowLkTtreeScan                 , T2                             
, PAT                        , I2                             , ,  >
< 528123000, <NULL>, 2, 6, RowLkTtreeScan                 , T3                             
, PAT                        , I2                             , ,  Z2 = X3; >
< 528123000, <NULL>, 3, 5, NestedLoop                     ,                                
,                                ,                                , ,  >
< 528123000, <NULL>, 4, 4, Materialized View              ,                                
,                                ,                                , ,  >
< 528123000, <NULL>, 5, 3, GroupBy                        ,                                
,                                ,                                , ,  >
< 528123000, <NULL>, 6, 2, Filter                         ,                                
,                                ,                                , ,  X1 = 
colum_name; >
< 528123000, <NULL>, 7, 1, NestedLoop(Left OuterJoin)     ,                                
,                                ,                                , ,  >
< 528123000, <NULL>, 8, 0, Filter                         ,                                
,                                ,                                , ,  X1 = 1; >

Example 10-5 Displaying a single SQL query plan

You can display any query plan associated with a SQL command by providing the command id of the SQL command as the input for the ttSqlCmdQueryPlan built-in procedure. The single query plan is displayed from within the SQL Command Cache. You can discover the command id from executing this ttSqlCmdCacheInfo built-in without input. The command id is the first column displayed.

The following example displays the query plan of the SQL command identified by command id of 528078576. It is displayed in terse format; to view with the column headings prepended, execute vertical on before executing the ttSqlCmdQueryPlan built-in procedure.

Note: for complex expressions, there are some difficulties to print original expressions.

Command> call ttSqlCmdQueryPlan( 528078576);
< 528078576, select * from t1 where 1=2 or (x1 in (select x2 from t2, t5 where y2 
in (select y3 from t3)) and y1 in (select x4 from t4)), <NULL>, <NULL>, <NULL>, 
<NULL>, <NULL>, <NULL>, <NULL>, <NULL> >
< 528078576, <NULL>, 0, 4, RowLkSerialScan                , T1                             
, PAT                        ,                                , ,  >
< 528078576, <NULL>, 1, 7, RowLkTtreeScan                 , T2                             
, PAT                        , I2                             , ,  >
< 528078576, <NULL>, 2, 7, RowLkTtreeScan                 , T5                             
, PAT                        , I2                             , ,  >
< 528078576, <NULL>, 3, 6, NestedLoop                     ,                                
,                                ,                                , ,  >
< 528078576, <NULL>, 4, 6, RowLkTtreeScan                 , T3                             
, PAT                        , I1                             ,  ( (Y3=Y2; ) ) ,  >
< 528078576, <NULL>, 5, 5, NestedLoop                     ,                                
,                                ,                                , ,  >
< 528078576, <NULL>, 6, 4, Filter                         ,                                
,                                ,                                , ,  X1 = X2; >
< 528078576, <NULL>, 7, 3, NestedLoop(Left OuterJoin)     ,                                
,                                ,                                , ,  >
< 528078576, <NULL>, 8, 2, Filter                         ,                                
,                                ,                                , ,  >
< 528078576, <NULL>, 9, 2, RowLkTtreeScan                 , T4                             
, PAT                        , I2                             , ,  Y1 = X4; >
< 528078576, <NULL>, 10, 1, NestedLoop(Left OuterJoin)     ,                                
,                                ,                                , ,  >
< 528078576, <NULL>, 11, 0, Filter                         ,                                
,                                ,                                , ,  >
13 rows found.
Command>

Modifying plan generation

If you decide that you want to modify a query plan, you can only modify the query plan that exists in the system PLAN table, as described in "Viewing a query plan from the system PLAN table". Once you do modify the query plan, it does not replace the query plan, but creates a new query plan with your changes.

The following sections describe why you may want to modify execution plans and then how to modify them:

Why modify an execution plan?

Applications may want to modify an execution plan for two reasons:

  • The plan is optimally fast but is ill-suited for the application. The optimizer may select the fastest execution 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 execute 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.

When to modify an execution plan

Applications can modify an execution plan by providing hints to the optimizer. Hints are specified by calls to one of the TimesTen optimizer built-in procedures and are in effect for all calls to the ODBC SQLPrepare function or JDBC PreparedStatement objects in the transaction. For more information on how to provide these hints, see "How to modify execution plan generation".

Note:

Make sure AUTOCOMMIT is not set. If it is, the current transaction completes after processing the ttOptSetFlag procedure and prepares in the next transaction are not affected.

If a command is prepared with certain hints in effect, those hints continue to apply if the command is reprepared 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 "When optimization occurs".

If a command is prepared without hints, subsequent hints will not affect the command if it is reprepared automatically. An application must call the ODBC SQLPrepare function or JDBC Connection.prepareStatement method a second time so that hints have an effect.

Example 10-6 Tuning 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 tune_join.gif follows
Description of the illustration tune_join.gif

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

Application steps for tuning joins
Description of the illustration execution_steps.gif

Example 10-7 Tuning 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.

Tuning a join within JDBC
Description of the illustration jdbc_steps.gif

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

JDBC execution steps
Description of the illustration jdbc_exec_steps.gif

How to modify execution plan generation

To change the query optimizer behavior, an application calls one of the following built-in procedures using the ODBC procedure call interface:

  • ttOptSetFlag—Sets certain optimizer parameters.

  • ttOptSetOrder—Allows an application to specify the table join order.

  • ttOptUseIndex—Allows an application to specify that an index be used or to disable the use of certain indexes.

  • ttOptClearStats, ttOptEstimateStats, ttOptSetColIntvlStats, ttOptSetTblStats, ttOptUpdateStats—Manipulate statistics that the TimesTen Data Manager 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 executes. For full details on these built-in procedures and any privileges required, 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 the optimizer hints. To view your current optimizer hint settings, use the optprofile command.

Example 10-8 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 10-9 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 */
...