Improving the performance of transformer stage

ravi468 · Post by **ravi468** » Sun Mar 11, 2007 10:09 pm

hi ,
i have a problem with the performance.
My source is a csv file and it loads into a oracle database.
we have a 100000 records only daily basis.
It is taking a quite long time to load these files.
the number of columns we have are 15.

please le tme know if any one can help me.

thanks in advance

narasimha · Post by **narasimha** » Sun Mar 11, 2007 10:25 pm

Welcome Aboard!

Use Oracle Bulk loading, you can load it in a few minutes.

kumar_s · Post by **kumar_s** » Sun Mar 11, 2007 11:35 pm

It depends up on the Job design, Transformaions used (if at all), length of the columns (which is not a very big factor unless other wise its LOB).
You need to identify the bottleneck. If the source is in different nodes which involves network bandwidth you ll need to work for that. If its on Database, you can use Bulk load as suggested. If its on transformations, perhaps you can post the list of options you use, and get suggestions from the form.
You can try Multiple readers per node and other similar options available to read parallely for IO contention.

oacvb · Post by **oacvb** » Mon Mar 12, 2007 12:36 am

Is any transformation logic built in at transformer stage? if not as suggested use Oracle Bulk Loader. Can we get entire job design information to analyze the problem.

DSguru2B · Post by **DSguru2B** » Mon Mar 12, 2007 6:45 am

...or perhaps the Oracle Enterprise stage.

ray.wurlod · Post by **ray.wurlod** » Mon Mar 12, 2007 7:56 am

Are you sure the bottleneck is in the Transformer stage, or is this simply a convenient scapegoat? What throughput do you get if you replace the Oracle stage with a Peek stage? What does that reveal to you?

What settings are you using in your Oracle stage? What are the settings of the two environment variables that control Oracle commit intervals?