That many columns means that it might be chaining the rows (nothing in the
OCI app you can do about that) and thus an insert doesn't hit 1 block but 3
which would be a massive performance hit. You might look on Tom's site
(asktom.oracle.com) and see if there is anything about chaining and bwt (big
wide tables)
I'll do some research today.
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.0301230734.61002734_at_posting.google.com...
> 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:54:42 CST
