1.1

Chapter 45
Evaluating Query 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 a Query Plan for an Individual Statement
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.
See Example Query Plan Analysis on page 268 for an example that describes the query plan output.
267