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

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

Articles Related

How to

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.

Command

explain

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

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:

ttSqlCmdQueryPlan

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

call ttSqlCmdQueryPlan();

call ttSqlCmdQueryPlan(sqlcmdid);

Support

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.