Display Query Plan for Statement in SQL Command Cache

The ttIsql explain command displays the query plan for an individual command.

• If you provide a command ID from the SQL command cache, the explain command invokes the ttSqlCmdQueryPlan built-in procedure to display the query plan for an individual command in the TimesTen SQL command cache. If you want the explain plan displayed in a formatted method, run the explain command instead of calling the ttSqlCmdQueryPlan built-in procedure. Both provide the same information, but the ttSqlCmdQueryPlan built-in procedure provides the data in a raw data format.

• If you provide a SQL statement or the history item number, the explain command compiles the SQL statements necessary to display the explain plan for this particular SQL statement.

The syntax for the explain command is as follows:

explain [plan for] {[<Connid>.]<ttisqlcmdid> | sqlcmdid <sqlcmdid> | <sqlstmt> 
| !<historyitem>}

Identical to the ttSqlCmdQueryPlan built-in procedure, you can provide a command ID to specify a specific command to be displayed. The command ID can be retrieved with the cmdcache command, as described in View Commands in the SQL Command Cache.

The following example provides an explain plan for command ID 38001456:

Command>EXPLAIN SQLCMDID 38001456;
 
Query Optimizer Plan:
 Query Text: select * from all_objects where object_name = 'DBMS_OUTPUT'
 
  STEP:             1
  LEVEL:            12
  OPERATION:        TblLkRangeScan
  TABLENAME:        OBJ$
  TABLEOWNERNAME:   SYS
  INDEXNAME:        USER$.I_OBJ
  INDEXEDPRED:      
  NONINDEXEDPRED:   (RTRIM( NAME ))  = DBMS_OUTPUT;NOT( 10 = TYPE#) ;
(  FLAGS ^ 128 = 0) ;
 
 
  STEP:             2
  LEVEL:            12
  OPERATION:        RowLkRangeScan
  TABLENAME:        OBJAUTH$
  TABLEOWNERNAME:   SYS
  INDEXNAME:        OBJAUTH$.I_OBJAUTH1
  INDEXEDPRED:      ( (GRANTEE#=1 )  OR (GRANTEE#=10 ) )  AND ( (PRIVILEGE#=8 ) )
  NONINDEXEDPRED:   OBJ# = OBJ#;
 
 
  STEP:             3
  LEVEL:            11
  OPERATION:        NestedLoop(Left OuterJoin)
  TABLENAME:        
  TABLEOWNERNAME:   
  INDEXNAME:        
  INDEXEDPRED:      
  NONINDEXEDPRED: 
...
 STEP:             21
  LEVEL:            1
  OPERATION:        Project
  TABLENAME:        
  TABLEOWNERNAME:   
  INDEXNAME:        
  INDEXEDPRED:      
  NONINDEXEDPRED:   
 
Command> 

In addition, the ttIsql explain command can generate an explain plan for any SQL query you provide. For example, the following shows the explain plan for the SQL query: "SELECT * FROM employees;"

Command>EXPLAIN SELECT * FROM employees;
 
Query Optimizer Plan:
 
  STEP:                1
  LEVEL:               1
  OPERATION:           TblLkRangeScan
  TBLNAME:             EMPLOYEES
  IXNAME:              EMP_NAME_IX
  INDEXED CONDITION:   <NULL>
  NOT INDEXED:         <NULL>

You can also retrieve explain plans based upon the command history. The following example shows how you explain a previously run SQL statement using the history command ID:

Command>SELECT * FROM all_objects WHERE object_name = 'DBMS_OUTPUT';
< SYS, DBMS_OUTPUT, <NULL>, 241, <NULL>, PACKAGE, 2009-10-13 10:41:11, 2009-10-13 
10:41:11, 2009-10-13:10:41:11, VALID, N, N, N, 1, <NULL> >
< PUBLIC, DBMS_OUTPUT, <NULL>, 242, <NULL>, SYNONYM, 2009-10-13 10:41:11, 
2009-10-13 10:41:11, 2009-10-13:10:41:11, INVALID, N, N, N, 1, <NULL> >
< SYS, DBMS_OUTPUT, <NULL>, 243, <NULL>, PACKAGE BODY, 2009-10-13 10:41:11, 
2009-10-13 10:41:11, 2009-10-13:10:41:11, VALID, N, N, N, 2, <NULL> >
3 rows found.
Command>HISTORY;
1     connect "DSN=cache";
2     help cmdcache;
3     cmdcache;
4     explain select * from dual;
5     select * from all_objects where object_name = 'DBMS_OUTPUT';
Command>EXPLAIN !5;
 
Query Optimizer Plan:
 
  STEP:                1
  LEVEL:               10
  OPERATION:           TblLkRangeScan
  TBLNAME:             SYS.OBJ$
  IXNAME:              USER$.I_OBJ
  INDEXED CONDITION:   <NULL>
  NOT INDEXED:         O.FLAGS & 128 = 0 AND CAST(RTRIM (O.NAME) AS VARCHAR2(30 
BYTE) INLINE) = 'DBMS_OUTPUT' AND O.TYPE# <> 10
 
  STEP:                2
  LEVEL:               10
  OPERATION:           RowLkRangeScan
  TBLNAME:             SYS.OBJAUTH$
  IXNAME:              OBJAUTH$.I_OBJAUTH1
  INDEXED CONDITION:   (OA.GRANTEE# = 1 OR OA.GRANTEE# = 10) AND OA.PRIVILEGE# = 8
  NOT INDEXED:         OA.OBJ# = O.OBJ#
 
  STEP:                3
  LEVEL:               9
  OPERATION:           NestedLoop(Left OuterJoin)
  TBLNAME:             <NULL>
  IXNAME:              <NULL>
  INDEXED CONDITION:   <NULL>
  NOT INDEXED:         <NULL>
 
  STEP:                4
  LEVEL:               9
  OPERATION:           TblLkRangeScan
  TBLNAME:             SYS.OBJAUTH$
  IXNAME:              OBJAUTH$.I_OBJAUTH1
  INDEXED CONDITION:   (OBJAUTH$.GRANTEE# = 1 OR OBJAUTH$.GRANTEE# = 10) AND 
(OBJAUTH$.PRIVILEGE# = 2 OR OBJAUTH$.PRIVILEGE# = 3 OR OBJAUTH$.PRIVILEGE# = 4 OR 
OBJAUTH$.PRIVILEGE# = 5 OR OBJAUTH$.PRIVILEGE# = 8)
  NOT INDEXED:         O.OBJ# = OBJAUTH$.OBJ#
... 
  STEP:                19
  LEVEL:               1
  OPERATION:           NestedLoop(Left OuterJoin)
  TBLNAME:             <NULL>
  IXNAME:              <NULL>
  INDEXED CONDITION:   <NULL>
  NOT INDEXED:         O.OWNER# = 1 OR (O.TYPE# IN (7,8,9) AND (NOT( ISNULLROW 
(SYS.OBJAUTH$.ROWID)) OR NOT( ISNULLROW (SYS.SYSAUTH$.ROWID)))) OR (O.TYPE# IN 
(1,2,3,4,5) AND NOT( ISNULLROW (SYS.SYSAUTH$.ROWID))) OR (O.TYPE# = 6 AND NOT( 
ISNULLROW (SYS.SYSAUTH$.ROWID))) OR (O.TYPE# = 11 AND NOT( ISNULLROW 
(SYS.SYSAUTH$.ROWID))) OR (O.TYPE# NOT IN (7,8,9,11) AND NOT( ISNULLROW 
(SYS.OBJAUTH$.ROWID))) OR (O.TYPE# = 28 AND NOT( ISNULLROW (SYS.SYSAUTH$.ROWID))) 
OR (O.TYPE# = 23 AND NOT( ISNULLROW (SYS.SYSAUTH$.ROWID))) OR O.OWNER# = 10