Parallel Execution of SQL Statements

Each SQL statement undergoes an optimization and parallelization process when it is parsed. If parallel execution is executed in parallel, then the following steps occur:

  1. The user session or shadow process takes on the role of a coordinator, often called the query coordinator.

  2. The query coordinator obtains the necessary number of parallel servers.

  3. The SQL statement is executed as a sequence of operations (a full table scan to perform a join on a nonindexed column, an ORDER BY clause, and so on). The parallel execution servers performs each operation in parallel if possible.

  4. When the parallel execution servers are finished executing the statement, the query coordinator performs any portion of the work that cannot be executed in parallel. For example, a parallel query with a SUM() operation requires adding the individual subtotals calculated by each parallel server.

  5. Finally, the query coordinator returns any results to the user.

After the optimizer determines the execution plan of a statement, the parallel execution coordinator determines the parallel execution method for each operation in the plan. Under normal conditions this is simply maintenance work to prepare the final result for the end user. For example, the parallel execution method might be to perform a parallel full table scan by block range or a parallel index range scan by partition. The coordinator must decide whether an operation can be performed in parallel and, if so, how many parallel execution servers to enlist. The number of parallel execution servers in one set is the degree of parallelism (DOP).