ttSQLCmdQueryPlan

This procedure returns all detailed runtime 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.

Usage in TimesTen Scaleout and TimesTen Classic

This procedure is supported in TimesTen Classic.

TimesTen Scaleout applications can call this built-in procedure.

In TimesTen Scaleout, this procedure runs locally on the element from which it is called.

Related Views

This procedure has these related views.

SYS.GV$SQL_CMD_QUERY_PLAN

SYS.V$SQL_CMD_QUERY_PLAN

Syntax

ttSQLCmdQueryPlan([sqlCmdID])

Parameters

ttSQLCmdQueryPlan has the optional parameter:

Parameter	Type	Description
sqlCmdID	TT_BIGINT	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

ttSQLCmdQueryPlan returns the result set:

Column	Type	Description
sqlCmdID	TT_BIGINT NOT NULL	The unique identifier of a command in the TimesTen command cache.
queryText	TT_VARCHAR(409600)	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(127)	The operation name of the current step in this run-time query plan.
tableName	TT_CHAR(31)	Name of the table used in this step, if any. Using a synonym to specify a table name is not supported.
tableOwnerName	TT_CHAR(31)	Name of the owner of the table used in this step, if any.
indexName	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.
miscellaneous	TT_VARCHAR (65536)	The type of constraint and other information about the constraint. Constraint type can be one of: ForeignKeyInsert - To insert foreign key. ForeignKeyDelete - To delete foreign key. UniqueKeyInsert - To insert unique key. ForeignKeyOrphanChild - To handle case where parent is lost in case of foreign key constraint. ForeignKeyCascadeDelete - To delete corresponding row for cascade delete. ForeignKeySyncCascadeDelete - To delete corresponding row for cascade delete from sync replica.

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 , SAMPLEUSER , , , >
< 528078576, <NULL>, 1, 7, RowLkRangeScan , T2 , SAMPLEUSER , I2 , , >
< 528078576, <NULL>, 2, 7, RowLkRangeScan , T5 , SAMPLEUSER , I2 , , >
< 528078576, <NULL>, 3, 6, NestedLoop , , , , , >
< 528078576, <NULL>, 4, 6, RowLkRangeScan , T3 , SAMPLEUSER , 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 , SAMPLEUSER , 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>, 1, 3, RowLkRangeScan  , T2 
 , SAMPLEUSER  , I2 , , NOT(LIKE( tuf ,abc ,NULL )) >
< 528079360, <NULL>, 2, 3, RowLkRangeScan  , T3  , SAMPLEUSER  , 
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  , SAMPLEUSER  ,  , , >
< 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 */