Query Parallelism

This topic shows detailed information about Accounting - Query Parallelism.

If a query exploits query CP (central processor) parallelism or sysplex query parallelism, several tasks (called parallel tasks) perform the work. For each of these tasks an accounting record is generated, which contains counters and timers pertinent to the work performed by the particular task. In addition, an accounting record is created that contains the details on nonparallel work within the thread as well as data related to parallel work.

For formatting reasons, OMEGAMON XE for DB2 PE shows different labels for report and trace. The following example shows both layouts, the report on the left, and the trace layout on the right.

Report:                                           Trace:

QUERY PARALLELISM           AVERAGE     TOTAL     QUERY PARALLELISM             TOTAL
-------------------------  --------  --------     -------------------------  --------
MAXIMUM DEGREE-ESTIMATED       0.00         0     MAXIMUM DEGREE-ESTIMATED        N/A
MAXIMUM DEGREE-PLANNED         0.00         0     MAXIMUM DEGREE-PLANNED          N/A
MAXIMUM DEGREE-EXECUTED         N/A         0     MAXIMUM DEGREE-EXECUTED           0
MAXIMUM MEMBERS USED            N/A         0     MAXIMUM MEMBERS USED            N/P
PARALLEL GROUPS EXECUTED       0.00         0     PARALLEL GROUPS EXECUTED          0
 RAN AS PLANNED                0.00         0      RAN AS PLANNED                   0
 RAN REDUCED-STORAGE           0.00         0      RAN REDUCED-STORAGE              0
 RAN REDUCED-NEGOTIATION       0.00         0      RAN REDUCED-NEGOTIATION        N/A
 SEQ-CURSOR                    0.00         0      SEQ-CURSOR                       0
 SEQ-NO ESA SORT               0.00         0      SEQ-NO ESA SORT                  0
 SEQ-NO BUFFER                 0.00         0      SEQ-NO BUFFER                    0
 SEQ-AUTONOMOUS PROC           0.00         0      SEQ-AUTONOMOUS PROC            N/A
 SEQ-NEGOTIATION               0.00         0      SEQ-NEGOTIATION                N/A
 ONE DB2-COORDINATOR = NO      0.00         0      ONE DB2-COORDINATOR = NO       N/A
 ONE DB2-ISOLATION LEVEL       0.00         0      ONE DB2-ISOLATION LEVEL          0
 ONE DB2-DCL TTABLE            0.00         0      ONE DB2-DCL TTABLE               0
MEMBER SKIPPED (%)              N/C       N/A     MEMB SKIPPED(%)                   0
DISABLED BY RLF                0.00         0     DISABLED BY RLF                   0
REFORM PARAL-CONFIG            0.00         0     REFORM PARAL-CONFIG        NO      
REFORM PARAL-NO BUF            0.00         0     REFORM PARAL-NO BUF               0

MAXIMUM DEGREE-ESTIMATED

The maximum parallel-group estimated degree (DB2 field: QXMAXESTIDG). It is the bind time estimated degree based on the cost formula. If the parallel group contains a host variable or parameter marker, bind time will estimate the parallel-group degree based on a valid assumption value.

Field Name: AXMESTDG

MAXIMUM DEGREE-PLANNED

The maximum parallel-group planned degree (DB2 field: QXMAXPLANDG). It is the ideal parallel-group degree obtained at execution time after the host variable or parameter marker value is "plug-in" and before the buffer pool negotiation and the system negotiation are performed.

Field Name: AXMPLNDG

MAXIMUM DEGREE-EXECUTED

The maximum degree of parallelism executed among all parallel groups to indicate the extent to which queries were processed in parallel.

Field Name: QXMAXDEG

MAXIMUM MEMBERS USED

The maximum number of DB2 members that participated in the processing of a query.

Field Name: AMAXMEMB

PARALLEL GROUPS EXECUTED

The total number of parallel groups executed.

Field Name: QXTOTGRP

RAN AS PLANNED

The total number of parallel groups that executed in the planned parallel degree. This field is incremented by one for each parallel group that executed in the planned degree of parallelism (as determined by DB2).

Field Name: QXNORGRP

RAN REDUCED-STORAGE

The total number of parallel groups that did not reach the planned parallel degree because of a lack of storage space or contention on the buffer pool.

The exception field name is QXREDGRP.

Background and Tuning Information

If this field is not 0, increase the size of the current buffer pool using the ALTER BUFFERPOOL command or use the ALTER TABLESPACE command to assign table spaces accessed by this query to a different buffer pool.

Field Name: QXREDGRP

This is an exception field.

RAN REDUCED-NEGOTIATION

The number of parallel-group degrees that is reduced because of the system negotiation result of the system stress level (DB2 field: QXSTOREDGRP).

Field Name: AXREDPGD

SEQ-CURSOR

The total number of parallel groups that fell back to sequential mode due to a cursor that can be used by UPDATE or DELETE.

Field Name: QXDEGCUR

SEQ-NO ESA SORT

The total number of parallel groups that fell back to sequential mode due to a lack of ESA sort support.

Field Name: QXDEGESA

SEQ-NO BUFFER

The total number of parallel groups that fell back to sequential mode due to a storage shortage or contention on the buffer pool.

The exception field name is QXDEGBUF.

Field Name: QXDEGBUF

SEQ-AUTONOMOUS PROC

The total number of parallel groups that fell back to sequential mode under an autonomous procedure.

Field Name: QXDEGAT

SEQ-NEGOTIATION

The number of parallel groups is degenerated to sequential because of the system negotiation result of system stress level (DB2 field: QXSTODGNGRP).

Field Name: AXDEGPGD

ONE DB2-COORDINATOR = NO

The total number of parallel groups executed on a single DB2 subsystem due to the COORDINATOR subsystem value being set to NO. When the statement was bound, the COORDINATOR subsystem value was set to YES. This situation can also occur when a package or plan is bound on a DB2 subsystem with COORDINATOR=YES, but is run on a DB2 subsystem with COORDINATOR=NO.

Field Name: QXCOORNO

ONE DB2-ISOLATION LEVEL

The total number of parallel groups executed on a single DB2 subsystem due to repeatable-read or read-stability isolation.

Field Name: QXISORR

ONE DB2-DCL TTABLE

The number of parallel groups in a query block that were downgraded to CPU parallelism because they referenced a UDF and a declared temporary table was detected at execution time.

DB2 enforces execution on a single DB2 (CPU parallelism), in this instance, because it cannot determine at incremental bind time for the statement whether the UDF will reference the declared temporary table. Other parallel groups in the same statement are not necessarily downgraded.

Field Name: QXDEGDTT

MEMBER SKIPPED (%)

The percentage of parallel groups that were not distributed over the data sharing group, as originally planned at bind time, because one or more DB2 members did not have enough buffer pool storage. This only applies to parallel groups that were intended to run in sysplex query parallelism.

This percentage is to indicate a lack of buffers at a member. It is only increased when the buffer pool is defined to allow for parallelism. For example, if VPXPSEQT=0 on an assistant, DB2 does not send parallel work there, and the percentage is not increased.

Field Name: AXXCRAT

DISABLED BY RLF (Report)

The number of threads where at least one dynamic SQL statement was disabled by the Resource Limit Facility (RLF).

Field Name: ADPARDNR

DISABLED BY RLF (Trace)

Indicates whether Query Parallelism is disabled by the Resource Limit Facility (RLF) for at least one dynamic SELECT statement in this thread.

Field Name: ADPARDIS

REFORM PARAL-CONFIG

The total number of parallel groups where DB2 reformulated the parallel portion of the access path because of a change in the number of active members, or because of a change of processor models on which they run, from bind time to run time. This counter is incremented only on the parallelism coordinator at run time.

Field Name: QXREPOP1

REFORM PARAL-NO BUF

The total number of parallel groups in which DB2 reformulated the parallel portion of the access path because there were insufficient buffer-pool resources. This counter is incremented only at the parallelism coordinator at run time.

Field Name: QXREPOP2