Re: Multi-threaded OCI Application Does Not Scale

From: Cary Lapoint <cary_at_nams.net>
Date: 24 Jan 2003 08:25:14 -0800
Message-ID: <6b0e1783.0301240825.68f7a1ab_at_posting.google.com>


In my code, after some simple initialization the first action performed by each thread is to call a database "logon" routine, passing it a pointer to a locally defined structure containing pointers to an OCIEnv, an OCIError, an OCISvcCtx, an OCIServer, and an OCISession. The "logon" routine then initializes each of these objects, which should be unique to each thread. I really do not see how multiple threads could be sharing the same connection / session. Unless my program isn't actually creating separate environment handles? If a single environment handle is being shared amongst the threads, then OCI library calls would be serialized. I actually ran across a post somewhere by an Oracle employee, which suggested that OCIEnvCreate() or maybe OCIEnvInit() might actually check if an environment had already been created and not create a new one, but I disregarded it since this is not what the docs say. Does anyone know anything about this? Is it permissible to have multiple OCIEnvCreate() calls within the same process, and do they actually establish distinct environment handles? Thanks.

Cary

anup.jalan_at_rave-tech.com (Anup Jalan) wrote in message news:<532afd4.0301240220.7631fdc8_at_posting.google.com>...
> Possibly, all the threads are using the same connection / session.
>
> As one session can execute only one command at a time, a single
> session used by all threads effectively makes it a single threaded
> program! You can monitor this on the server side through V$SESSIONS to
> see how many sessions your program has created.
>
> If this is the problem, let each thread create a different session.
>
> Anup Jalan
> Rave Technologies
> www.rave-tech.com
>
>
> cary_at_nams.net (Cary Lapoint) wrote in message news:<6b0e1783.0301231314.1c05a725_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 Fri Jan 24 2003 - 17:25:14 CET

Original text of this message