OCILIB Direct path vs SQL LOADER (sqlldr)

[Sifang Li]

Hi,

I am now trying to decide with way to go to maximize the performance of
insertions to a table that has primary keys.
I have done some benchmark tests myself using sqlldr and OCILIB DP. I found I
could load 90k rows/sec using sqlldr which is very impressive. Instead I could
only achieve 20k insertions / sec using OCILIB direct path. I am very
interested on what I am doing wrong if any. Shouldn't I expect similar
performance between these two approach?

• I have allocated a very big DP buffer, but I found the OCI_DirPathGetMaxRows only returns a value of 30+K no matter what the buffer size I give;
• have to do OCI_DirPathSetParallel(ocilib_dp_, FALSE) due to my table is not (can not be) empty (same for sqlldr side tests)
• Set the date cache on the OCILIB (100) - not much difference
• OCI_DirPathSetNoLog set true
• OCILIB version 3.9.0; oracle client version 11.2.0.1.0

[Vincent Rogier]

Hi,

send me by email :

• your sqlldr control file
• your OCILIB source code

I'll see what is wrong with it.

vincent

[Sifang Li]

Sent to vince.rogier@ocilib.net

Thanks!