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
Performance issue while inserting into Oracle table
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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?
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?
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Re: Performance issue while inserting into Oracle table
--------------
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
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
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
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.