Parallel Query Components

The optimizer creates a query plan that divides the query into components that it can execute in parallel.

The number of tables or fragments that a query requires determines whether a query executes in parallel. If the words Parallel, fragments appear in the access plan for a table in the SET EXPLAIN output, parallel scans occur. The Resource Grant Manager (RGM) assigns the query operators to different instances across coservers. The SET EXPLAIN output lists these instances as secondary threads.

The optimizer uses SQL operators and exchange operators to execute a query, as the following sections explain.

SQL Operators

An SQL operator is a process that performs predefined operations on one or more streams of rows. For a list of SQL operators and their functions, see Interpreting SQL Operator Runtime Data in the Query Plan.

The optimizer uses SQL operators and exchanges to create a parallel query plan and construct a tree of operators. The query plan is sometimes called the xplan because operators often execute in parallel across coservers. Each query plan consists of branches and branch instances:

• Branch (sometimes referred to as segment)

  Each branch represents a set of one or more SQL operators that do not require exchanges between them. For example, a branch might contain a group operator and a hash-join operator.

• Branch instance

  A branch instance is a secondary thread that executes one of the SQL operators in the branch.

  The optimizer creates multiple instances of each branch to execute in parallel on different parts of the data. The number of instances of these SQL operators is determined by the availability and number of CPU virtual processors.

Some SQL operators handle data from a local table or local index. A table or index is local if it resides on the same coserver where the SQL operator is executing.

The optimizer structures complex queries into a plan of SQL operators. Figure 28 shows the SQL operator plan that the optimizer constructs to process the following SQL query:

SELECT geo_id, sum(dollars)
   FROM customer a, cash b
   WHERE a.cust_id=b.cust_id
   GROUP BY geo_id
   ORDER BY SUM(dollars);

Figure 28. SQL Operator Plan for a Query

Exchanges

An exchange is a special parallel operator. As data flows through the query plan, exchanges facilitate parallelism, pipelining, and communication of data from producer instances of an SQL operator branch to consumer instances of the SQL operator branch above it. The optimizer inserts exchanges at places in an SQL operator plan where parallel redistribution of producer output to consumers is beneficial.

When several producers supply data to a single consumer, the exchange coordinates the transfer of data. For example, if a fragmented table is sorted, the database server usually creates a separate scan thread for each fragment. The exchange divides the data that the various scan threads produce into one or more sort threads with minimum buffering.

To maximize the efficiency of parallel processing, query plans for complex queries might contain a multilayered hierarchy of producers, exchanges, and consumers.

Figure 29 shows how an exchange can add parallelism to the set of SQL operators from Figure 28.

Figure 29. Simplified Illustration of Exchanges in SQL Operator Plan for a Query

The example in Figure 29 shows how a single coserver with two CPU virtual processors and one fragment of each of the customer and cash tables might process the query. Starting from the bottom, Figure 29 shows the following operations:

• One CPU virtual processors scans a table fragment of the customer table while the other CPU virtual processor scans a table fragment of the cash table.
• Exchange operators repartition the rows based on the join key and distribute the request to form a join with the repartitioned data to the two CPU virtual processors.

  The exchange operators coordinate data from both the customer and cash tables to ensure that customer and cash rows with the same key go to the same CPU virtual processors for the join operation.

• Two join operators use a hash-join method to combine the data from the two table fragments.
• Exchange operators receive the results of the joins and divide the rows for the GROUP operation.
• Group operators group the data by geographic area.
• Exchange operators combine the results of the GROUP operation and distribute the data to the sort instances.
• The sort operator sorts the data by dollar sum.
• A final exchange operator combines the results of the sort.

On a real database server, of course, this example would be much more complicated. Many table fragments and many CPU virtual processors might exist, either on the same coserver or across many coservers. Figure 29 shows only the simplest case.

The database server can execute each instance of an SQL operator on a separate CPU virtual processor if enough CPU virtual processors are available and if SET ENVIRONMENT COMPUTE_QUOTA is not set for the session that submits the query. For example, if five coservers are configured with two CPU virtual processors on each coserver, the exchange operators can initiate 10 hash-join operators to increase the parallel execution of the joins.

Parallel Processing Threads

Depending on the resources that are required for a query, the Resource Grant Manager (RGM) assigns the branch instances in a query subplan to coservers. The query executor executes the query plan and starts the following threads:

• An sqlexec thread on the connection coserver

  The sqlexec threads manages the global session context on the coserver to which the session is connected.

• An x_exec thread on each participating coserver

  The x_exec threads manage the session context on the participating coservers.

• Additional threads to execute the branch instances

  The SET EXPLAIN output lists these threads as secondary threads.

The query executor creates these secondary threads and exchanges automatically and transparently. They are terminated automatically as they complete processing for a given query. The query executor creates new threads and exchanges as needed for subsequent queries.