OCI8: Multithreading Apps and Binding

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

        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,
                    (CONST OCISnapshot *) NULL, (OCISnapshot *) 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", strlen(":myString"), 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 Mon Dec 03 2001 - 20:47:05 CET