Viewing SQL Query Plans

You can view the query plan for a command in one of two ways: storing the latest query plan into the system PLAN table or viewing all cached 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 statement to run, you can instruct that the plan be stored in the system PLAN table:

Instruct TimesTen to generate the plan and store it in the system PLAN table.
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.
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 or 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:

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, enable the creation of entries in the PLAN table with the plan generation option as follows:

For transaction level optimizer hints, call the built-in ttOptSetFlag procedure and enable the GenPlan flag.
For statement level optimizer hints, set TT_GENPLAN(1), which is only in effect for the statement. After the statement runs, the plan generation option takes on the value of the GenPlan transaction level optimizer hint.

Note:

See Use Optimizer Hints to Modify the Execution Plan for details on statement level and transaction level optimizer hints.

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

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 plan generation option set, it is also recompiled for plan generation. Thus, the plan is generated even though the plan for another query was found in the SYS.PLAN table.

You can use showplan in ttIsql to test the query and optimizer hints, which enables plan generation as well as shows the query plan for the statements in the transaction. Autocommit must be off.

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 and Views in the Oracle TimesTen In-Memory Database System Tables and Views Reference.

The following example generates a query plan with the following query:

Command> 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 processing.

Step	Level	Operation	TblNames	IXName	Pred	Other Pred
1	3	TblLkRangeScan	t1	IX1	N/A	N/A
2	3	TblLkRangeScan	t2	IX2(D)	N/A	t2.b <> 0
3	2	MergeJoin	N/A	N/A	t1.a = -t2.a	N/A
4	2	TblLkRangeScan	t3	IX3(D)	N/A	N/A
5	1	MergeJoin	N/A	N/A	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. This example 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 query processing. For this example, the join tree is as follows:

Description of the illustration jointree.eps
Column 3 (Operation)

Indicates the type of operation being run. For a description of the potential values in this field and the type of table scan each represents, see SYS.PLAN in System Tables and Views in the Oracle TimesTen In-Memory Database System Tables and Views 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 runtime 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 this example, 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 runs. 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 this example, 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 command output, only the query plan for the specified command is displayed.

Note:

If you want to display a query plan for a specific command, you must provide the command identifier that is displayed with the ttSQLCmdCacheInfo built-in procedure. See Displaying Commands Stored in the SQL Command Cache.

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:

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 run the ttIsql explain command for a formatted version of this output. 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: Displaying all SQL query plans

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

The following example shows the output when running 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, run vertical on before calling 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, RowLkRangeScan                 , T2 
, PAT                        , I2                             , , NOT(LIKE( tuf 
,abc ,NULL ))  >
< 528079360, <NULL>, 2, 3, RowLkRangeScan                 , 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, RowLkRangeScan                 , T2 
, PAT                        , I2                             , ,  >
< 528123000, <NULL>, 2, 6, RowLkRangeScan                 , 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: Displaying a single SQL query plan

You can display any query plan associated with a command by providing the command id of the command as the input for the ttSQLCmdQueryPlan built-in procedure. The single query plan is displayed from within the SQL command cache. If no command id is supplied, the ttSQLCmdCacheInfo built-in procedure displays information about all current commands in the TimesTen cache.

The following example displays the query plan of the command identified by command id of 528078576. It is displayed in terse format; to view with the column headings prepended, run vertical on before calling 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, RowLkRangeScan                 , T2 
, PAT                        , I2                             , ,  >
< 528078576, <NULL>, 2, 7, RowLkRangeScan                 , T5 
, PAT                        , I2                             , ,  >
< 528078576, <NULL>, 3, 6, NestedLoop                     , 
,                                ,                                , ,  >
< 528078576, <NULL>, 4, 6, RowLkRangeScan                 , 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, RowLkRangeScan                 , T4 
, PAT                        , I2                             , ,  Y1 = X4; >
< 528078576, <NULL>, 10, 1, NestedLoop(Left OuterJoin)     , 
,                                ,                                , ,  >
< 528078576, <NULL>, 11, 0, Filter                         , 
,                                ,                                , ,  >
13 rows found.
Command>