Database Reference
In-Depth Information
package, you have to check the
TQ
column. With it, you can identify not only how many data flow operations are used
in an execution plan, but also which operations are executed by which set of slave processes. In fact, the content of the
TQ
column provides the following information:
NULL
value corresponds to the operations carried out by the query coordinator.
•
A
•
The value prefixed by the letter Q corresponds to the ID of the data flow operation.
•
The value following the comma corresponds to the ID of the table queue where a set of slave
processes
write
. What you can't know is how many slave processes belong to the set.
In the previous execution plan, because of the value
Q1,00
, you know that operations 2 to 4 belong to a single
data flow operation (whose ID is 1) and they are all executed by a single set of slave processes (whose ID is 0).
Data access operations aren't the only operations that can be executed in parallel. In fact, among other things,
the database engine is able to parallelize inserts, joins, aggregations, and sorts. When a SQL statement executes two or
more independent operations (for example, a scan and a sort), it's common for the database engine to use two sets of
slave processes. For example, as illustrated in Figure
15-7
, if a SQL statement executes a scan and then a sort, one set
is used for the scan and another set is used for the sort.
Figure 15-7.
Two sets of slave processes can be used to execute a SQL statement
The parallelization of a single operation is referred to as
intra-operation parallelism
. For example, in Figure
15-7
,
intra-operation parallelism (with four slave processes) is used twice: once for the scan and once for the sort. When
two sets of slave processes are used to execute a data flow operation, the parallelization is referred to as
inter-operation
parallelism
. For example, in Figure
15-7
, inter-operation parallelism is used between set 1 (scan) and set 2 (sort).
The following execution plan is an example of the processing illustrated in Figure
15-7
:
SELECT * FROM t ORDER BY id
-------------------------------------------------------------------------
| Id | Operation | Name | TQ |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | |
| 1 | PX COORDINATOR | | | | |
| 2 | PX SEND QC (ORDER) | :TQ10001 | Q1,01 | P->S | QC (ORDER) |