Timesten - How to explain a query plan ? (Explain command|ttSqlCmdQueryPlan)

> Database > (TimesTen|TT) (In-Memory Database|IMDB)

1 - About

To explain a query and show a query plan, you can use:

  • The ttIsql explain command for an individual SQL command.
  • or the ttSqlCmdQueryPlan built-in procedure

3 - How to

3.1 - Get the sqlcmdid

you get it from command cache (the query cache)

Command> cmdcache;
....................
< 62958908272, 1, 3, 3, 0, 1, 3488, ORACLE                         , select * from all_users >
....................
< 62958919720, 1, 2, 2, 0, 1, 2408, ORACLE                         , CALL ttOptGetFlag ('PassThrough') >
< 62958917216, 1, 2, 2, 0, 1, 2328, ORACLE                         , CALL ttOptGetFlag () >
15 rows found.
Advertising

4 - Command

4.1 - explain

4.1.1 - Syntax

explain [plan for] {[<CONNID>.]ttisqlcmdid | sqlcmdid <sqlcmdid> | <stmt> | !<historyitem> }

where

  • stmt is a explain for a statement
  • sqlcmdid is the ID of a SQL in the cache

4.1.2 - Example

  • with a statement
Command> EXPLAIN SELECT * FROM ALL_USERS;

Query Optimizer Plan:

  STEP:                1
  LEVEL:               1
  OPERATION:           TblLkRangeScan
  TBLNAME:             SYS.USER$
  IXNAME:              USER$.I_USER
  INDEXED CONDITION:   <NULL>
  NOT INDEXED:         U.TYPE# = 1
  • wit a sqlcmdid
Command> EXPLAIN sqlcmdid 62958925456;

Query Optimizer Plan:
 Query Text: select * from all_users

  STEP:             1
  LEVEL:            2
  OPERATION:        TblLkRangeScan
  TABLENAME:        USER$
  TABLEOWNERNAME:   SYS
  INDEXNAME:        USER$.I_USER
  INDEXEDPRED:
  NONINDEXEDPRED:   TYPE# =


  STEP:             2
  LEVEL:            1
  OPERATION:        Project
  TABLENAME:
  TABLEOWNERNAME:
  INDEXNAME:
  INDEXEDPRED:
  NONINDEXEDPRED:

4.2 - ttSqlCmdQueryPlan

The ttSqlCmdQueryPlan built-in procedure can also be used for this purpose.

call ttSqlCmdQueryPlan();

or

call ttSqlCmdQueryPlan(sqlcmdid);

Advertising

5 - Support

5.1 - Syntax error

 
Command> EXPLAIN SQLCMDID 62958925456;
 1001: Syntax error in SQL statement before or at: "SQLCMDID", character position: 1
SQLCMDID 62958925456
^^^^^^^^
The command failed.

SQLCMDID must be in lowercase.

6 - Documentation / Reference