All Forums Analytics
Sandi 12 posts Joined 10/05
10 Oct 2008
Reducing runtime for a MLOAD job

Hi All,I have a monthly MLOAD job which loads around 26 millions of records, but it takes 16 - 20 hours to complete loading. Can someone give some tips to improve performance and reduce runtime? PS: I don't prefer changing MLOAD to any other utilities.Thanks a lot for your time and help

joedsilva 505 posts Joined 07/05
10 Oct 2008

very little information to give useful comments...But these would be the direction on which I would investigateWhat is the number of records on the table ? is it partitioned ? on what ? how many records per partiton ? do you load across partitions ?Is your table set/multiset ?Do you have UPI/NUPI ?Are there too many duplicates on the PI ?What is the width of the records being loaded ?Is the performance issue on the network throughput Versus DB ?

Sandi 12 posts Joined 10/05
12 Oct 2008

Hi,Please find the details belowWhat is the number of records on the table ? is it partitioned ? on what ? how many records per partiton ? do you load across partitions ?Is your table set/multiset ?No: of records - 26million. It is not partitioned.Table is a SET tableDo you have UPI/NUPI ?I have UPI defined to the table. It is a combination of 4 columns.Are there too many duplicates on the PI ?It is a UPI . So NO duplicatesWhat is the width of the records being loaded ?Record length of the file is 2500 charactersIs the performance issue on the network throughput Versus DB ?There is no performance issue as far as i know.Please let me know if you need more details

Sandi 12 posts Joined 10/05
13 Oct 2008

Hi,I have noticed that the options AMPCHECK IS ALL and also 40 input files are concatenated. CHKPOINT is default 15mts. Table is not a fall back. Will this affect performance?

joedsilva 505 posts Joined 07/05
13 Oct 2008

Just to experiment, can you try concatenating all the files and do a fastload to an empty table ?Also is your 26 million the number of records you are loading or the number of records in the table ?If the table is huge, you might find a lot of performance issues doing insert into it. (which can be fixed to certain extend by using appropriate partitioning techniques)Do a fastload to an empty table, check it's speed, and try doing a select insert into the target table and check the time for that step.

Sandi 12 posts Joined 10/05
14 Oct 2008

26 millions of records are in the file and not in the target table.

amit_dwh 13 posts Joined 06/06
22 Oct 2008

Are u getting a lot of records in UV table?or check the data skew for current PI. Also AMPCHECK option doesnt effect performance.

Sandi 12 posts Joined 10/05
23 Oct 2008

No records are going to UV table. Also it is using UPI.

joedsilva 505 posts Joined 07/05
23 Oct 2008

What is the number of existing records in the table ?Also can you load the file to an empty table and tell us if that's any faster ?

Sandi 12 posts Joined 10/05
24 Oct 2008

We are loading in to an empty table and this is a refresh load.

joedsilva 505 posts Joined 07/05
24 Oct 2008

if you are loading empty table, can you try fastload ? mload has to go through the way of a work table.

Roopalini 13 posts Joined 05/08
27 Nov 2012

Hi Sandi,
Can you tell me what measures you took to improve the performance of the MLOAD job? I am in a similar situation now and looking for suggestions to improve the performance of my load.
Thanks
 
 
 
 
 
 
 

You must sign in to leave a comment.