Simple example on how to caputure the query execution plan from SQLFire

The example below shows how we can capure the query plan for SQL running in SQLFire. Although any user can run an explain plan you must connect as a peer to show the query execution plan as the data is stored in the SYS.STATEMENTPLANS table.

1. Determine what indexes we currently have in the "APP" schema.

  
sqlf> show indexes in app;
TABLE_NAME          |COLUMN_NAME         |NON_U&|TYPE|ASC&|CARDINA&|PAGES   
----------------------------------------------------------------------------
DEPT                |DEPTNO              |0     |3   |A   |NULL    |NULL    
EMP                 |EMPNO               |0     |3   |A   |NULL    |NULL    
EMP                 |DEPTNO              |1     |3   |A   |NULL    |NULL    
EMP                 |JOB                 |1     |3   |A   |NULL    |NULL    

4 rows selected

2. Run a query explain for an individual SQL statement and display the execution plan. In this example below we connect as a peer client to perform the opertion BUT we only need to conect as a peer client to actually display the execution plan and we can explain our SQL as a regular client user.

  
sqlf> connect peer 'host-data=false;mcast-port=12333';
sqlf> explain select * from emp where job = 'CLERK';
MEMBER_PLAN                                                                                                                     
--------------------------------------------------------------------------------------------------------------------------------
ORIGINATOR 192-168-1-4.tpgi.com.au(2201)<v4>:11255/52510 BEGIN TIME 2012-05-21 20:02:03.659 END TIME 2012-05-21 20:02:03.694
DI&
Slowest Member Plan: 
member   192-168-1-4.tpgi.com.au(2064)<v0>:39756/52450 begin_execution  2012-05-21 20:02:03.664 end_execu&
Fastest Member Plan: 
member   192-168-1-4.tpgi.com.au(2065)<v1>:55134/52451 begin_execution  2012-05-21 20:02:03.664 end_execu&

3 rows selected
sqlf> select STMT_ID, STMT_TEXT from SYS.STATEMENTPLANS;
STMT_ID                             |STMT_TEXT                                                                                                                       
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
00000001-ffff-ffff-ffff-000300000016| select * from emp where job = 'CLERK'                                                                                          

1 row selected
sqlf> explain '00000001-ffff-ffff-ffff-000300000016';
stmt_id   00000001-ffff-ffff-ffff-000300000016 begin_execution  2012-05-21 20:02:03.659 end_execution  2012-05-21 20:02:03.694
QUERY-SCATTER execute_time 31696000 member_node 192-168-1-4.tpgi.com.au(2064)<v0>:39756/52450,192-168-1-4.tpgi.com.au(2065)<v1>:55134/52451
  QUERY-SEND execute_time 397000 member_node 192-168-1-4.tpgi.com.au(2064)<v0>:39756/52450
    QUERY-SEND execute_time 178000 member_node 192-168-1-4.tpgi.com.au(2065)<v1>:55134/52451
      RESULT-RECEIVE execute_time 150000 member_node 192-168-1-4.tpgi.com.au(2065)<v1>:55134/52451
        RESULT-RECEIVE execute_time 35000 member_node 192-168-1-4.tpgi.com.au(2064)<v0>:39756/52450
          SEQUENTIAL-ITERATION returned_rows 7 no_opens 1 execute_time 218000
            RESULT-HOLDER returned_rows 2 no_opens 1 execute_time 39000 member_node 192-168-1-4.tpgi.com.au(2065)<v1>:55134/52451
              RESULT-HOLDER returned_rows 5 no_opens 1 execute_time 33000 member_node 192-168-1-4.tpgi.com.au(2064)<v0>:39756/52450
                DISTRIBUTION-END returned_rows 7 execute_time 28521000
member   192-168-1-4.tpgi.com.au(2065)<v1>:55134/52451 begin_execution  2012-05-21 20:02:03.664 end_execution  2012-05-21 20:02:03.691
QUERY-RECEIVE execute_time 26501000 member_node 192-168-1-4.tpgi.com.au(2201)<v4>:11255/52510
  RESULT-SEND execute_time 35000 member_node 192-168-1-4.tpgi.com.au(2201)<v4>:11255/52510
    RESULT-HOLDER returned_rows 2 no_opens 1 execute_time 802000
      ROWIDSCAN returned_rows 2 no_opens 1 execute_time 62000
        INDEXSCAN returned_rows 2 no_opens 1 execute_time 6173000 scan_qualifiers None scanned_object EMP_JOB_IDX scan_type 
member   192-168-1-4.tpgi.com.au(2064)<v0>:39756/52450 begin_execution  2012-05-21 20:02:03.664 end_execution  2012-05-21 20:02:03.692
QUERY-RECEIVE execute_time 27574000 member_node 192-168-1-4.tpgi.com.au(2201)<v4>:11255/52510
  RESULT-SEND execute_time 25000 member_node 192-168-1-4.tpgi.com.au(2201)<v4>:11255/52510
    RESULT-HOLDER returned_rows 5 no_opens 1 execute_time 885000
      ROWIDSCAN returned_rows 5 no_opens 1 execute_time 108000
        INDEXSCAN returned_rows 5 no_opens 1 execute_time 6682000 scan_qualifiers None scanned_object EMP_JOB_IDX scan_type 
sqlf>

So the steps are as follows:

1. Run an explain for the SQL using the key word "EXPLAIN".
2. Connect as a peer
3. Show the query plan execution for the statement.

For more information on the query execution plan codes visit the link below.

http://pubs.vmware.com/vfabric5/index.jsp?topic=/com.vmware.vfabric.sqlfire.1.0/manage_guide/explain-codes.html