Monday, November 26, 2018

DTM Buffer Size


DTM Buffer Size

Typically, the DTM Buffer Size is set to Auto, and the Auto Memory Settings are used to determine the value here. Occasionally it is useful to set this higher, but never lower than the Auto Memory value of 512 MB. In older version of PowerCenter, or for sessions that are auto-generated, the value is typically set to 24000000, or 24 MB. Obviously, this is much smaller than the default of 512 MB. Values that are less than 512 MB should be set to Auto to use the default value for the Auto memory settings.

The DTM Buffer has three sections, one for Read, one for Write, and one for Transform. Rows that are being processed get loaded into the DTM buffer. The three sections are not each the same size. That is adjusted at execution time. See the explanation of this later in the document to understand how increasing the DTM Buffer Size can allow more rows to process at once and to improve performance.

The number of values where the settings were manually set to something other than Auto are too numerous to display here and instead are included in an appendix. Many are set too low, or set manually to 512 MB.

Below are repository queries to list values on all objects in a repository.

SELECT
                B.SUBJECT_AREA,
                B.TASK_NAME AS SESS_NAME,
                A.ATTR_VALUE AS DTM_BUFFER_SIZE
FROM
                OPB_TASK_ATTR A ,
                REP_ALL_TASKS B
WHERE
                A.ATTR_ID IN (101)
                AND A.TASK_ID = B.TASK_ID
                AND TASK_TYPE_NAME IN ('Session')
ORDER BY 1,2 ASC

  


SELECT *
  FROM (SELECT Z.SUBJ_NAME,
               A.TASK_NAME     WORKFLOW_OR_WORKLET_NAME,
               B.INSTANCE_NAME SESSION_NAME,
               C.ATTR_VALUE    VALUE
          FROM OPB_TASK       A,
               OPB_TASK_INST  B,
               OPB_TASK_ATTR  C,
               OPB_SUBJECT    Z
         WHERE     Z.SUBJ_ID = A.SUBJECT_ID
               AND A.TASK_TYPE IN (70, 71)
               AND A.TASK_ID = B.WORKFLOW_ID
               AND B.TASK_TYPE = 68
               AND B.INSTANCE_ID = C.INSTANCE_ID
               AND C.TASK_TYPE = 68
               AND B.TASK_ID = C.TASK_ID
               AND ATTR_ID = 101
        UNION
        SELECT Z.SUBJ_NAME,
               A.TASK_NAME     WORKFLOW_OR_WORKLET_NAME,
               B.INSTANCE_NAME SESSION_NAME,
               C.ATTR_VALUE    VALUE
          FROM OPB_TASK       A,
               OPB_TASK_INST  B,
               OPB_TASK_ATTR  C,
               OPB_SUBJECT    Z
         WHERE     Z.SUBJ_ID = A.SUBJECT_ID
               AND A.TASK_TYPE IN (70, 71)
               AND A.TASK_ID = B.WORKFLOW_ID
               AND B.TASK_TYPE = 68
               AND C.INSTANCE_ID = 0
               AND C.TASK_TYPE = 68
               AND B.TASK_ID = C.TASK_ID
               AND ATTR_ID = 101
               AND B.INSTANCE_ID NOT IN
                       (SELECT C.INSTANCE_ID
                          FROM OPB_TASK       A,
                               OPB_TASK_INST  B,
                               OPB_TASK_ATTR  C,
                               OPB_SUBJECT    Z
                         WHERE     Z.SUBJ_ID = A.SUBJECT_ID
                               AND A.TASK_TYPE IN (70, 71)
                               AND A.TASK_ID = B.WORKFLOW_ID
                               AND B.TASK_TYPE = 68
                               AND B.INSTANCE_ID = C.INSTANCE_ID
                               AND C.TASK_TYPE = 68
                               AND B.TASK_ID = C.TASK_ID
                               AND ATTR_ID = 101)) DUAL