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