1.0

Chapter 45
Evaluating Query Execution Plans and
Query Statistics
SQLFire can capture query execution plans to determine whether a given query will execute locally, or will execute
in parallel across members of the cluster.
You can also use query execution plans to determine where the system spends time in executing a particular query.
You can generate Query plans on any peer or thin client connection that enables query plans.
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.
259