EXPLAIN

Capture or display the query execution plan for a statement.

Syntax

EXPLAIN { sql_statement | statement_id }

Description

When called with a SQL statement, the EXPLAIN command captures the query execution plan for the statement and stores it in the STATEMENTPLANS. When called with a statement ID from SYS.STATEMENTPLANS, the EXPLAIN command displays the full query plan for the statement. See SQLFire Query Execution Plan Codes for a description of the SQLFire query execution plan codes.

Note: You can capture query execution plans using either a thin client or peer client connection. However, you can display a stored query execution plan only when using a peer client connection.

As an alternative, you can capture query plans for all of the statements that are executed in a specific connection. See Capture Query Execution Plans for All Statements.

Example

This statement captures a query plan in SYS.STATEMENTPLANS:

EXPLAIN select * from flights f, flightavailability fa where f.flight_id = fa.flight_id; 
You can see the statement ID for the query execution plan by querying the system table:
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
To view the actual query execution plan, connect as a peer to the SQLFire system and execute the EXPLAIN command with the statement ID:
sqlf> connect peer 'host-data=false;locators=mylocator[10334]';
sqlf> explain '00000001-ffff-ffff-ffff-000000000022';
member   10.0.1.26(52578)<v1>:32828/54810 begin_execution  2012-04-09 15:01:29.478 end_execution  2012-04-09 15:01:29.545
QUERY-RECEIVE execute_time 66323000 member_node 10.0.1.26(52590)<v2>:27914/54823
  RESULT-SEND execute_time 127000 member_node 10.0.1.26(52590)<v2>:27914/54823
    RESULT-HOLDER returned_rows 286 no_opens 1 execute_time 21378000
      NLJOIN returned_rows 286 no_opens 1 execute_time 2364000
        TABLESCAN returned_rows 261 no_opens 1 execute_time 3721000 scan_qualifiers None scanned_object FLIGHTS scan_type HEAP
        HASHSCAN returned_rows 572 no_opens 261 execute_time 16700000 scan_qualifiers None scanned_object FLIGHTAVAILABILITY scan_type HEAP
member   10.0.1.26(52590)<v2>:27914/54823 begin_execution  1969-12-31 16:00:00.0 end_execution  2012-04-09 15:01:29.726
QUERY-SCATTER execute_time 70690000 member_node 10.0.1.26(52578)<v1>:32828/54810,10.0.1.26(52590)<v2>:27914/54823
  QUERY-SEND execute_time 294000 member_node 10.0.1.26(52578)<v1>:32828/54810
    QUERY-SEND execute_time 36271000 member_node 10.0.1.26(52590)<v2>:27914/54823
      RESULT-RECEIVE execute_time 27000 member_node 10.0.1.26(52590)<v2>:27914/54823
        RESULT-RECEIVE execute_time 320000 member_node 10.0.1.26(52578)<v1>:32828/54810
          SEQUENTIAL-ITERATION returned_rows 594 no_opens 1 execute_time 85105000
            RESULT-HOLDER returned_rows 308 no_opens 1 execute_time 843000 member_node 10.0.1.26(52590)<v2>:27914/54823
              RESULT-HOLDER returned_rows 286 no_opens 1 execute_time 6681000 member_node 10.0.1.26(52578)<v1>:32828/54810
                DISTRIBUTION-END returned_rows 594 execute_time 115540000
Local plan:
member   10.0.1.26(52590)<v2>:27914/54823 begin_execution  2012-04-09 15:01:29.496 end_execution  2012-04-09 15:01:29.612
NLJOIN returned_rows 308 no_opens 1 execute_time 2803000
  TABLESCAN returned_rows 281 no_opens 1 execute_time 3947000 scan_qualifiers None scanned_object FLIGHTS scan_type HEAP
  HASHSCAN returned_rows 616 no_opens 281 execute_time 20737000 scan_qualifiers None scanned_object FLIGHTAVAILABILITY scan_type HEAP