ttSQLCmdQueryPlan

Description

Displays all detailed run-time query plans for SQL statements in the TimesTen SQL command cache. If no argument is supplied, this procedure displays the query plan for all valid commands in the TimesTen cache. For invalid commands, an error is returned that displays the text of the query and the syntax problems.

Required privilege

This procedure requires the ADMIN privilege.

Syntax

ttSQLCmdQueryPlan(commandID)

Parameters

ttSQLCmdQueryPlan has the optional parameter:

Parameter	Type	Description
`sqlCommandID`	TT_INTEGER for 32-bit systems TT_BIGINT for 64-bit systems	The unique identifier of a SQL command in the TimesTen command cache. If no value is supplied displays the query plan for all valid commands in the TimesTen cache.

Result set

ttSQLQueryPlan returns the result set:

Parameter	Type	Description
`sqlCommandID`	TT_INTEGER NOT NULL for 32-bit systems TT_BIGINT NOT NULL for 64-bit systems	The unique identifier of a command in the TimesTen command cache.
`queryText`	TT_VARCHAR(1024)	The first 1024 characters of the SQL text for the current command.
`step`	TT_INTEGER	The step number of current operation in this run-time query plan.
`level`	TT_INTEGER	The level number of current operation in this run-time query plan.
`operation`	TT_CHAR(31)	The operation name of the current step in this run-time query plan.
`tblName`	TT_CHAR(31)	Name of the table used in this step, if any.
`tblOwnerName`	TT_CHAR(31)	Name of the owner of the table used in this step, if any.
`ixName`	TT_CHAR(31)	Name of the index used in this step, if any.
`indexedPred`	TTVARCHAR(1024)	In this step, if an index is used, the indexed predicate is printed if available. Not all expressions can be printed out and the output may be fragmented and truncated. "..." represents the unfinished portion of the expression.
`nonIndexedPred`	TT_VARCHAR(1024)	In this step, if a non-indexed predicate is used, the non-indexed predicate is printed if available. Not all expressions can be printed out and the output may be fragmented and truncated. "..." represents the unfinished portion of the expression.

Examples

To display the query plan for SQLCmdID 528078576:

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                             , TTUSER                        ,                                , ,  >
< 528078576, <NULL>, 1, 7, RowLkTtreeScan                 , T2                             , TTUSER                        , I2                             , ,  >
< 528078576, <NULL>, 2, 7, RowLkTtreeScan                 , T5                             , TTUSER                        , I2                             , ,  >
< 528078576, <NULL>, 3, 6, NestedLoop                     ,                                ,                                ,                                , ,  >
< 528078576, <NULL>, 4, 6, RowLkTtreeScan                 , T3                             , TTUSER                        , 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                             , TTUSER                        , I2                             , ,  Y1 = X4; >
< 528078576, <NULL>, 10, 1, NestedLoop(Left OuterJoin)     ,                                ,                                ,                                , ,  >
< 528078576, <NULL>, 11, 0, Filter                         ,                                ,                                ,                                , ,  >
13 rows found.

To display query plans for all valid queries, omit the argument for 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                             , TTUSER                        ,                                , ,  >
< 528079360, <NULL>, 1, 3, RowLkTtreeScan                 , T2                             , TTUSER                        , I2                             , , NOT(LIKE( tuf ,abc ,NULL ))  >
< 528079360, <NULL>, 2, 3, RowLkTtreeScan                 , T3                             , TTUSER                        , 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                             , TTUSER                        ,                                , ,  >
< 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                 ,                                ,                                ,                                , ,  >
….. /* more rows here */