1.1

NLJOIN (2.68%) execute_time 0.378 ms returned_rows 14 no_opens 1
TABLESCAN (53.09%) execute_time 7.482 ms returned_rows 1 no_opens
1 scan_qualifiers Column[0][0] Id: FLIGHT_ID Operator: = AA1116 Ordered
nulls: false Unknown return value: false scanned_object APP.FLIGHTS
scan_type HEAP
ROWIDSCAN (1.02%) execute_time 0.144 ms returned_rows 14 no_opens
1 node_details FLIGHTAVAILABILITY :
CONSTRAINTSCAN (43.19%) execute_time 6.087 ms returned_rows 14
no_opens 1 scan_qualifiers None 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)
The top of the query plan output displays the statement ID and the original text of the query itself. The body
of the plan is divided into two main sections identified by the query plan codes QUERY-SCATTER and
QUERY-RECEIVE. Each section contains indented rows showing nested tasks. Because each task feeds
into the task above it, you should read the query plan starting with the bottom, most-nested task in each
section. Individual task rows show the task execution time in milliseconds, as well as the overall percentage
of time spent completing that task relative to all of the tasks in the section, combined.
For example, QUERY-SCATTER includes all of the tasks associated with delivering the query from the
query coordinator to one or more data stores in the cluster, and then receiving and processing results on the
query coordinator. This example uses a single data store member to provide all results, so very little time is
spent on the query coordinator after the DISTRIBUTION-END phase (which represents 97% of the time
required to distribute, receive, and process the results). When data is distributed across multiple data stores
(for partitioned and/or replicated tables) additional time will be spent on the query coordinator to merge the
results from each data store.
The QUERY-RECEIVE section of the plan describes the query processing tasks that a data store member
takes in order to execute a query. This header displays the member ID and process ID of the data store, as
well as the total aggregate time for all of the subordinate tasks. If the query is distributed to multiple data
store members, then each member will display a separate QUERY-RECEIVE section. Each section should
be identical, unless tables are partitioned across different sets of machines of VMs.
The example shows that a nested loop join (NLJOIN) is performed in order to produce the results. NLJOIN
always has two nested subtasks that represent the outer and inner tables in the join. In this example, a
TABLESCAN is first performed on the FLIGHTS table, which holds the foreign key FLIGHT_ID. The
TABLESCAN returns a single row. The inner table then uses the results of the TABLESCAN in a
CONSTRAINTSCAN performed on the FLIGHTAVAILABILITY table.
Table scans are very expensive operations in query processing, and even this quick query of a small data set
shows that the majority of the time (53% of the query execution time) was spent scanning the FLIGHTS
table. The remaining steps show how creating an index changes the query plan and improves performance.
8. To avoid the costly table scan used in the sample query, execute the following command to create an index
on the FLIGHT_ID column of the FLIGHTS table:
CREATE INDEX fa_index ON flights (flight_id);
9. Re-run the query, creating a new query plan. Then examine the new plan:
explain select * from flights f, flightavailability fa where
f.flight_id=fa.flight_id and f.flight_id='AA1116';
select STMT_ID, STMT_TEXT from SYS.STATEMENTPLANS;
explain '00000001-ffff-ffff-ffff-00010000051e';
You should receive output similar to:
stmt_id 00000001-ffff-ffff-ffff-00010000051e SQL_stmt select * from
flights f, flightavailability fa where f.flight_id=fa.flight_id and
f.flight_id=<?> begin_execution 2013-01-30 15:33:46.857 end_execution
2013-01-30 15:33:46.878
vFabric SQLFire User's Guide270
Managing and Monitoring vFabric SQLFire