Using ttIsql to View and Change Query Optimizer Plans
You can view the query optimizer plans, commands in the SQL command cache, or query plans for commands in the SQL command cache.
Using 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. See The TimesTen Query Optimizer.
-
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.
The example below shows how these commands can be used to change the query optimizer execution plan.
Command> CREATE TABLE T1 (A NUMBER); Command> CREATE TABLE T2 (B NUMBER); Command> CREATE TABLE T3 (C NUMBER); 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 AND A=B; 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 AND A=B; 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 a query against three tables runs and the query optimizer plan is
displayed. 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.
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 application requirements.
Viewing Commands and Explain Plans From the SQL Command Cache
You can view commands and their explain plans.
The following sections describe how to view commands and their explain plans:
View Commands in the SQL Command Cache
The ttIsql
cmdcache command invokes the ttSqlCmdCacheInfo built-in
procedure to display the contents of the TimesTen SQL command cache.
See Displaying Commands Stored in the SQL Command Cache.
If you run the cmdcache command without parameters, the full SQL command cache contents are displayed. Identical to the ttSqlCmdCacheInfo built-in procedure, you can provide a command ID to specify a specific command to be displayed.
In addition, the ttIsql cmdcache command can filter the results so that only those commands that match a particular owner or query text are displayed.
The syntax for the cmdcache command is as follows:
cmdcache [[by {sqlcmdid | querytext | owner}] <query_substring>If you provide the owner parameter, the results are filtered by the owner, identified by the <query_substring>, displayed within each returned command. If you provide the querytext parameter, the results are filtered so that all queries are displayed that contain the substring provided within the <query_substring>. If only the <query_substring> is provided, such as cmdcache <query_substring>, the command assumes to filter the query text by the <query_substring>.
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
explaincommand invokes thettSqlCmdQueryPlanbuilt-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 theexplaincommand instead of calling thettSqlCmdQueryPlanbuilt-in procedure. Both provide the same information, but thettSqlCmdQueryPlanbuilt-in procedure provides the data in a raw data format. See Viewing Query Plans Associated With Commands Stored in the SQL Command Cache. -
If you provide a SQL statement or the history item number, the
explaincommand 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