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
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 ?
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
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?
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.
26 millions of records are in the file and not in the target table.
Are u getting a lot of records in UV table?or check the data skew for current PI. Also AMPCHECK option doesnt effect performance.
No records are going to UV table. Also it is using UPI.
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 ?
We are loading in to an empty table and this is a refresh load.
if you are loading empty table, can you try fastload ? mload has to go through the way of a work table.
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.