Viewing Query Plans Associated with Commands Stored in the SQL Command Cache
Use the query plan information to monitor and troubleshoot your queries.
Note:
For more reasons why to use the ttSQLCmdQueryPlan built-in
procedure, see Managing Performance and Troubleshooting Commands.
The ttSQLCmdQueryPlan built-in procedure displays the query plan of a specific statement or all statements in the command cache. It displays the detailed run-time query plans for the cached SQL queries. By default, all query plans are displayed; if you specify the command id taken from the command output, only the query plan for the specified command is displayed.
Note:
If you want to display a query plan for a specific command, you must provide the
command identifier that is displayed with the ttSQLCmdCacheInfo
built-in procedure. See Displaying Commands Stored in the SQL Command Cache.
The plan data displayed when you invoke this built-in procedure is as follows:
-
Command identifier
-
Query text up to 1024 characters
-
Step number of the current operation in the run-time query plan
-
Level number of the current operation in the query plan tree
-
Operation name of current step
-
Name of table used
-
Owner of the table
-
Name of index used
-
If used and available, the index predicate
-
If used and available, the non-indexed predicate
Note:
See Reading Query Plan from the PLAN Table. The source of the data may be different, but the
mapping and understanding of the material is the same as the query plan in the
system PLAN table.
The ttSQLCmdQueryPlan built-in process displays the query plan in a
raw data format. Alternatively, you can run the ttIsql
explain command for a formatted version of this output. See Display Query Plan for Statement in SQL Command Cache in the
Oracle TimesTen In-Memory Database ttIsql User's and Reference Guide.
The following examples show how to display all or a single SQL query plan from the
SQL command cache using the ttSQLCmdQueryPlan built-in procedure.
Example: Displaying all SQL query plans
You can display all SQL query plans associated with commands stored in the command cache with the ttSQLCmdQueryPlan built-in procedure within the ttIsql utility.
The following example shows the output when running the ttSQLCmdQueryPlan built-in procedure without arguments, which displays detailed run-time query plans for all valid queries. For invalid queries, there is no query plan; instead, the query text is displayed.
The query plans are displayed in terse format. To view with the column headings prepended, run vertical on before calling the ttSQLCmdQueryPlan built-in procedure.
Note: For complex expressions, there may be some difficulties in printing out the original expressions.
Command> call ttSQLCmdQueryPlan();
< 528079360, select * from t7 where x7 is not null or exists (select 1 from t2,t3
where not 'tuf' like 'abc'), <NULL>, <NULL>, <NULL>, <NULL>, <NULL>, <NULL>,
<NULL>, <NULL> >
< 528079360, <NULL>, 0, 2, RowLkSerialScan , T7
, PAT , , , >
< 528079360, <NULL>, 1, 3, RowLkRangeScan , T2
, PAT , I2 , , NOT(LIKE( tuf
,abc ,NULL )) >
< 528079360, <NULL>, 2, 3, RowLkRangeScan , T3
, PAT , I2 , , >
< 528079360, <NULL>, 3, 2, NestedLoop ,
, , , , >
< 528079360, <NULL>, 4, 1, NestedLoop(Left OuterJoin) ,
, , , , >
< 528079360, <NULL>, 5, 0, Filter ,
, , , , X7 >
< 527576540, call ttSQLCmdQueryPlan(527973892), <NULL>, <NULL>, <NULL>, <NULL>,
<NULL>, <NULL>, <NULL>, <NULL> >
< 527576540, <NULL>, 0, 0, Procedure Call ,
, , , , >
< 528054656, create table t2(x2 int,y2 int, z2 int), <NULL>, <NULL>, <NULL>,
<NULL>, <NULL>, <NULL>, <NULL>, <NULL> >
< 528066648, insert into t2 select * from t1, <NULL>, <NULL>, <NULL>, <NULL>,
<NULL>, <NULL>, <NULL>, <NULL> >
< 528066648, <NULL>, 0, 0, Insert , T2
, PAT , , , >
< 528013192, select * from t1 where exists (select * from t2 where x1=x2) or
y1=1, <NULL>, <NULL>, <NULL>, <NULL>, <NULL>, <NULL>, <NULL>, <NULL> >
< 528061248, create index i1 on t3(y3), <NULL>, <NULL>, <NULL>, <NULL>, <NULL>,
<NULL>, <NULL>, <NULL> >
< 528070368, call ttOptSetOrder('t3 t4 t2 t1'), <NULL>, <NULL>, <NULL>, <NULL>,
<NULL>, <NULL>, <NULL>, <NULL> >
< 528070368, <NULL>, 0, 0, Procedure Call ,
, , , , >
< 528018856, insert into t2 select * from t1, <NULL>, <NULL>, <NULL>, <NULL>,
<NULL>, <NULL>, <NULL>, <NULL> >
< 527573452, call ttSQLCmdCacheInfo(527973892), <NULL>, <NULL>, <NULL>, <NULL>,
<NULL>, <NULL>, <NULL>, <NULL> >
< 527573452, <NULL>, 0, 0, Procedure Call ,
, , , , >
< 528123000, select * from t1 where x1 = 1 or x1 = (select x2 from t2,t3 where
z2=t3.x3), <NULL>, <NULL>, <NULL>, <NULL>, <NULL>, <NULL>, <NULL>, <NULL> >
< 528123000, <NULL>, 0, 2, RowLkSerialScan , T1
, PAT , , , >
< 528123000, <NULL>, 1, 6, RowLkRangeScan , T2
, PAT , I2 , , >
< 528123000, <NULL>, 2, 6, RowLkRangeScan , T3
, PAT , I2 , , Z2 = X3; >
< 528123000, <NULL>, 3, 5, NestedLoop ,
, , , , >
< 528123000, <NULL>, 4, 4, Materialized View ,
, , , , >
< 528123000, <NULL>, 5, 3, GroupBy ,
, , , , >
< 528123000, <NULL>, 6, 2, Filter ,
, , , , X1 =
colum_name; >
< 528123000, <NULL>, 7, 1, NestedLoop(Left OuterJoin) ,
, , , , >
< 528123000, <NULL>, 8, 0, Filter ,
, , , , X1 = 1; >Example: Displaying a single SQL query plan
You can display any query plan associated with a command by providing the command id of the command as the input for the ttSQLCmdQueryPlan built-in procedure. The single query plan is displayed from within the SQL command cache. If no command id is supplied, the ttSQLCmdCacheInfo built-in procedure displays information about all current commands in the TimesTen cache.
The following example displays the query plan of the command identified by command id of 528078576. It is displayed in terse format; to view with the column headings prepended, run vertical on before calling the ttSQLCmdQueryPlan built-in procedure.
Note: For complex expressions, there are some difficulties to print original expressions.
Command> call ttSQLCmdQueryPlan( 528078576); < 528078576, select * from t1 where 1=2 or (x1 in (select x2 from t2, t5 where y2 in (select y3 from t3)) and y1 in (select x4 from t4)), <NULL>, <NULL>, <NULL>, <NULL>, <NULL>, <NULL>, <NULL>, <NULL> > < 528078576, <NULL>, 0, 4, RowLkSerialScan , T1 , PAT , , , > < 528078576, <NULL>, 1, 7, RowLkRangeScan , T2 , PAT , I2 , , > < 528078576, <NULL>, 2, 7, RowLkRangeScan , T5 , PAT , I2 , , > < 528078576, <NULL>, 3, 6, NestedLoop , , , , , > < 528078576, <NULL>, 4, 6, RowLkRangeScan , T3 , PAT , I1 , ( (Y3=Y2; ) ) , > < 528078576, <NULL>, 5, 5, NestedLoop , , , , , > < 528078576, <NULL>, 6, 4, Filter , , , , , X1 = X2; > < 528078576, <NULL>, 7, 3, NestedLoop(Left OuterJoin) , , , , , > < 528078576, <NULL>, 8, 2, Filter , , , , , > < 528078576, <NULL>, 9, 2, RowLkRangeScan , T4 , PAT , I2 , , Y1 = X4; > < 528078576, <NULL>, 10, 1, NestedLoop(Left OuterJoin) , , , , , > < 528078576, <NULL>, 11, 0, Filter , , , , , > 13 rows found. Command>