Improving the performance of OCI 9i Stage

[yaminids]

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

[rajkraj]

OCI 9i Stage is better option, the processing speed was depend on server load.

raj

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

[Sainath.Srinivasan]

You need to analyse your records to determine the best combination. OCI is better and efficient than ODBC.

[yaminids]

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

[amsh76]

Please look into the documents..its explained there.

[chulett]

Can you also tell me which one would yield a better result?

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.

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.

[ray.wurlod]

I just had a report from a customer as follows. Job design is

Code: Select all

OCI9 ----->  HashedFile

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.