OCI8: Multithreading Apps and Binding

From: Rolf Unger <rolf.unger_at_ctilabs.de>
Date: 4 Dec 2001 05:50:00 -0800
Message-ID: <32fe19ad.0112040550.43a9ca13@posting.google.com>

Hi Oracle Gurus,

I have to questions regarding OCI-Programming. I use OCI 8.05 on a WinNT machine. I started with ORACLE two years ago, so now it's time to face some performance issues. I have a multithreaded plain C Application that runs permanently 24 hours on 7 days. I use between 4 and 10 session, each with it's own environment, and have about 150 threads sharing this 10 sessions for concurrent database access. (Maybe not the best design, but a really tough job to change it to something like a "message queue serialised" architecture).

As I know from the documentation (Oracle Call Interface Programmer's Guide, Release 8.0, Chapter 7 "Thread Safety") the OCI library maintains a mutex for each opened environment, if I start the application with

    OCIInitialize(OCI_THREADED,...)
and

    OCIEnvInit((OCIEnv **) envhp, OCI_DEFAULT, ...)

Does this really mean that any OCISomething-Function has to wait on the mutex even if it's just a simple OCIHandleAlloc() or OCIHandleFree()?!
So every OCISomething in the following typical function is a potential point of context switches of the OS scheduler?!

// ----------------------------------------------------------

sgh[] is an array where each element is a structure that contains the env-handle, the server and server context handle and the error handle.

int db_Sql (int nIdx, char* pszStmt)
{

   sb4 status;
   OCIStmt *stmthp = NULL;    

   if ( OCIHandleAlloc((dvoid*) sgh[nIdx].envhp, (dvoid**) &stmthp,

                        OCI_HTYPE_STMT, 0, (dvoid**) 0) )
   	return OCI_EXIT_FAILURE;

// As 'OCIStmtPrepare()' is processed locally and independent of a
// specified database server (no service context handle as parameter),
// don't execute it inside the critical section

   if (status = OCIStmtPrepare(stmthp, sgh[nIdx].errhp, (text *) pszStmt,

                    (ub4) strlen(pszStmt), OCI_NTV_SYNTAX, OCI_DEFAULT))
   {
       checkerr(sgh[nIdx].errhp, status);
       goto cleanup;

   }

// to protect the transaction state of the global service context
   EnterCriticalSection(&g_csProtectServiceContext[nIdx]);

   status = OCIStmtExecute(sgh[nIdx].svchp, stmthp, sgh[nIdx].errhp,

                           (ub4) 1, (ub4) 0, NULL, NULL, OCI_DEFAULT);

   if ( status )
   {

      checkerr(sgh[nIdx].errhp, status);
      OCITransRollback(sgh[nIdx].svchp, sgh[nIdx].errhp, (ub4) 0);
      status = OCI_EXIT_FAILURE;

   }
   else

      OCITransCommit(sgh[nIdx].svchp, sgh[nIdx].errhp, (ub4) 0);

   LeaveCriticalSection(&g_csProtectServiceContext[nIdx]);

cleanup:

   if ( stmthp ) {

      OCIHandleFree((dvoid *) stmthp, OCI_HTYPE_STMT);    }
   return status;
}

// ----------------------------------------------------------

I use this function mostly in the following way

 sprintf(szStmt, "update this_table set columnA=%d where columnB='%s'",

         intPar, szKey);
or
 sprintf( szStmt, "BEGIN any.method('%s',%d,%d); END;",

          szKey, intPar1, intPar2);

 db_sql( nSerial%10, szStmt);

I had to introduce another Mutex around OCIExecute and OCICommit/Rollback, to avoid that successfully executed data is rolled back by another thread that get's the CPU timeslice directly after the OCIExecute() of the first thread, fails in his execute and does a rollback on the complete transaction.

Now I suspect the two mutexes to produce race conditions or dead locks, because sometimes under heavy load, all threads are blocked and if I shutdown the application with the UserInterface Thread (does a log out for all sessions), all sessions are finished properly. They are no longer visible in the Instance Manager of Oracle's Enterprise Manager Suite.
On other occasions if I kill one or more sessions on the database server, all threads seems to continue processing again. Of course any OCI-function call that does a round trip to the server returns with "ORA..Session terminated" errors, but at least they no longer block somethere in the OCI-library.

I tend to re-design the mutex concept and use a single mutex per session/env, that is accessed whenever a thread touches the first Oci-function and released when it leaves such functions as the above 'db_Sql()'. I will do it for all type of Database access also for selects and not only for updates and inserts. I hope I can then call 'OCIEnvInit()' with OCI_ENV_NO_MUTEX, so that only my own mutex per environment is used.

Any thoughts, if that is a good idea? For me it seems to be a very rigid serialisation method..

2) I'd like to use bind-variables in the where clause of my queries,

   in order to bind input variables with OCIBindByPosition(). The OCI    documentation is not so bad on this topic, I also made some    successfull experiments with OCIBindArrayStruct().    Of course this means that I can no longer use a simple db_sql()    with one string as parameter but have to discern between situation    that need different number and different types of Parameters. And    these Types are exactly my problem:
   In the above db_sql() 'sprintf()' does the job, whenever I need to    specify a VARCHAR column as key I just inclose it in quotes, no    matter if the C datatype is an integer or a string.    My problem is the column that I can use in most of the    tables/queries as key has 11 or 12 digits, so it exceeds the size    of a 32 bit integer.
  ==> HOW do I bind a LONGLONG (or 64bit integer) to a placeholder.    Currently the value in the C-Application is stored as a string,    mainly because this is handy with the 'sprintf'-method and I do not    need to do calculations with this value.   ==> Can I use oratype SQLT_CHR to just bind the C character buffer

      to the key column ?

   From PL/SQL I know that a query like

   VARIABLE myString VARCHAR2(14)

   myString := '4567';
   SELECT bla FROM INTO v_dummy WHERE numeric_col = :myString;

   is something that you should avoid, as the implicit type conversion    may be an obstacle for the optimizer to use an index.

   If I do it in the same manner in OCI

     OCIStmtPrepare( "SELECT bla FROM WHERE numeric_col = :myString" );

     OCIBindByName( ..., ":myString", 9, pszMyBuffer, ..., SQLT_CHR, ...)

   Does this mean that the 12 digit string in pszBuffer is converted    due to the SQLT_CHR specifier in the OCIBind to a VARCHAR type on    the client and later on on the database server back to a number to    process the WHERE condition? If this is the case, does this really    bring a performance gain?

3) Minor question to binding and performance

   If I use a stored procedure with only IN parameters for some    complex update on several tables, does it really make a big    difference if the database sees

   BEGIN myProcedure(:bindNum1,:bindNum2,:bindStr3,:bindStr4); END;

   and not just the literal values:

   BEGIN myProcedure(12345,485,'some','data'); END;

Feel free to pick out only one topic from the above ;->

And I am also happy for any hints on good books about OCI, there is lots of PL/SQL material and quite a lot on JDBC but nearly nothing on OCI. Currently I rely completely on the Oracle Docs, and I'm not so happy with the examples. Somehow they seem to miss out the parts where it's getting interesting.

Cheers, Rolf. Received on Tue Dec 04 2001 - 07:50:00 CST