Multi-threaded OCI Application Does Not Scale

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 - 17:37:53 CST