Structure of a PDQ Query

The database server divides a query into components that can be performed in parallel to increase the speed of query execution significantly.

Depending on the number of tables or fragments that a query must search, the optimizer determines if a query subplan can execute in parallel.

The Resource Grant Manager (RGM) assigns the different components of a query to different threads across processors on different coservers.

The sqlexec thread initiates these component threads, which the SET EXPLAIN output lists as secondary threads.

Secondary threads are further classified as either producers or consumers, depending on their function. A producer thread supplies data to another thread. For example, a scan thread might read data from shared memory that corresponds to a given table and pass it along to a join thread. In this case, the scan thread is considered a producer, and the join thread is considered a consumer. The join thread, in turn, might pass data along to a sort thread. When it does so, the join thread is considered a producer, and the sort thread is considered a consumer.

The database server uses SQL operators and exchanges to divide a query plan into subplans that can be performed in parallel to increase the speed of query execution significantly.

SQL Operators

An SQL operator is a process that accepts a stream of rows from one or two data tables. Each SQL operator reads each row in a stream and applies a predefined behavior to the data.

For example, a typical query plan might contain scan and hash join SQL operators. The behavior of these SQL operators is as follows:

• Scan

  This type of SQL operator performs a sequential read on:

  • a table or index fragment.
  • an unfragmented table or index.

  The scan SQL operators handle data from a local table or local index. A local table or local index resides on the same coserver on which the SQL operator executes.

• Hash join

  This type of SQL uses a hash method to join tables. It selects one table from to constructs a hash table. It then uses that hash table to join data from other tables involved in the join operation.

The database server creates multiple instances of each SQL operator to execute on different parts of the data in parallel, as follows:

• The scan operators execute in parallel, based on the fragmentation strategy of the tables.
• The hash join operators execute in parallel, based on the availability of resources (such as memory and number of CPU VPs) on the coservers.

The database server structures queries into a plan of SQL operators. Figure 75 shows the SQL operator plan that the database server constructs to process the following SQL query:

SELECT geo_id, dollars
   FROM customer a, cash b
   WHERE a.cust_id=b.cust_id;

Figure 75. SQL Operator Plan for a Query

Exchanges

An exchange is another process that affects parallel processing. An exchange takes the results of two or more instances of an SQL operator and initiates another set of operators to process the next SQL operator that is required to complete the query. The database server inserts exchanges at places within an SQL operator plan where parallelism is beneficial.

When several instances of an SQL operator supply data to another SQL operator, the exchange synchronizes the transfer of data from the multiple instances to the next SQL operator. For instance, if two fragmented tables are to be joined, the optimizer typically calls for a separate scan thread for each fragment. Because of different I/O characteristics, the scan threads can complete their work at different times. The database server uses an exchange to funnel the data that the various scan threads produce into one or more join threads with a minimum amount of buffering.

PDQ Threads

Depending on the resources that are available for a decision-support query, the database server assigns the different components of a query plan to different threads across coservers. The sqlexec thread initiates these PDQ threads, which the SET EXPLAIN output lists as secondary threads.

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

Some monitoring tools display only the SQL operator but not the exchanges. For more information on monitoring PDQ, refer to your IBM Informix: Performance Guide.