Page 1 of 1
Performance issue while inserting into Oracle table
Posted: Fri Jan 18, 2008 10:14 am
by 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
Posted: Fri Jan 18, 2008 3:18 pm
by 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?
Posted: Mon Jan 21, 2008 3:48 pm
by 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.
Re: Performance issue while inserting into Oracle table
Posted: Thu Jan 24, 2008 2:18 pm
by 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
Posted: Thu Jan 24, 2008 5:53 pm
by 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.
Posted: Thu Jan 24, 2008 6:11 pm
by lstsaur
Also purely curious, as a DataStage developer, how would you even think about to check the network card's speed?
Posted: Thu Jan 24, 2008 6:12 pm
by lstsaur
Also purely curious, as a DataStage developer, how would you even think about to check the network card's speed?
Posted: Thu Jan 24, 2008 11:22 pm
by 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.