11 Optimizing Performance of OCCI Applications

Using the Interfaces for Reading and Writing Multiple LOBs

Each of the readVectorOfxxx() and writeVectorOfxxx() interface uses the following parameters:

• conn, a Connection class object

• vec, a vector of LOB objects: Bfile, Blob, or Clob

• byteAmts, array of amounts, in bytes, for reading or writing

• charAmts, array of amounts, in characters, for reading or writing (only applicable for Clobs and NClobs)

• offsets, array of offsets, in bytes for Bfiles and Blobs, in characters for Clobs)

• buffers, array of buffer pointers

• bufferLengths, array of buffer lengths.

If there are errors in either reading or writing of one of the LOBs in the vector, the whole operation is cancelled. The byteAmts or charAmts parameters should be checked to determine the actual number of bytes or characters read or written.

Using Transparent Application Failover

To enable TAF, the connect string has to be configured for failover and registered on Connection (created from Environment, ConnectionPool and StatelessConnectionPool). To register the callback function, use the Connection Class interface setTAFNotify().

void Connection::setTAFNotify(
   int (*notifyFn)(
      Environment *env,
      Connection *conn,
      void *ctx,
      FailOverType foType,
      FailOverEventType foEvent),
   void *ctxTAF);

Note that TAF support for ConnectionPools does not include BACKUP and PRECONNECT clauses; these should not be used in the connect string.

Objects and Transparent Application Failover

Transparent application failover works with the OCCI navigational and associative access models and the object cache. In a non-RAC setup, you must ensure that the object type definitions and object OIDs in primary and backup instances are identical.

If the application receives ORA-25402: transaction must roll back error after the failover, then it must initiate a rollback to correctly reset the object cache on the client. If a transaction has not started before the failover, the application should still initiate a rollback after the failover to refresh the objects on the client object cache from the new instance.

Connection Pooling and Transparent Application Failover

If the transparent application failover feature is activated, connections created in a connection pool are also failed over. The application failover callback needs to be specified for each connection obtained from the connection pool; these connections will be failed over when used after the primary instance failure.

Introduction to Thread Safety

Threads are lightweight processes that exist within a larger process. Threads each share the same code and data segments, but have their own program counters, machine registers, and stack. Global and static variables are common to all threads, and a mutual exclusivity mechanism may be required to manage access to these variables from multiple threads within an application.

Once spawned, threads run asynchronously to one another. They can access common data elements and make OCCI calls in any order. Because of this shared access to data elements, a mechanism is required to maintain the integrity of data being accessed by multiple threads. The mechanism to manage data access takes the form of mutexes (mutual exclusivity locks), which ensure that no conflicts arise between multiple threads that are accessing shared resources within an application. In OCCI, mutexes are granted on an OCCI environment basis.

This thread safety feature of the Oracle database server and OCCI library enables developers to use OCCI in a multithreaded application with these added benefits:

• Multiple threads of execution can make OCCI calls with the same result as successive calls made by a single thread.

• When multiple threads make OCCI calls, there are no side effects between threads.

• Even if you do not write a multithreaded program, you do not pay any performance penalty for including thread-safe OCCI calls.

• Use of multiple threads can improve program performance. You can discern gains on multiprocessor systems where threads run concurrently on separate processors, and on single processor systems where overlap can occur between slower operations and faster operations.

In addition to client/server applications, where the client can be a multithreaded program, thread safety is typically used in three-tier or client/agent/server architectures. In this architecture, the client is concerned only with presentation services. The agent (or application server) processes the application logic for the client application. Typically, this relationship is a many-to-one relationship, with multiple clients sharing the same application server.

The server tier in the three-tier architecture is an Oracle database server. The applications server (agent) supports multithreading, with each thread serving a separate client application. In an Oracle environment, this middle-tier application server is an OCCI or precompiler program.

Implementing Thread Safety

In order to take advantage of thread safety by using OCCI, an application must be running in a thread-safe operating system. Then the application must inform OCCI that the application is running in multithreaded mode by specifying THREADED_MUTEXED or THREADED_UNMUTEXED for the mode parameter of the createEnvironment() method. For example, to turn on mutual exclusivity locking, issue the following statement:

Environment *env = Environment::createEnvironment( 
          Environment::THREADED_MUTEXED);

Note that once createEnvironment is called with THREADED_MUTEXED or THREADED_UNMUTEXED, all subsequent calls to the createEnvironment method must also be made with THREADED_MUTEXED or THREADED_UNMUTEXED modes.

If a multithreaded application is running in a thread-safe operating system, then the OCCI library will manage mutexes for the application on a for each-OCCI-environment basis. However, you can override this feature and have your application maintain its own mutex scheme. This is done by specifying a mode value of THREADED_UNMUTEXED to the createEnvironment() method.

Serialization

As an application programmer, you have two basic options regarding concurrency in a multithreaded application:

• Automatic serialization, in which you utilize OTIS's transparent mechanisms

• Application-provided serialization, in which you manage the contingencies involved in maintaining multiple threads

setDataBuffer() Method

For high performance applications, OCCI provides the setDataBuffer method whereby the data buffer is managed by the application. The following example shows the setDataBuffer() method:

void setDataBuffer(int paramIndex,
   void *buffer,
   Type type,
   sb4 size,
   ub2 *length,
   sb2 *ind = NULL, 
   ub2 *rc = NULL); 

The following parameters are used in the previous method example:

• paramIndex: Parameter number

• buffer: Data buffer containing data

• type: Type of the data in the data buffer

• size: Size of the data buffer

• length: Current length of data in the data buffer

• ind: Indicator information. This indicates whether the data is NULL or not. For parameterized statements, a value of -1 means a NULL value is to be inserted. For data returned from callable statements, a value of -1 means NULL data is retrieved.

• rc: Return code. This variable is not applicable to data provided to the Statement method. However, for data returned from callable statements, the return code specifies parameter-specific error numbers.

Not all datatypes can be provided and retrieved by means of the setDataBuffer() method. For instance, C++ Standard Library strings cannot be provided with the setDataBuffer() interface.

There is an important difference between the data provided by the setxxx() methods and setDataBuffer() method. When data is copied in the setxxx() methods, the original can change once the data is copied. For example, you can use a setString(str1) method, then change the value of str1 prior to execute. The value of str1 that is used is the value at the time setString(str1) is called. However, for data provided by means of the setDataBuffer() method, the buffer must remain valid until the execution is completed.

If iterative executes or the executeArrayUpdate() method is used, then data for multiple rows and iterations can be provided in a single buffer. In this case, the data for the ith iteration is at buffer + (i-1) *size address and the length, indicator, and return codes are at *(length + i), *(ind + i), and *(rc + i) respectively.

This interface is also meant for use with array executions and callable statements that have array or OUT bind parameters.

The same method is available in the ResultSet class to retrieve data without re-allocating the buffer for each fetch.

executeArrayUpdate() Method

If all data is provided with the setDataBuffer() methods or output streams (that is, no setxxx() methods besides setDataBuffer() or getStream() are called), then there is a simplified way of doing iterative execution.

In this case, you should not call setMaxIterations() and setMaxParamSize(). Instead, call the setDataBuffer() or getStream() method for each parameter with the appropriate size arrays to provide data for each iteration, followed by the executeArrayUpdate(int arrayLength) method. The arrayLength parameter specifies the number of elements provided in each buffer. Essentially, this is same as setting the number of iterations to arrayLength and executing the statement.

Since the stream parameters are specified only once, they can be used with array executes as well. However, if any setxxx() methods are used, then the addIteration() method is called to provide data for multiple rows. To compare the two approaches, consider an example that inserts two employees in the emp table:

Statement *stmt = conn->createStatement("insert into emp (id, ename) 
   values(:1, :2)"); 
char enames[2][] = {"SMITH", "MARTIN"};
ub2 enameLen[2];
for (int i = 0; i < 2; i++)
   enameLen[i] = strlen(enames[i] + 1);
stmt->setMaxIteration(2);             // set maximum number of iterations
stmt->setInt(1, 7369);                // specify data for the first row
stmt->setDataBuffer(2, enames, OCCI_SQLT_STR, sizeof(ename[0]), &enameLen);
stmt->addIteration();
stmt->setInt(1, 7654);                // specify data for the second row

// a setDatBuffer is unnecessary for the second bind parameter as data 
// provided through setDataBuffer is specified only once.
stmt->executeUpdate();

However, if the first parameter could also be provided through the setDataBuffer() interface, then, instead of the addIteration() method, you would use the executeArrayUpdate() method:

stmt ->setSQL("insert into emp (id, ename) values (:1, :2)");
char enames[2][] = {"SMITH", "MARTIN"};
ub2 enameLen[2];
for (int i = 0; i < 2; i++)
   enameLen[i] = strlen(enames[i] + 1);
int ids[2] = {7369, 7654}; 
ub2 idLen[2] = {sizeof(ids[0], sizeof(ids[1])};
stmt->setDataBuffer(1, ids, OCCIINT, sizeof(ids[0]), &idLen);
stmt->setDataBuffer(2, enames, OCCI_SQLT_STR, sizeof(ename[0]), &len);
stmt->executeArrayUpdate(2);          // data for two rows is inserted.