Thursday, November 29, 2012

How I report Informatica Load Times


How I report Informatica Load Times

I am testing the performance of ETL workflows that use Informatica PowerCenter v9.   After the designers decide what would be the right way to map the data, and after the developers translate the design into a workflow, the QA team checks whether the results are correct, while I work on a team to see how fast it runs.   (We don’t care if it is right, we only care whether it is fast!  J)

I have found the following table of information is a useful format for reporting the runtimes of our Informatica workflows.    The query I find useful for reporting that is shown farther below.  If you have to focus your performance-tuning efforts productively, this will help you see which workflows and/or sessions take the longest, read and/or write the most rows, use the most CPU, etc.   Maybe this will give you some ideas.   If so, I hope that is helpful to you.

I connect with a read-only account that we use for reporting from Toad to the Oracle database that holds our Informatica repository.   Our repository schema is called INFADMIN9.  I export the results into an Excel table where I can subtotal things like the total runtime and total rows.   I’m confident you could adapt this further for your situation.   Maybe this will give you some ideas.  Let me know if I can talk about it more.

This query does an outer join to the OPB_TASK_STATS table.  Maybe it will find rows, or maybe not.   That table will only have rows in if your Informatica Adminstrator has configured the server to collect performance data.   Even if configured, it will only collect data if the workflow runs long enough for it to notice.  Short-running workflows probably aren’t a performance problem for you anyway, so try to not worry about it.

Best wishes to focus your attention where it needs to be,.
~ the hardworking DBA.

WORKFLOW_NAME
(Names changed for illustration)
VER
SION
WFLOW
START TIME
WFLOW END TIME
WFLOW RUN TIME
WFLOW STATUS
TASK_NAME
TASK START TIME
TASK END TIME
TASK RUN TIME
TASK STATUS
PCT_OF WFLOW TIME
SRC SUCCESS ROWS
TGT SUCCESS ROWS
TGT ROWS PER_MIN
WALL
CLOCK
TIME
CPU
TIME
PROCESS
SIZE
wf_CLNT_ACCT
15
11/16/2012 16:29
16:29:52
0:00:17
Succeeded
s_m_CLNT_ACCT
2012-11-16 16:29:39
16:29:48
0:00:09
Succeeded
50
14,191
14,191
94,607
700
9
1,107,849,216
wf_CLNT_BEN_PLAN_NT_ALLNC
15
11/16/2012 16:29
16:29:54
0:00:12
Succeeded
s_m_CLNT_BEN_PLAN_NT_ALLNC
2012-11-16 16:29:45
16:29:51
0:00:06
Succeeded
50
17,036
8,518
85,180
wf_CLNT_BEN_PLAN_VENDR
14
11/16/2012 16:29
16:30:09
0:00:22
Succeeded
s_m_CLNT_BEN_PLAN_VENDR
2012-11-16 16:29:51
16:30:05
0:00:14
Succeeded
64
111,741
29,226
125,254
1,300
21
731,312,128
wf_CLNT_BEN_PLAN_VRTN
13
11/16/2012 16:29
16:30:57
0:01:06
Succeeded
s_m_CLNT_BEN_PLAN_VRTN
2012-11-16 16:29:55
16:30:54
0:00:59
Succeeded
89
238,054
104,103
105,867
5,600
11
1,030,103,040
wf_CLNT_BRNCH
15
11/16/2012 16:29
16:31:26
0:01:32
Succeeded
s_m_CLNT_BRNCH
2012-11-16 16:29:58
16:31:21
0:01:23
Succeeded
90
220,913
220,913
159,696
8,400
15
1,032,458,240
wf_CLNT_CLM_SYS
15
11/16/2012 16:30
16:30:47
0:00:46
Succeeded
s_m_CLNT_CLM_SYS
2012-11-16 16:30:05
16:30:42
0:00:37
Succeeded
80
190,533
96,381
156,294
3,500
15
1,110,691,840
wf_CLNT_MATRN_CVRG
13
11/16/2012 16:30
16:34:26
0:04:19
Succeeded
s_m_CLNT_MATRN_CVRG
2012-11-16 16:30:11
16:34:21
0:04:10
Succeeded
97
813,124
374,143
89,794
25,000
14
2,186,006,528
wf_CLNT_NT
15
11/16/2012 16:30
16:30:44
0:00:30
Succeeded
s_m_ECDB_CLNT_NT
2012-11-16 16:30:17
16:30:38
0:00:21
Succeeded
68
47,057
47,057
134,449
2,300
9
1,099,677,696
wf_CLNT_BEN_PLAN
20
11/16/2012 18:29
18:29:53
0:00:38
Succeeded
s_m_ECDB_CLNT_BEN_PLAN
2012-11-16 18:29:17
18:29:48
0:00:30
Succeeded
79
96,381
96,381
186,544
3,000
15
1,114,316,800
Totals
11/16/2012 16:29
18:29:53
100%
0:08:29
100%
667
1,749,030
990,913


VARIABLE my_subject_area1   VARCHAR2(32);
VARIABLE my_subject_area2   VARCHAR2(32);
VARIABLE my_task_to_ignore1 VARCHAR2(32);  -- Some tasks can be ignored,
VARIABLE my_task_to_ignore2 VARCHAR2(32);  -- like event timers and schedulers
VARIABLE my_start_time      VARCHAR2(32);
VARIABLE my_stop_time       VARCHAR2(32);

EXEC :my_subject_area1   := 'masters-client';
EXEC :my_subject_area2   := '';
EXEC :my_task_to_ignore1 := '%%_UPDATE_LOAD_CNTL_TBLS';
EXEC :my_task_to_ignore2 := '%%_CHECK_FOR_DELTA';
EXEC :my_start_time      := '11/16/2012 14:05:00';
EXEC :my_stop_time       := ' 9/13/2212 12:12:12'; -- Set far into the future
                                                   -- unless you have a specific
                                                   -- end-time in mind.

SELECT  WFLOW.WORKFLOW_NAME                                 AS WORKFLOW_NAME,
        WFLOW.VERSION_NUMBER                                AS VERSION,
        TO_CHAR (WFLOW.START_TIME, 'YYYY-MM-DD HH24:MI:SS') AS WFLOW_START_TIME,
        TO_CHAR (WFLOW.END_TIME, 'HH24:MI:SS')              AS WFLOW_END_TIME,
        LTRIM (TO_CHAR (FLOOR ( (WFLOW.END_TIME -WFLOW.START_TIME) * 24), '09'))
                || ':'
                || LTRIM (TO_CHAR (MOD (FLOOR ( (WFLOW.END_TIME- WFLOW.START_TIME) * 24 * 60), 60), '09'))
                || ':'
                || LTRIM (TO_CHAR (MOD (FLOOR ( (WFLOW.END_TIME- WFLOW.START_TIME) * 24 * 60 * 60), 60), '09'))
                                                            AS WFLOW_RUN_TIME,
        DECODE (WFLOW.RUN_STATUS_CODE,
                1, 'Succeeded',
                3, 'Failed',
                4, 'Stopped',
                5, 'Aborted',
                6, 'Running',
                   'Unknown')                               AS WFLOW_STATUS,
        INST.TASK_NAME,
        TO_CHAR (INST.START_TIME, 'YYYY-MM-DD HH24:MI:SS')  AS TASK_START_TIME,
        TO_CHAR (INST.END_TIME, 'HH24:MI:SS')               AS TASK_END_TIME,
        LTRIM (TO_CHAR (FLOOR ( (INST.END_TIME - INST.START_TIME) * 24), '09'))
                || ':'
                || LTRIM (TO_CHAR (MOD (FLOOR ( (INST.END_TIME- INST.START_TIME) * 24 * 60), 60), '09'))
                || ':'
                || LTRIM (TO_CHAR (MOD (FLOOR ( (INST.END_TIME- INST.START_TIME) * 24 * 60 * 60), 60), '09'))
                                                            AS TASK_RUN_TIME,
        DECODE (INST.RUN_STATUS_CODE,
                1, 'Succeeded',
                2, 'Disabled',
                3, 'Failed',
                4, 'Stopped',
                5, 'Aborted',
                6, 'Running',
                    'Unknown')                                AS TASK_STATUS,
        DECODE (
                WFLOW.RUN_STATUS_CODE,
                6, DECODE (INST.RUN_STATUS_CODE,
                        6, ROUND (100.0 * -- If running, anchor end to now.
                                 (SYSDATE - INST.START_TIME) / (SYSDATE - WFLOW.START_TIME)),
                       /* else */
                           ROUND (100.0 * (INST.END_TIME - INST.START_TIME) / (SYSDATE - WFLOW.START_TIME))),
                /* ELSE */
                        ROUND (100.0 * (INST.END_TIME - INST.START_TIME) / (WFLOW.END_TIME -WFLOW.START_TIME)))
                                                            AS PCT_OF_WFLOW_TIME,
        SESS.SRC_SUCCESS_ROWS                               AS SRC_SUCCESS_ROWS,
        SESS.TARG_SUCCESS_ROWS                              AS TGT_SUCCESS_ROWS,
        CASE WHEN INST.END_TIME > INST.START_TIME
             THEN ROUND (SESS.TARG_SUCCESS_ROWS/
                        ( (INST.END_TIME- INST.START_TIME) * 24 * 60))
             ELSE 0
             END                                            AS TGT_ROWS_PER_MIN,
        STATS.WALLCLOCK_TIME                                AS WALLCLOCK_TIME,
        STATS.CPU_TIME                                      AS CPU_TIME,
        STATS.PROCESS_SIZE                                  AS PROCESS_SIZE
FROM    INFADMIN9.OPB_WFLOW_RUN WFLOW,
        INFADMIN9.OPB_SUBJECT SUBJECT,
        INFADMIN9.OPB_TASK_INST_RUN INST,
        INFADMIN9.OPB_SESS_TASK_LOG SESS,
        INFADMIN9.OPB_TASK_STATS STATS
WHERE  (SUBJECT.SUBJ_NAME    = :MY_SUBJECT_AREA1
     OR SUBJECT.SUBJ_NAME    = :MY_SUBJECT_AREA2)
AND     WFLOW.START_TIME     > TO_DATE (:MY_START_TIME, 'mm/dd/yyyy HH24:MI:ss')
AND WFLOW.START_TIME         < TO_DATE (:MY_STOP_TIME,  'mm/dd/yyyy HH24:MI:ss')
AND INST.TASK_NAME           NOT LIKE :MY_TASK_TO_IGNORE1
AND INST.TASK_NAME           NOT LIKE :MY_TASK_TO_IGNORE2
AND WFLOW.SUBJECT_ID         = SUBJECT.SUBJ_ID
AND WFLOW.WORKFLOW_ID        = INST.WORKFLOW_ID
AND WFLOW.WORKFLOW_RUN_ID    = INST.WORKFLOW_RUN_ID
AND WFLOW.WORKFLOW_ID        = SESS.WORKFLOW_ID
AND WFLOW.WORKFLOW_RUN_ID    = SESS.WORKFLOW_RUN_ID
AND INST.WORKFLOW_ID         = SESS.WORKFLOW_ID
AND INST.WORKFLOW_RUN_ID     = SESS.WORKFLOW_RUN_ID
AND INST.INSTANCE_ID         = SESS.INSTANCE_ID
AND INST.TASK_TYPE          != 62 --62 is the Start task.
AND STATS.WORKFLOW_ID    (+) = SESS.WORKFLOW_ID
AND STATS.WORKFLOW_RUN_ID(+) = SESS.WORKFLOW_RUN_ID
AND STATS.INSTANCE_ID    (+) = SESS.INSTANCE_ID
ORDER BY
      WFLOW.START_TIME ASC,
      WFLOW.WORKFLOW_NAME,
      INST.START_TIME ASC;