Capture the Query Execution Plan for Individual Statements

SQLFire provides the EXPLAIN command to display the query execution plan for a single statement.

Procedure
  1. Establish a peer client connection to the SQLFire cluster. For example:
    sqlf
    connect peer 'host-data=false;mcast-port=12777';
  2. Use the EXPLAIN command with a SQL statement to capture the query execution plan for that statement. For example:
    explain select * from flights f, flightavailability fa where f.flight_id = fa.flight_id; 
  3. Query plans for statements that you prefix with the EXPLAIN command are stored in the SYS.STATEMENTPLANS table for later use. To display the query plan for the statement, query SYS.STATEMENTPLANS to determine the statement ID, and then use that statement ID again with the EXPLAIN command. For example:
    sqlf> select STMT_ID, STMT_TEXT from SYS.STATEMENTPLANS;
    STMT_ID                             |STMT_TEXT
    ---------------------------------------------------------------------------------------------------------------------------------------------------------------------
    00000001-ffff-ffff-ffff-000000000022| select * from flights f, flightavailability fa where f.flight_id = fa.flight_id and f.flight_id = 'AA1116'                     
    
    1 row selected
    
    sqlf> explain '00000001-ffff-ffff-ffff-000000000022';
    Note: You must connect using a peer client connection to view captured query plans.
  4. Exit sqlf:
    exit;

See SQLFire Query Execution Plan Codes for information about individual codes displayed in the query plan output.