Performance issue while inserting into Oracle table

[Apotluri]

Hi Friends,

Greetings

This is a PX environment

I am facing performance issue while inserting 2,800,000 rows from a dataset to Oracle table using Oracle OCI Bulk stage

The job design is as follows

dataset--->copy stage----> OraOCI Bulk stage

It is taking 2 hours to insert nearly 3 million records
I have one single node in config.apt file
all partitions kept as default as they come with stages

perviously I tried with Oracle enterprise stage, by disabling constraints and Rebuilding Indexes options, but could not help?

Any suggestions will be great

[ray.wurlod]

With a single node confiuration file everything is executed sequentially irrespective of partitioning settings in stages.

It does seem like a long time. Try not automatically executing sqlldr from DataStage but executing it subsequently, manually, with your DBA advising what's happening.

Are there very many indexes on the table? How are you handling those?

[DSguru2B]

Try using a two node configuration and slowly go higher in the number of nodes to find optimal time. Use Oracle Enterprise stage with it.

[Apotluri]

--------------

Now I got the problem resolved

Not either from the DataStage side or from Oracle side

this is network card issue, there were lot of collissions between DataStage server and Oracle sever, because the network speed was set to very low . Now the unix guys increased the network speed to the max speed to 100 mbps.

Inserting 3 million rows in to Oracle now taking just 4 minutes, :D

Thanks for your support

Regards

Arvind

[ray.wurlod]

Purely curious, can you ask them why they set the speed low? It doesn't save electricity or anything like that. Seems to me a strange decision to make.

[lstsaur]

Also purely curious, as a DataStage developer, how would you even think about to check the network card's speed?

[lstsaur]

Also purely curious, as a DataStage developer, how would you even think about to check the network card's speed?

[Teej]

Something tells me that it defaulted to 10mbps for some reason or another.

As for testing the speed, simply throw something across the network using a bandwidth-intensive program (such as FTP), and convert it to bits, and assume 60%-80% efficiency.

My first question would have been: How long is the record? There have been some who like to throw around a megabyte-sized records and wondering why it is pushing 300 rows per second or something insanely low.