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