Hello friends,
I am trying different options to load data into Oracle database.
Initailly I used ODBC Stage and was able to process about 800 rows per second. I used OCI 9i Stage, in hope of improving the performance but was disappointed with it's perforamnce. I was able to process only 200 rows per second. Am I doing anything wrong? What values should I use for "Array Size", "Transaction Size" etc to get better results.
Thank you very much in advance
Yamini
Improving the performance of OCI 9i Stage
Moderators: chulett, rschirm, roy
Re: Improving the performance of OCI 9i Stage
OCI 9i Stage is better option, the processing speed was depend on server load.
raj
raj
yaminids wrote:Hello friends,
I am trying different options to load data into Oracle database.
Initailly I used ODBC Stage and was able to process about 800 rows per second. I used OCI 9i Stage, in hope of improving the performance but was disappointed with it's perforamnce. I was able to process only 200 rows per second. Am I doing anything wrong? What values should I use for "Array Size", "Transaction Size" etc to get better results.
Thank you very much in advance
Yamini
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom
Improving the performance of OCI 9i Stage
Hi Sai,
Even though the number of records to be written depends on the network, can you tell me where I am supposed to set this number.
Is it in
1) Array size field or
2) Transaction size field or
3) Rows per transaction field
in the OCI 9i Stage. Can you also tell me which one would yield a better result?
Thanks a lot
Yamini
Even though the number of records to be written depends on the network, can you tell me where I am supposed to set this number.
Is it in
1) Array size field or
2) Transaction size field or
3) Rows per transaction field
in the OCI 9i Stage. Can you also tell me which one would yield a better result?
Thanks a lot
Yamini
Re: Improving the performance of OCI 9i Stage
Unfortunately, no. There are too many variables involved - variables related to your particular server, database design, table layout, etc, etc - to hand out one-size-fits-all advice.yaminids wrote:Can you also tell me which one would yield a better result?
Array size is probably your best bet for a cheap 'performance improvement'. Run some tests, bump it up and see what happens.
Don't worry about the 'Transaction size' on the General tab. Per the help: "This field exists for backwards compatability, but it is ignored for release 3.0 and later of the Plug-in." In other words, the version you are using right now. Use the 'Rows per transaction' setting on the 'Transaction Handling' tab instead.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
I just had a report from a customer as follows. Job design is There are 34756 rows to move. Array size is set to 1000. The job loaded 34000 rows, and apparently discarded the incomplete array.
Further investigation is required.
Code: Select all
OCI9 -----> HashedFile
Further investigation is required.
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.