Looks like you are using the array interface so that is good. Are you using
bind variables? If not you must. Do you open the statement once, bind,
execute, rebind, execute, etc. (not closing the cursor) If not look into
doing that. It should be very fast. Another alternative is that there is a
native sqlloader api that is supposed to fly. I haven't used it, but it
might be something to consider.
Jim
--
Replace part of the email address: kennedy-down_with_spammers_at_attbi.com
with family. Remove the negative part, keep the minus sign. You can figure
it out.
"Cary Lapoint" <cary_at_nams.net> wrote in message
news:6b0e1783.0301221537.5849e673_at_posting.google.com...
> Hello,
>
> I have written a multi-threaded (pthreads) OCI program that INSERT's
> large quantities of data into an Oracle database. It works very
> reliably but does not scale well at all. To be specific, as the
> number of threads increases, the total import rate decreases slightly!
> However, as the number of separate concurrent program instances
> (processes) increases, the total import rate increases almost linearly
> until maxing out available system resources. The issue is currently
> under review by Oracle Support, but since it is a severity 4 problem,
> it might never be resolved. These are the facts. Identical behavior
> has been observed on a Solaris system running Oracle 8.1.7.0.0 and a
> Linux box running Oracle 8.1.7.0.1. Each loader thread has its own
> dedicated environment handle created using OCIEnvCreate() with
> OCI_THREADED | OCI_ENV_NO_MUTEX | OCI_OBJECT and a single session.
> Timing code embedded in the program shows that all examined
> sub-operations except one actually do scale reasonably well.
> Unfortunately, the one that does not, the OCIStmtExecute() call that
> performs the array INSERT's, dominates the overall program
> performance. Has anyone observed this behavior before? Can anyone
> cite/describe a database-intensive multi-threaded OCI application that
> does scale well? Any advice? Should it even be necessary to use
> OCI_THREADED since I use completely separate environment handles
> (still a little unclear on this point)? Thanks in advance.
>
>
> Cary
Received on Wed Jan 22 2003 - 20:59:25 CST
