|Managing and Monitoring GemFire XD / Evaluating Query Execution Plans|
GemFire XD may add the following codes to a generated query plan. All times reported in the plan are in milliseconds.
The following codes apply to the query coordinator (the GemFire XD member that distributes a query to other data store members and processes the results).
|Query Plan Code||Description|
|DISTRIBUTION-END||The total time spent processing the query on the data store members.|
|GROUPED-ITERATION||The time spent grouping rows on the query coordinator. This code is displayed only if the query contains an GROUP BY clause and the query was processed over multiple table partitions.|
|ORDERED-ITERATION||The time spent resorting rows on the query coordinator. This code is displayed only if the query contains an ORDER BY clause and the query was processed over multiple table partitions.|
|OUTER-JOIN-ITERATION||The time spent evaluating outer joins after receiving rows from other members.|
|QUERY-SCATTER||QUERY-SCATTER is the top-level code that organizes all tasks associated with sending queries from the query coordinator and receiving and processing results on the coordinator. The execute time reported for QUERY-SCATTER is the total, aggregate time of all the subordinate tasks. QUERY-SCATTER lists the GemFire XD members to which the query was distributed; it does not list the query coordinator member.|
|QUERY-SEND||The network time required to send the query, as well as the target member. This is an "OUT" operation from the perspective of the query coordinator. A separate QUERY-SEND code is displayed for each data store that receives the query.|
|REGION-GET||An atomic "get" operation in the GemFire component.|
|REGION-GETALL||An atomic "getall" operation in the GemFire component.|
|REGION-PUT||An atomic "put" operation in the GemFire component.|
|REGION-PUTALL||An atomic "putall" operation in the GemFire component.|
|RESULT-RECEIVE||The network time required to receive the results from data stores back to the query coordinator. This is an "IN" operation from the perspective of the query coordinator. A separate RESULT-RECEIVE code is displayed for each data store that processes the query.|
|RESULT-HOLDER||Shows the total time spent batching up
results from the target member. This is an "IN" operation from the perspective
of the query coordinator. RESULT-HOLDER displays the number of rows returned
from the specified data store member. A separate RESULT-HOLDER code is
displayed for each data store that processes the query.
RESULT-HOLDER is a blocking operation that is connected to SEQUENTIAL-ITERATION.
|ROUNDROBIN-ITERATION||The time required to merge results from an n-way merge.|
|ROW-COUNT-ITERATION||The time spent returning rows specified in a FETCH NEXT clause. (See OFFSET and FETCH FIRST Clauses.)|
|SEQUENTIAL-ITERATION||SEQUENTIAL-ITERATION is an operator that gathers results and sends them to the client as the client requests, one block at a time. This code shows the total aggregate row count of the query. If a FETCH FIRST clause was used, it is displayed in this operator.|
The following codes are shown only for data store members in a query plan:
|Query Plan Code||Description|
|AGGREGATION||An aggregate operation such as SUM, AVG, MIN, or MAX.|
|CONSTRAINTSCAN||The time spent on an index scan created from a table constraint.|
|EXCEPT||An EXCEPT, EXCEPT ALL, or EXCEPT DISTINCT operation. See SQL Language Limitations.|
|FILTER||A predicate filtering operation.|
|GLOBALINDEXSCAN||The time spent performing a global hash index scan (for example, for reference checks).|
|GROUPBY||Time spent grouping columns.|
|HASHJOIN||The time spent on a hash join operation.|
|HASHSCAN||The time spent scanning a generated hash table.|
|HEAP||Time spent scanning GemFire regions.|
|INDEXSCAN||If the query required a scan on an index, then this code indicates the time required for the scan.|
|INTERSECT||An INTERSECT, INTERSECT ALL, or INTERSECT DISTINCT operation. See SQL Language Limitations.|
|LOHASHJOIN||The time spend for a left or right hash outer join operation.|
|LONLJOIN||The time spend for a left or right nested loop outer join operation.|
|MERGEJOIN||The time spent on a merge join operation.|
|NLJOIN||The time taken to perform a nested loop join
(matching all of the left table rows with the right table rows in a query).
NLJOIN always has two nested entries. The first entry corresponds to the outer table, which is read once and returns some number (n) of rows. The second entry corresponds to the inner table which is iterated over n times (once for each result in the outer table).
The individual seeks for the inner and outer tables are timed separately and are listed in each nested entry.
Example Query Plan Analysis describes an example query plan that performs an NLJOIN.
|PROJECT-FILTER||A predicate filtering operation that occurs while extracting columns (PROJECTION operation).|
|PROJECTION||The time spent to extract the selected columns from the base row of the table. This code can appear multiple times for a query depending on how the engine processes the query.|
|QUERY-RECEIVE||QUERY-RECEIVE is the top-level code that organizes all tasks associated with processing a query on a specific data store member. QUERY-RECEIVE 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 was distributed to multiple data store members, then each member displays a separate QUERY-RECEIVE code. The contents of multiple QUERY-RECEIVE codes are similar unless tables are partitioned across different VMs or physical machines.|
|RESULT-HOLDER||This value is linked to the RESULT-HOLDER code on the query coordinator. All of the tasks beneath RESULT-HOLDER represent query operators performed on the data store member.|
|RESULT-SEND||The network time that was spent sending result rows back to the query coordinator. The target node is the query coordinator.|
|ROW-COUNT||The time spent processing results for the FETCH clause.|
|ROWIDSCAN||The total time spent for index entries to fetch the base row from the table. This occurs when the projection of the query is different from the index column.|
|SET||Indicates a set operation (a UNION, UNION ALL, UNION DISTINCT, INTERSECT, INTERSECT ALL, INTERSECT DISTINCT, EXCEPT, EXCEPT ALL, or EXCEPT DISTINCT operation). See SQL Language Limitations.|
|SCROLL-INSENSITIVE||A scroll insensitive cursor operation.|
|SORT||The time spent sorting rows to execute an ORDER BY, DISTINCT, or GROUP BY clause.|
|TABLESCAN||The time required to scan a table.|
|UNION||The time spent performing a UNION, UNION ALL, or UNION DISTINCT operation of two queries. See SQL Language Limitations.|