Re: Multi-threaded OCI Application Does Not Scale

Yes, I am using bind variables with OCIBindByPos() and OCIBindArrayOfStruct(). The INSERT statement is identical for each thread and is completely static, so the variable binding process occurs exactly once for each thread. One other piece of information that might have some bearing is that the target database table is structurally large (974 columns - almost at the limit). I am familiar with the direct path load interface you refer to, which is undoubtedly very fast, but I wish to avoid it because of its restrictions (no support for remote object loading, triggers, CHECK constraints, referential constraints, etc.) The real issue here, however, is not that my program is particularly slow. On the contrary, the reason I have not devoted much effort to researching program performance before now (it's 1.5 years old) is that in all but one of our customer systems, a single loader thread provides perfectly adequate near-realtime performance. The real issue is that the program does not scale, and it should. It hasn't become a business obstacle yet, but it could. If we encountered a potential customer with an extremely high volume datastream, we would have to turn away the business because the program's total import rate is bound by something other than the database or hardware (which, of course, is scalable given enough $$$).

Cary

"Jim Kennedy" <kennedy-down_with_spammers_at_attbi.com> wrote in message news:<gUIX9.2045$4y2.471_at_sccrnsc04>...
> 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 Thu Jan 23 2003 - 09:34:12 CST