EXPLAIN

Capture or display the query execution plan for a statement.

Syntax

EXPLAIN { sql_statement | 'statement_id' } [ AS XML | AS XMLFRAGMENTS [ EMBED 'xsl-filename' ] ]

Description

When called with a SQL statement, the EXPLAIN command displays the full query execution plan -and stores the plan in the STATEMENTPLANS table. You can also use EXPLAIN with the statement ID from SYS.STATEMENTPLANS to display the stored query plan at a later time. See Query Plan Codes for a description of the GemFire XD query execution plan codes. See Example Query Plan Analysis for an example that describes the query plan output.

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.
Note: Use the gfxd MaximumDisplayWidth command to increase the display width before you execute the EXPLAIN sql_statement command. If you do 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 value.

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

The EXPLAIN command is compatible with regular SQL statements as well as prepared statements.

Include the AS XML option to output the query plan as a single XML document. Use AS XMLFRAGMENTS to output each GemFire XD member's plan as a separate XML form without headers.

When using either AS XML or AS XMLFRAGMENTS, you can include EMBED 'xsl-filename' to embed a fully-qualified XSL filename in the output XML.

Examples

This statement both stores a statement query plan in SYS.STATEMENTPLANS and displays the plan output in gfxd:

gfxd> EXPLAIN select * from flights f, flightavailability fa where f.flight_id = fa.flight_id; 
MEMBER_PLAN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
stmt_id 00000001-ffff-ffff-ffff-00010000052d
SQL_stmt select * from flights f, flightavailability fa where f.flight_id = fa.flight_id
 begin_execution 2013-12-04 16:00:49.733 end_execution 2013-12-04 16:00:49.879 (0 seconds elapsed) 
|   |(2)QUERY-SCATTER (43.19%) execute_time 57.365ms (serialize/deserialize=0.02ms + process=57.345ms + throttle=0.0ms) member_node ward(1335)<v0>:21787
|   |   |---QUERY-SEND (0.15%) execute_time 0.201ms member_node ward(1335)<v0>:21787
|   |   |(5)RESULT-RECEIVE (0.17%) execute_time 0.231ms (serialize/deserialize=0.199ms + process=0.032ms + throttle=0.0ms) member_node ward(1335)<v0>:21787
|   |   |(3)SEQUENTIAL-ITERATION (7.30%) execute_time 9.689ms returned_rows 594 no_opens 1
|   |   |   |(4)RESULT-HOLDER (5.55%) execute_time 7.366ms (serialize/deserialize=0.0ms + process=7.366ms + throttle=0.0ms) returned_rows 594 no_opens 1 member_node ward(1335)<v0>:21787
|   |   |(1)DISTRIBUTION-END (43.64%) execute_time 57.958ms (construct=0.0ms + open=57.144ms + next=10.485ms + close=0.018ms) returned_rows 594
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
member ward(1335)<v0>:21787 begin_execution 2013-12-04 16:00:49.733 end_execution 2013-12-04 16:00:49.789 (0 seconds elapsed) 
|   |(1)QUERY-RECEIVE (39.73%) execute_time 55.891ms (serialize/deserialize=0.0ms + process=55.891ms + throttle=0.0ms) member_node ward(1342)<v1>:14611
|   |   |---RESULT-SEND (0.17%) execute_time 0.233ms member_node ward(1342)<v1>:14611
|   |   |   |(2)RESULT-HOLDER (32.66%) execute_time 45.946ms (serialize/deserialize=8.449ms + process=37.497ms + throttle=0.0ms) returned_rows 594 no_opens 1 member_node NULL
|   |   |   |   |---PROJECTION (0.84%) execute_time 1.18ms returned_rows 594 no_opens 1 node_details SELECT :FLIGHT_ID SEGMENT_NUMBER ORIG_AIRPORT DEPART_TIME DEST_AIRPORT ARRIVE_TIME MEAL FLYING_TIME MILES AIRCRAFT FLIGHT_ID SEGMENT_NUMBER FLIGHT_DATE ECONOMY_SEATS_TAKEN BUSINESS_SEATS_TAKEN FIRSTCLASS_SEATS_TAKEN 
|   |   |   |   |   |(4)NLJOIN (5.17%) execute_time 7.268ms (construct=0.0020ms + open=0.197ms + next=36.767ms + close=0.427ms) returned_rows 594 no_opens 1
|   |   |   |   |   |   |---ROWIDSCAN (2.72%) execute_time 3.825ms returned_rows 518 no_opens 1 node_details FLIGHTAVAILABILITY : 
|   |   |   |   |   |   |   |---CONSTRAINTSCAN (1.62%) execute_time 2.275ms returned_rows 518 no_opens 1 visited_rows 518 scan_qualifiers None next_qualifiers NULL scanned_object APP.6__FLIGHTAVAILABILITY__FLIGHT_ID__SEGMENT_NUMBER:base-table:APP.FLIGHTAVAILABILITY scan_type  node_details NULL
|   |   |   |   |   |   |(5)ROWIDSCAN (4.25%) execute_time 5.974ms (construct=0.0080ms + open=10.029ms + next=10.956ms + close=3.06ms) returned_rows 594 no_opens 518 node_details FLIGHTS : 
|   |   |   |   |   |   |   |(3)INDEXSCAN (12.85%) execute_time 18.082ms (construct=0.0030ms + open=9.579ms + next=5.441ms + close=3.059ms) returned_rows 594 no_opens 518 visited_rows 0 scan_qualifiers None next_qualifiers NULL scanned_object FA_INDEX scan_type  node_details WHERE : ((F.FLIGHT_ID = FA.FLIGHT_ID) and true)


2 rows selected
You can see the statement IDs for stored query execution plans by querying the SYS.STATEMENTPLANS table:
gfxd> select STMT_ID, STMT_TEXT from SYS.STATEMENTPLANS;
STMT_ID                             |STMT_TEXT                                                                                                                
--------------------------------------------------------------------------------------------------------------------------------------------------------------
00000001-ffff-ffff-ffff-00010000052d| select * from flights f, flightavailability fa where f.flight_id = fa.flight_id                                         
00000001-ffff-ffff-ffff-00010000051e| select * from flights f, flightavailability fa where f.flight_id=fa.flight_id and f.flight_id=<?>                       
00000001-ffff-ffff-ffff-00010000050f| select * from flights f, flightavailability fa where f.flight_id=fa.flight_id and f.flight_id=<?>                       

3 rows selected
You can view a stored query execution plan by executing the EXPLAIN command with a statement ID from SYS.STATEMENTPLANS:
gfxd> connect peer 'host-data=false;locators=mylocator[10334]';
gfxd> explain '00000001-ffff-ffff-ffff-00010000052d';
MEMBER_PLAN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
stmt_id 00000001-ffff-ffff-ffff-00010000052d
SQL_stmt select * from flights f, flightavailability fa where f.flight_id = fa.flight_id
 begin_execution 2013-12-04 16:00:49.733 end_execution 2013-12-04 16:00:49.879 (0 seconds elapsed) 
|   |(2)QUERY-SCATTER (43.19%) execute_time 57.365ms (serialize/deserialize=0.02ms + process=57.345ms + throttle=0.0ms) member_node ward(1335)<v0>:21787
|   |   |---QUERY-SEND (0.15%) execute_time 0.201ms member_node ward(1335)<v0>:21787
|   |   |(5)RESULT-RECEIVE (0.17%) execute_time 0.231ms (serialize/deserialize=0.199ms + process=0.032ms + throttle=0.0ms) member_node ward(1335)<v0>:21787
|   |   |(3)SEQUENTIAL-ITERATION (7.30%) execute_time 9.689ms returned_rows 594 no_opens 1
|   |   |   |(4)RESULT-HOLDER (5.55%) execute_time 7.366ms (serialize/deserialize=0.0ms + process=7.366ms + throttle=0.0ms) returned_rows 594 no_opens 1 member_node ward(1335)<v0>:21787
|   |   |(1)DISTRIBUTION-END (43.64%) execute_time 57.958ms (construct=0.0ms + open=57.144ms + next=10.485ms + close=0.018ms) returned_rows 594
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
member ward(1335)<v0>:21787 begin_execution 2013-12-04 16:00:49.733 end_execution 2013-12-04 16:00:49.789 (0 seconds elapsed) 
|   |(1)QUERY-RECEIVE (39.73%) execute_time 55.891ms (serialize/deserialize=0.0ms + process=55.891ms + throttle=0.0ms) member_node ward(1342)<v1>:14611
|   |   |---RESULT-SEND (0.17%) execute_time 0.233ms member_node ward(1342)<v1>:14611
|   |   |   |(2)RESULT-HOLDER (32.66%) execute_time 45.946ms (serialize/deserialize=8.449ms + process=37.497ms + throttle=0.0ms) returned_rows 594 no_opens 1 member_node NULL
|   |   |   |   |---PROJECTION (0.84%) execute_time 1.18ms returned_rows 594 no_opens 1 node_details SELECT :FLIGHT_ID SEGMENT_NUMBER ORIG_AIRPORT DEPART_TIME DEST_AIRPORT ARRIVE_TIME MEAL FLYING_TIME MILES AIRCRAFT FLIGHT_ID SEGMENT_NUMBER FLIGHT_DATE ECONOMY_SEATS_TAKEN BUSINESS_SEATS_TAKEN FIRSTCLASS_SEATS_TAKEN 
|   |   |   |   |   |(4)NLJOIN (5.17%) execute_time 7.268ms (construct=0.0020ms + open=0.197ms + next=36.767ms + close=0.427ms) returned_rows 594 no_opens 1
|   |   |   |   |   |   |---ROWIDSCAN (2.72%) execute_time 3.825ms returned_rows 518 no_opens 1 node_details FLIGHTAVAILABILITY : 
|   |   |   |   |   |   |   |---CONSTRAINTSCAN (1.62%) execute_time 2.275ms returned_rows 518 no_opens 1 visited_rows 518 scan_qualifiers None next_qualifiers NULL scanned_object APP.6__FLIGHTAVAILABILITY__FLIGHT_ID__SEGMENT_NUMBER:base-table:APP.FLIGHTAVAILABILITY scan_type  node_details NULL
|   |   |   |   |   |   |(5)ROWIDSCAN (4.25%) execute_time 5.974ms (construct=0.0080ms + open=10.029ms + next=10.956ms + close=3.06ms) returned_rows 594 no_opens 518 node_details FLIGHTS : 
|   |   |   |   |   |   |   |(3)INDEXSCAN (12.85%) execute_time 18.082ms (construct=0.0030ms + open=9.579ms + next=5.441ms + close=3.059ms) returned_rows 594 no_opens 518 visited_rows 0 scan_qualifiers None next_qualifiers NULL scanned_object FA_INDEX scan_type  node_details WHERE : ((F.FLIGHT_ID = FA.FLIGHT_ID) and true)


2 rows selected