Use the showplan Command

The built-in showplan command is used to display the query optimizer plans used by TimesTen for processing queries.

In addition, ttIsql contains built-in query optimizer hint commands for altering the query optimizer plan. By using the showplan command in conjunction with the ttIsql commands summarized below, the optimum execution plan can be designed.

• optprofile - Displays the current optimizer hint settings and join order.

• setjoinorder - Sets the join order.

• setuseindex - Sets the index hint.

• tryhash - Enables or disables the use of hash indexes.

• trymergejoin - Enables or disables merge joins.

• trynestedloopjoin - Enables or disables nested loop joins.

• tryserial - Enables or disables serial scans.

• trytmphash - Enables or disables the use of temporary hash indexes.

• trytmptable - Enables or disables the use of an intermediate results table.

• trytmprange - Enables or disables the use of temporary range indexes.

• tryrange - Enables or disables the use of range indexes.

• tryrowid - Enables or disables the use of rowid scans.

• trytbllocks - Enables or disables the use of table locks.

• unsetjoinorder - Clears the join order.

• unsetuseindex - Clears the index hint.

When using the showplan command and the query optimizer hint commands, the autocommit feature must be turned off. Use the ttIsql autocommit command to turn off autocommit.

This example shows how these commands can be used to change the query optimizer execution plan.

Command>CREATE TABLE T1 (A NUMBER);
Table created.
Command>CREATE TABLE T2 (B NUMBER);
Table created.
Command>CREATE TABLE T3 (C NUMBER);
Table created.
Command>INSERT INTO T1 VALUES (3);
1 row inserted.
Command>INSERT INTO T2 VALUES (3);
1 row inserted.
Command>INSERT INTO T3 VALUES (3);
1 row inserted.
Command>INSERT INTO T1 VALUES (4);
1 row inserted.
Command>INSERT INTO T2 VALUES (5);
1 row inserted.
Command>INSERT INTO T3 VALUES (6);
1 row inserted.
Command>autocommit 0;
Command>showplan;
Command>SELECT * FROM T1, T2, T3 WHERE A=B AND B=C;

Query Optimizer Plan:
 
  STEP:                1
  LEVEL:               3
  OPERATION:           TblLkSerialScan
  TBLNAME:             T2
  IXNAME:              <NULL>
  INDEXED CONDITION:   <NULL>
  NOT INDEXED:         <NULL>
 
 
  STEP:                2
  LEVEL:               3
  OPERATION:           TblLkSerialScan
  TBLNAME:             T3
  IXNAME:              <NULL>
  INDEXED CONDITION:   <NULL>
  NOT INDEXED:         T2.B = T3.C
 
 
  STEP:                3
  LEVEL:               2
  OPERATION:           NestedLoop
  TBLNAME:             <NULL>
  IXNAME:              <NULL>
  INDEXED CONDITION:   <NULL>
  NOT INDEXED:         <NULL>
 
 
  STEP:                4
  LEVEL:               2
  OPERATION:           TblLkSerialScan
  TBLNAME:             T1
  IXNAME:              <NULL>
  INDEXED CONDITION:   <NULL>
  NOT INDEXED:         T1.A = T2.B AND T1.A = T2.B
 
 
  STEP:                5
  LEVEL:               1
  OPERATION:           NestedLoop
  TBLNAME:             <NULL>
  IXNAME:              <NULL>
  INDEXED CONDITION:   <NULL>
  NOT INDEXED:         <NULL>
 
< 3, 3, 3 >
1 row found.

Command>trytbllocks 0;
Command>tryserial 0;
Command>SELECT * FROM T1, T2, T3 WHERE A=B AND B=C;

Query Optimizer Plan:
 
  STEP:                1
  LEVEL:               3
  OPERATION:           TmpRangeScan
  TBLNAME:             T2
  IXNAME:              <NULL>
  INDEXED CONDITION:   <NULL>
  NOT INDEXED:         <NULL>
 
 
  STEP:                2
  LEVEL:               3
  OPERATION:           RowLkSerialScan
  TBLNAME:             T3
  IXNAME:              <NULL>
  INDEXED CONDITION:   <NULL>
  NOT INDEXED:         T2.B = T3.C
 
 
  STEP:                3
  LEVEL:               2
  OPERATION:           NestedLoop
  TBLNAME:             <NULL>
  IXNAME:              <NULL>
  INDEXED CONDITION:   <NULL>
  NOT INDEXED:         <NULL>
 
 
  STEP:                4
  LEVEL:               2
  OPERATION:           RowLkSerialScan
  TBLNAME:             T1
  IXNAME:              <NULL>
  INDEXED CONDITION:   <NULL>
  NOT INDEXED:         T1.A = T2.B AND T1.A = T2.B
 
 
  STEP:                5
  LEVEL:               1
  OPERATION:           NestedLoop
  TBLNAME:             <NULL>
  IXNAME:              <NULL>
  INDEXED CONDITION:   <NULL>
  NOT INDEXED:         <NULL>
 
< 3, 3, 3 >
1 row found.

In this example, the query optimizer plan is displayed for a Select query. The first version of the query simply uses the query optimizer's default execution plan. However, in the second version, the trytbllocks and tryserial ttIsql built-in hint commands have been used to alter the query optimizer's plan. Instead of using serial scans and nested loop joins, the second version of the query uses temporary index scans, serial scans, and nested loops. The second version uses row lock instead of table lock because the optimizer hint for table lock is turned off.

In this way, the showplan command in conjunction with ttIsql's built-in query optimizer hint commands can be used to quickly determine which execution plan should be used to meet the application requirements. The query optimizer generates the best query plan based on the statistics. When you observe the bad query performance, then you need to use the optimizer hints to change the query plan.