Performance

[krisna]

Hi,

We are loading data from a sequential file to oracle and volume is 650 millions of records. It took 45 hours to load 318 million records, performance wise it's very poor. Job design is like this

Code: Select all

sequential file --> transformer --> oracle enterprise stage
                               |
                               V
                          oracle enterprise stage

in transformer we are using a transformation rule to check for spaces(4 columns) move to one oracle enterprise stage other move to another oracle stage.

we are using oracle load options as direct = false, parallel = true.

in oracle enterprise stage

write method = load
write mode = append

Please help us to improve performance.

Thanks in advance.

[SURA]

Again this is relates to performance.

Did you made any changes in Number Of readers per node ?

All the records are in the same file / set of files?

Find a way to split the data and load one after another.

DS User

[vmcburney]

There are several things that could slow your job down. 1) make sure there is no sorting going on as this is a huge overhead. 2) Make sure you are reading from the sequential file with multiple reads so you do not have a sequential bottleneck. 3) From memory the version 7 Oracle Enterprise stage first has to create an Oracle bulk load file and then call the bulk load command. Sometimes it is faster to create the bulk load file using the Sequential file stage and to call Oracle bulk load after the file is ready.

Does anyone remember whether the version 7 Oracle Enterprise stage is slow to build the bulk load file?

[chulett]

I seem to recall it pushing data through a named pipe.

Curious why "direct=false"? At that point you've got a conventional load rather than a bulk load... indexes?

[chandra.shekhar@tcs.com]

You can also use Oracle Connector stage as your target since it also works in parallel mode.

[chulett]

... if only it was in the 7.x release.

[nagarjuna]

Do you have indexes on the target table ? Check the possibility of loading in direct load ..

[krisna]

It's not a fixed width file. We are using version 7. We have indexes on tables.