A parallel query plan is composed of different operators, at least one of which is an exchange operator. At runtime, a parallel query plan is bound to a set of server processes that, together, execute the query plan in a parallel fashion.
The server process associated with the user connection is called the alpha process because it is the source process from which parallel execution is initiated. In particular, each worker process involved in the execution of the parallel query plan is spawned by the alpha process.
In addition to spawning worker processes, the alpha process initializes all the worker processes involved in the execution of the plan, and creates and destroys the pipes necessary for worker processes to exchange data. The alpha process is, in effect, the global coordinator for the execution of a parallel query plan.
At runtime, Adaptive Server associates each exchange operator in the plan with a set of worker processes. The worker processes execute the query plan fragment located immediately below the exchange operator.
For the query in Example A, represented in “EXCHANGE operator”, the exchange operator is associated with three worker processes. Each worker process executes the plan fragment made of the exchange:emit operator and of the scan operator.
Figure 5-2: Query execution plan with one exchange operator
Each exchange operator is also associated with a server process named the beta process, which can be either the alpha process or a worker process. The beta process associated with a given exchange operator is the local coordinator for the execution of the plan fragment below the exchange operator. In the example above, the beta process is the same process as the alpha process, because the plan to be executed has only one level of exchange operators.
Next, use this query to illustrate what happens when the query plan contains multiple exchange operators:
select count(*),pub_id, pub_date from titles group by pub_id, pub_date
Figure 5-3: Query execution plan with two exchange operators
There are two levels of exchange operators marked as EXCHANGE-1 and EXCHANGE-2 in Figure 5-3. Worker process T4 is the beta process associated with the exchange operator EXCHANGE-2.
The beta process locally orchestrate execution of the plan fragment below the exchange operator; it dispatches query plan information that is needed by the worker processes, and synchronizes the execution of the plan fragment.
A process involved in the execution of a parallel query plan that is neither the alpha process nor a beta process is called a gamma process.
A given parallel query plan is bound at runtime to a unique alpha process, to one or more beta processes, and to at least one gamma process. Any Adaptive Server parallel plan needs at least two different processes (alpha and gamma) to be executed in parallel.
To find out the mapping between exchange operators and worker processes, as well as to figure out which process is the alpha process, and which processes are the beta processes, use dbcc traceon(516):
=======Thread to XCHg Map BEGINS======= ALFA thread spid: 17 XCHG = 2 <- refers to Xchg-2 Comp Count = 2 Exec Count = 2 Range Adjustable Consumer XCHG = 5 Parent thread spid: 34 <- refers to T4 Child thread 0: spid:37 <- refers to T1 Child thread 1: spid: 38 <- refers to T2 Child thread 2: spid: 36 <- refers to T3 Scheduling level: 0 XCHG = 5 <- refers to Xchg-1 Comp Count = 3 Exec Count = 3 Bounds Adjustable Consumer XCHG -1 Parent thread spid: 17 <- refers to Alpha Child thread 0: spid: 34 <- refers to T4 Child thread 1: spid: 35 <- refers to T5 Scheduling level: 0 =======Thread to XCHg Map BEGINS=======
