Job is running very slow

[ravij]

Hi,

I have a simple one to one mapping job is taking long time to run. My job design is OraOci -> Trf -> OraOCI.

To Process 4440 records it took 30mins and the other day it took 58 mins to procsess just 125 records in Production. We have a user defined source extract query to pick the data based on dates. The query is

Code: Select all

select * from (select LZ.*,row_number() over (partition by incident_id order by incident_id) rn from lz_incident lz where lz.LAST_UPDATED_DATE >=  to_date('#pFROM_DATE#', 'YYYY-MM-DD-HH24:MI:SS')  and 
lz.LAST_UPDATED_DATE < to_date('#pTO_DATE#', 'YYYY-MM-DD-HH24:MI:SS')  and  lz.last_updated_date in
 (select max(last_updated_date) from lz_incident group by incident_id)) where rn=1
and
LAST_UPDATED_DATE >=  to_date('#pFROM_DATE#', 'YYYY-MM-DD-HH24:MI:SS') 
and LAST_UPDATED_DATE < to_date('#pTO_DATE#', 'YYYY-MM-DD-HH24:MI:SS')

The target Write Method is 'Load'. I really don't understand the behaviour of this job. Can anybody help me where do I look into the issue.

Thanks in advance.
RJ

[samdsx]

Split the job into 2 jobs (extracts to dataset) and (dataset to load) and see if it improves.

[qt_ky]

I ran into that on 7.5 some years back with same job design taking 11 hours. After splitting the job exactly as samdsx suggested here, the total time went to 30 minutes on millions of rows. I never found the root cause for performance issue within a single job.

[SURA]

OraOci -> peek-->Trf ->peek-->OraOCI

Try this.

DS User

[ray.wurlod]

OraOci -> peek-->Trf ->peek-->OraOCI

Try this.

DS User

Why? What do you believe it will accomplish in terms of the original question?

[pandeesh]

How long it takes, if you run the below query in toad or any other clients?

Code: Select all

select * from (select LZ.*,row_number() over (partition by incident_id order by incident_id) rn from lz_incident lz where lz.LAST_UPDATED_DATE >=  to_date('#pFROM_DATE#', 'YYYY-MM-DD-HH24:MI:SS')  and
lz.LAST_UPDATED_DATE < to_date('#pTO_DATE#', 'YYYY-MM-DD-HH24:MI:SS')  and  lz.last_updated_date in
 (select max(last_updated_date) from lz_incident group by incident_id)) where rn=1
and
LAST_UPDATED_DATE >=  to_date('#pFROM_DATE#', 'YYYY-MM-DD-HH24:MI:SS')
and LAST_UPDATED_DATE < to_date('#pTO_DATE#', 'YYYY-MM-DD-HH24:MI:SS')

[SURA]

Ray

1) If an issue in the source and it took time(!) to pull the data from the table.

In that case the option what i said will not help.

Whereas something wrong while process in or after tfm on that case if peek is used, you can find the data flow without hanging somewhere is the whole aim.

In other way to narrow down...

DS User

[jwiles]

Is this a parallel or server job? You have OracleOCI listed as the source stage....

Is the table lz_incident indexed on LAST_UPDATED_DATE?

[manoj_23sakthi]

Hi,
Could You please give the stats of this extract query in both Datastage and Toad ?
Please index columns for Joins
I am not sure about this table and it key column
so i blindly giving this query..
WITH VAL AS ( KEYCOLUMNS,rn )
(SELECT KEYCOLUMNS ,row_number() over (partition by incident_id order by
incident_id) rn from
lz_incident lz
where
lz.LAST_UPDATED_DATE >= to_date('#pFROM_DATE#', 'YYYY-MM-DD-HH24:MI:SS')
and lz.LAST_UPDATED_DATE < to_date('#pTO_DATE#', 'YYYY-MM-DD-HH24:MI:SS') )

SELECT LZ.*,A.rn FROM lz_incident LZ ,VAL A
WHERE
L2 KEYCOLUMNS = A.KEYCOLUMNS
AND rn= 1 AND LAST_UPDATED_DATE >= to_date('#pFROM_DATE#', 'YYYY-MM-DD-HH24:MI:SS')
and LAST_UPDATED_DATE < to_date('#pTO_DATE#', 'YYYY-MM-DD-HH24:MI:SS')

after this extract use sort stage and remove dups stage to get max(LAST_UPDATED_DATE) by its grouping column



Please let me know if i am wrong

[manoj_23sakthi]

Hi,
Could You please give the stats of this extract query in both Datastage and Toad ?
Please index columns for Joins
I am not sure about this table and it key column
so i blindly giving this query..
WITH VAL AS ( KEYCOLUMNS,rn )
(SELECT KEYCOLUMNS ,row_number() over (partition by incident_id order by
incident_id) rn from
lz_incident lz
where
lz.LAST_UPDATED_DATE >= to_date('#pFROM_DATE#', 'YYYY-MM-DD-HH24:MI:SS')
and lz.LAST_UPDATED_DATE < to_date('#pTO_DATE#', 'YYYY-MM-DD-HH24:MI:SS') )

SELECT LZ.*,A.rn FROM lz_incident LZ ,VAL A
WHERE
L2 KEYCOLUMNS = A.KEYCOLUMNS
AND rn= 1 AND LAST_UPDATED_DATE >= to_date('#pFROM_DATE#', 'YYYY-MM-DD-HH24:MI:SS')
and LAST_UPDATED_DATE < to_date('#pTO_DATE#', 'YYYY-MM-DD-HH24:MI:SS')

after this extract use sort stage and remove dups stage to get max(LAST_UPDATED_DATE) by its grouping column



Please let me know if i am wrong