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;
No comments:
Post a Comment