Capture a Query Plan for an Individual Statement

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

Procedure
  1. Establish a peer client connection to the GemFire XD cluster. For example:
    gfxd> connect peer 'host-data=false;mcast-port=12777';
  2. Increase the gfxd maximum display width in order to show the full query plan output. For example:
    gfxd> maximumdisplaywidth 10000;
  3. Use the EXPLAIN command with a SQL statement to capture the query execution plan for that statement. For example:
    gfxd> explain select * from flights f, flightavailability fa where f.flight_id = fa.flight_id; 
    MEMBER_PLAN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     
    -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    stmt_id 00000001-ffff-ffff-ffff-00010000000d
    SQL_stmt select * from flights f, flightavailability fa where f.flight_id = fa.flight_id
     begin_execution 2013-12-04 14:48:46.377 end_execution 2013-12-04 14:48:46.565 (0 seconds elapsed) 
    |   |(1)QUERY-SCATTER (44.66%) execute_time 96.351ms (serialize/deserialize=0.766ms + process=95.585ms + throttle=0.0ms) member_node ward(1251)<v0>:42531
    |   |   |(5)QUERY-SEND (0.80%) execute_time 1.726ms (serialize/deserialize=0.766ms + process=0.96ms + throttle=0.0ms) member_node ward(1251)<v0>:42531
    |   |   |---RESULT-RECEIVE (0.12%) execute_time 0.262ms member_node ward(1251)<v0>:42531
    |   |   |(3)SEQUENTIAL-ITERATION (6.14%) execute_time 13.257ms returned_rows 594 no_opens 1
    |   |   |   |(4)RESULT-HOLDER (4.96%) execute_time 10.702ms (serialize/deserialize=0.0ms + process=10.702ms + throttle=0.0ms) returned_rows 594 no_opens 1 member_node ward(1251)<v0>:42531
    |   |   |(2)DISTRIBUTION-END (43.31%) execute_time 93.444ms (construct=0.0ms + open=92.535ms + next=14.111ms + close=0.055ms) returned_rows 594
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         
    member ward(1251)<v0>:42531 begin_execution 2013-12-04 14:48:46.382 end_execution 2013-12-04 14:48:46.471 (0 seconds elapsed) 
    |   |(1)QUERY-RECEIVE (42.25%) execute_time 88.656ms (serialize/deserialize=0.0ms + process=88.656ms + throttle=0.0ms) member_node ward(1259)<v1>:35008
    |   |   |---RESULT-SEND (0.12%) execute_time 0.242ms member_node ward(1259)<v1>:35008
    |   |   |   |(2)RESULT-HOLDER (30.26%) execute_time 63.501ms (serialize/deserialize=11.963ms + process=51.538ms + throttle=0.0ms) returned_rows 594 no_opens 1 member_node NULL
    |   |   |   |   |(5)NLJOIN (3.90%) execute_time 8.192ms (construct=0.0020ms + open=1.268ms + next=55.412ms + close=0.689ms) returned_rows 594 no_opens 1
    |   |   |   |   |   |---ROWIDSCAN (2.27%) execute_time 4.766ms returned_rows 542 no_opens 1 node_details FLIGHTS : 
    |   |   |   |   |   |   |---INDEXSCAN (1.60%) execute_time 3.356ms returned_rows 542 no_opens 1 visited_rows 542 scan_qualifiers None next_qualifiers NULL scanned_object DESTINDEX scan_type  node_details 
    |   |   |   |   |   |(4)ROWIDSCAN (3.95%) execute_time 8.298ms (construct=0.0060ms + open=23.621ms + next=12.242ms + close=5.237ms) returned_rows 594 no_opens 542 node_details FLIGHTAVAILABILITY : 
    |   |   |   |   |   |   |(3)CONSTRAINTSCAN (15.64%) execute_time 32.811ms (construct=0.0030ms + open=23.038ms + next=4.535ms + close=5.235ms) returned_rows 594 no_opens 542 visited_rows 6 scan_qualifiers None next_qualifiers NULL scanned_object APP.6__FLIGHTAVAILABILITY__FLIGHT_ID__SEGMENT_NUMBER:base-table:APP.FLIGHTAVAILABILITY scan_type  node_details WHERE : ((F.FLIGHT_ID = FA.FLIGHT_ID) and true)
    
    
    2 rows selected
    
    Note: If you did not increase the default MAXIMUMDISPLAYWIDTH value, the EXPLAIN play output is truncated before any query plan codes appear. If this occurs, re-run the EXPLAIN command, specifying the statement ID (the STMT_ID value stored in SYS.STATEMENTPLANS) of the plan you just created. For example:
    gfxd> select STMT_ID, STMT_TEXT from SYS.STATEMENTPLANS;
    STMT_ID                             |STMT_TEXT
    ---------------------------------------------------------------------------------------------------------------------------------------------------------------------
    00000001-ffff-ffff-ffff-00010000000d| select * from flights f, flightavailability fa where f.flight_id = fa.flight_id                                                                                          
    
    1 row selected
    
    gfxd> explain '00000001-ffff-ffff-ffff-00010000000d';
    You must connect using a peer client connection to view captured query plans.

See Example Query Plan Analysis for an example that describes the query plan output.

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