Tuning parallel processing

You can improve the use of parallel processing by ensuring the availability of buffer pools, minimizing logical and physical contention and other situations that might reduce the degree of parallelism.

Procedure

Begin program-specific programming interface information.To tune parallel processing, use any of the following approaches:

  • Increase the availability of buffer pools.
    Depending on buffer pool availability, Db2 might reduce the degree of parallelism (see RAN REDUCED in the example accounting trace report) or revert to a sequential plan before executing the parallel group (SEQ - NO BUF in the example accounting trace report).
    1. Check the QW0221C section in IFCID 0221 to determine which buffer pools is short on storage.
    2. Use the ALTER BUFFERPOOL command to increase the values of the following parameters:
      • VPSEQT, the sequential steal threshold
      • VPPSEQT, the parallel sequential threshold
    3. If increasing the parallel threshold parameters does not improve the degree of parallelism, use the ALTER BUFFERPOOL command to increase the total buffer pool size (VPSIZE).
      Use information from the statistics trace and the following formula trace to determine the amount of buffer space you need:
      (QBSTJIS / QBSTPQF) × 32 = buffer increase value
      QBSTJIS is the total number of requested prefetch I/O streams that were denied because of a storage shortage in the buffer pool. (There is one I/O stream per parallel task.) QBSTPQF is the total number of times that Db2 could not allocate enough buffer pages to allow a parallel group to run to the planned degree.
      For example, assume that the value of QBSTJIS is 100,000 and QBSTPQF is 2500 and apply the formula:
      (100,000 / 2500) × 32 = 1280

      In this case, you would use the ALTER BUFFERPOOL to increase the current VPSIZE by 2560 buffers to alleviate the degree degradation problem. You can use the DISPLAY BUFFERPOOL command to see the current value of the VPSIZE parameter.

  • Minimize logical contention.
    For example, in a nested-loop join, the inner table can be in a partitioned or nonpartitioned table space, but Db2 is more likely to use a parallel join operation when the outer table exists in a partitioned table space.
  • Minimize physical contention by putting data partitions on separate physical devices and keeping the number of partitions smaller than the number of internal paths in the controller.
  • Check for updatable cursors.
    At run time, Db2 might determine that an ambiguous cursor is updatable. This appears in the SEQ - CURSOR field in example accounting trace report
  • Check for proper hardware and software support.
    If you do not have the hardware sort facility at run time, and a sort merge join is needed, you see a value in the SEQ - NO ESA field.
  • Check whether the configuration of online processors has changed.
    If fewer online processors are available at run time than at bind time, Db2 reformulates the parallel degree to take best advantage of the current processing power. This reformulation is indicated by a value in theREFORM PARAL-CONFIG field in the accounting report.

    The following example, shows part of an accounting trace report.

    Figure 1. A partial sample that shows parallelism fields in the IBM® OMEGAMON® for Db2 Performance Expert on z/OS® accounting trace report
    TIMES/EVENTS  APPL (CLASS 1)   DB2 (CLASS 2)    CLASS 3 SUSP.   ELAPSED TIME
------------  --------------  --------------    --------------  ------------
ELAPSED TIME       32.578741       32.312218    LOCK/LATCH         25.461371
 NONNESTED         28.820003       30.225885    SYNCHRON. I/O       0.142382
 STORED PROC        3.758738        2.086333     DATABASE I/O       0.116320
 UDF                0.000000        0.000000     LOG WRTE I/O       0.026062
 TRIGGER            0.000000        0.000000    OTHER READ I/O   3:00.404769
                                                OTHER WRTE I/O      0.000000
CPU CP TIME      1:29.695300     1:29.644026    SER.TASK SWTCH      0.000000
 AGENT              0.225153        0.178128     UPDATE COMMIT      0.000000
  NONNESTED         0.132351        0.088834     OPEN/CLOSE         0.000000
  STORED PRC        0.092802        0.089294     SYSLGRNG REC       0.000000
  UDF               0.000000        0.000000     EXT/DEL/DEF        0.000000
  TRIGGER           0.000000        0.000000     OTHER SERVICE      0.000000
 PAR.TASKS       1:29.470147     1:29.465898    ARC.LOG(QUIES)      0.000000
  ⋮
…            QUERY PARALLEL.     TOTAL
               ---------------  --------
               MAXIMUM MEMBERS         1
               MAXIMUM DEGREE         10
               GROUPS EXECUTED         1
                RAN AS PLANNED         1
                RAN REDUCED            0
                ONE  COOR=N            0
                ONE  ISOLAT            0
                ONE  DCL TTABLE        0
                SEQ - CURSOR           0
                SEQ - NO ESA           0
                SEQ - NO BUF           0
                SEQ - ENCL.SER.        0
               MEMB SKIPPED(%)         0
               DISABLED BY RLF        NO
               REFORM PARAL-CONFIG     0
               REFORM PARAL-NO BUF     0
  • Set the value of the PARA_EFF subsystem parameter to control the parallelism efficiency that Db2 assumes when selecting access paths.
    The recommended setting is 50.
    The efficiency of parallelism depends on system configuration and workload.

    Perfect parallelism efficiency assumes that Db2 is able to distribute the work uniformly across all parallel tasks and that the system resources (processor, memory, bufferpool) exist to process the expected degree of parallelism. Often, the distribution of work is not uniform and the processing resources are not available to handle all of the parallel tasks concurrently. Consequently, assuming perfect parallelism efficiency for the purposes of deciding to switch to a more parallel plan from a less parallel plan can result in inefficient performance.

    The value of the PARA_EFF subsystem parameter has the following meanings:
    100
    Db2 uses the most-optimistic assumption regarding the cost reductions that result from parallelism efficiency. However, Db2 might overestimate the cost reduction that a higher degree of parallelism provides. When that happens Db2 might select an access path that achieves a higher degree of parallelism but actually yields neither the estimated cost savings nor a shorter elapsed time.
    1-99
    Db2 considers cost reductions that result from parallelism efficiency, but it uses a proportionally reduced estimation of the cost reduction that degrees of parallelism provide. The closer the value to 1, the less effect that the expected degree of parallelism has on the access path selection. Whereas, the closer that the value is to 100, the more likely Db2 is to choose an access path that provides a higher estimated degree of parallelism, even though the estimated or actual cost might be greater.
    0
    Db2 chooses the access path that has the lowest sequential cost, regardless any consideration of the estimated cost reduction by parallelism. However, this setting only removes the consideration of parallelism during access path selection. It does not prevent the use of parallelism for the selected access path.
    End program-specific programming interface information.