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.
Accounting - Query Parallelism
The field labels shown in the following sample layout of
Accounting - Query Parallelism are described in the following section.
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
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
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