OraFAQ Forum: Data Integration » problem in ETL with large data

Home » Developer & Programmer » Data Integration » problem in ETL with large data

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

problem in ETL with large data [message #153445]

Mon, 02 January 2006 07:51

samar@infoprise
Messages: 4
Registered: January 2006
Location: lebanon

Junior Member

i am working with ETL, but i am facing a problem which is when have large amount of data (1 million records) the ETL takes about 4 to 5 hours to be finished which is a long time. so is there any way to optimize this time.

Any comment may be helpfull for me.
Thanks.

Report message to a moderator

Re: problem in ETL with large data [message #155921 is a reply to message #153445]

Thu, 19 January 2006 12:22

d.fowler
Messages: 21
Registered: January 2006
Location: Missouri

Junior Member

Is this a mapping issue? Did a package get created from the map process. My have to run trace on generated plsql package.

How was output process step to final table defined. Record at a time format? error check at record level slow.....

Report message to a moderator

Re: problem in ETL with large data [message #155975 is a reply to message #155921]

Fri, 20 January 2006 01:43

Maaher
Messages: 7065
Registered: December 2001

Senior Member

Dave is right: run a trace on the package.

You have to identify what part is slowing the process down. You might want to use a test source that is somewhat smaller. One option might be to use a log procedure (in an autonomous transaction) to identify at what timestamp you arrive at a given point in your process.

MHE

Report message to a moderator

Re: problem in ETL with large data [message #157895 is a reply to message #153445]

Tue, 07 February 2006 01:33

amits76
Messages: 5
Registered: February 2006

Junior Member

samar,
There are quite a few points you might have to look at.
1) Look whether all columns on which you have made a join are indexed or not.
2) Analyse all the tables,if possible analyse the schema.
3) if you are using filters and your join condition has where clause ,shift those to filters...
4) Avoid using union operators.
5) Sort your table data.
6) Carry forward onlt those columns which you need from any source table.
7) Run mapping in Set based only.
Cool

Give commit freq as 1000.
9) Break the big maping into small mapping.
.
Hope this helps.

Report message to a moderator

Re: problem in ETL with large data [message #157906 is a reply to message #157895]

Tue, 07 February 2006 02:24

samarsheet
Messages: 7
Registered: November 2005

Junior Member

Thank u for your help

Report message to a moderator

Re: problem in ETL with large data [message #159126 is a reply to message #153445]

Thu, 16 February 2006 05:34

jayadesh
Messages: 10
Registered: February 2006
Location: INDIA

Junior Member

HELLO GUYS,
ONCE U LOAD DATA IN TARGET ,WHICH IS HAVING INDEXED COLUMNS OBVIOUSLY IT WILL TAKE TIME.
SO TRY TO DROP THE INDEX IN PRE-SESSION AND CREATE IT IN POST-SESSION SQL.
IT MAY HELP UPTO ANY EXTEND.
THANKS

Report message to a moderator

Previous Topic:	WB 10gR1 and Asynchronous CDC
Next Topic:	Looping in OWB 10g

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Dec 28 01:19:33 CST 2024