This is the session pool handle.

It is allocated using OCIHandleAlloc(). It must be passed to OCISessionPoolCreate() and OCISessionPoolDestroy(). It has the attribute type OCI_HTYPE_SPOOL.

An example of the OCIHandleAlloc() call follows:

OCISPool *spoolhp;
OCIHandleAlloc((void *) envhp, (void **) &spoolhp, OCI_HTYPE_SPOOL, 
                        (size_t) 0, (void **) 0));

For an environment handle, multiple session pools can be created.

This is the authentication information handle.

It is allocated using OCIHandleAlloc(). It is passed to OCISessionGet(). It supports all the attributes that are supported for a user session handle. The authentication information handle has the attribute type OCI_HTYPE_AUTHINFO (see Table 3-1).

An example of the OCIHandleAlloc() call follows:

OCIAuthInfo *authp;
OCIHandleAlloc((void *) envhp, (void **) &authp, OCI_HTYPE_AUTHINFO, 
                      (size_t) 0, (void **) 0));

Session pooling requires that the pool handle OCI_HTYPE_SPOOL be allocated by calling OCIHandleAlloc().

Multiple pools can be created for a given environment handle. For a single session pool, here is an allocation example:

OCISPool *poolhp; 
OCIHandleAlloc((void *) envhp, (void **) &poolhp, OCI_HTYPE_SPOOL, (size_t) 0,
               (void **) 0));

You can use the function OCISessionPoolCreate() to create the session pool.

Here is an example of how to use this call:

OCISessionPoolCreate(envhp, errhp, poolhp, (OraText **)&poolName, 
              (ub4 *)&poolNameLen, database, 
              (ub4)strlen((const signed char *)database),
              sessMin, sessMax, sessIncr,
              (OraText *)appusername,
              (ub4)strlen((const signed char *)appusername),
              (OraText *)apppassword,
              (ub4)strlen((const signed char *)apppassword),
              OCI_DEFAULT);

You can use these calls to log on to the database in session pooling mode.

• OCILogon2()

  This is the simplest call. However, it does not give the user the option of using tagging. Here is an example of how to use OCILogon2() to log on to the database in session pooling mode:

  for (i = 0; i < MAXTHREADS; ++i) 
  { 
    OCILogon2(envhp, errhp, &svchp[i], "hr", 2, "hr", 2, poolName,
              poolNameLen, OCI_LOGON2_SPOOL));
  }
  

• OCISessionGet()

  This is the recommended call to use. It gives the user the option of using tagging to label sessions in the pool, which makes it easier to retrieve specific sessions. An example of using OCISessionGet() follows. It is taken from cdemosp.c in the demo directory.

  OCISessionGet(envhp, errhp, &svchp, authInfop,
               (OraText *)database,strlen(database), tag,
               strlen(tag), &retTag, &retTagLen, &found, 
               OCI_SESSGET_SPOOL);
  

  When using service contexts obtained from an OCI session pool, you are required to use the service context returned by OCISessionGet() (or OCILogon2()), and not create other service contexts outside of these calls.

  Any statement handle obtained using OCIStmtPrepare2() with the service context should be subsequently used only in conjunction with the same service context, and never with a different service context.

Indicates two ways in which to log off from the database in session pooling mode depending on the logon call.

From the following calls, choose the one that corresponds to the logon call and use it to log off from the database in session pooling mode.

• OCILogoff()

  If you used OCILogon2() to make the connection, you must call OCILogoff() to log off.

• OCISessionRelease()

  If you used OCISessionGet() to make the connection, then you must call OCISessionRelease() to log off. Pending transactions are automatically committed.

Call OCISessionPoolDestroy() to destroy the session pool.

This is shown in the following example:

OCISessionPoolDestroy(poolhp, errhp, OCI_DEFAULT);

Call OCIHandleFree() to free the session pool handle.

This is shown in the following example:

OCIHandleFree((void *)poolhp, OCI_HTYPE_SPOOL);

Connection pooling on the middletier is similar to what a shared server offers on the back end.

Connection pooling makes a dedicated server instance behave like a shared server instance by managing the session multiplexing logic on the middle tier.

The connection pool on the middle tier controls the pooling of dedicated server processes including incoming connections into the dedicated server processes. The main difference between connection pooling and a shared server is that in the latter case, the connection from the client is normally to a dispatcher in the database instance. The dispatcher is responsible for directing the client request to an appropriate shared server. However, the physical connection from the connection pool is established directly from the middletier to the dedicated server process in the back-end server pool.

Connection pooling is beneficial only if the middle tier is multithreaded. Each thread can maintain a session to the database. The actual connections to the database are maintained by the connection pool, and these connections (including the pool of dedicated database server processes) are shared among all the threads in the middle tier.

Stateless sessions are serially reusable across mid-tier threads.

After a thread is done processing a database request on behalf of a three-tier user, the same database session can be reused for a completely different request on behalf of a completely different three-tier user.

Stateful sessions to the database, however, are not serially reusable across mid-tier threads because they may have some particular state associated with a particular three-tier user. Examples of such state may include open transactions, the fetch state from a statement, or a PL/SQL package state. So long as the state exists, the session is not reusable for a different request.

What connection pooling offers is stateless connections and stateful sessions.

You can use the advanced concept of multiple connection pools for different database connections.

Multiple connection pools can also be used when different priorities are assigned to users. Different service-level guarantees can be implemented using connection pooling.

Figure 10-1 illustrates OCI connection pooling.

Figure 10-1 OCI Connection Pooling

Description of "Figure 10-1 OCI Connection Pooling"

Transaction application failover (TAF) is enabled for connection pooling.

The concepts of TAF apply equally well with connections in the connection pool except that the BACKUP and PRECONNECT clauses should not be used in the connect string and do not work with connection pooling and TAF.

When a connection in the connection pool fails over, it uses the primary connect string itself to connect. Sessions fail over when they use the pool for a database round-trip after their instance failure. The listener is configured to route the connection to a good instance if available, as is typical with service-based connect strings.

Connection pooling requires that the pool handle OCI_HTYPE_CPOOL be allocated by OCIHandleAlloc().

OCICPool *poolhp;
OCIHandleAlloc((void *) envhp, (void **) &poolhp, OCI_HTYPE_CPOOL, 
                      (size_t) 0, (void **) 0));

The function OCIConnectionPoolCreate() initializes the connection pool handle.

OCIConnectionPoolCreate((OCIEnv *)envhp,
                   (OCIError *)errhp, (OCICPool *)poolhp,
                   &poolName, &poolNameLen,
                   (text *)database,strlen(database),
                   (ub4) connMin, (ub4) connMax, (ub4) connIncr,
                   (text *)poolUsername,strlen(poolUserLen),
                   (text *)poolPassword,strlen(poolPassLen),
                   OCI_DEFAULT));

The application can use one of several interfaces.

With OCI_CPOOL mode (connection pooling), the session memory (UGA) in the back-end database comes out of the SGA.

This may require some SGA tuning on the back-end database to have a larger SGA if your application consumes more session memory than the SGA can accommodate. The memory tuning requirements for the back-end database are similar to configuring the LARGE POOL in a shared server back end except that the instance is still in dedicated mode.

If you are still running into the SGA limitation, you must consider:

• Reducing the session memory consumption by having fewer open statements for each session

• Reducing the number of sessions in the back end by pooling sessions on the mid-tier

• Or otherwise, turning off connection pooling

The application must avoid using dedicated database links on the back end with connection pooling.

If the back end is a dedicated server, effective connection pooling is not possible because sessions using dedicated database links are tied to a physical connection rendering that same connection unusable by other sessions. If your application uses dedicated database links and you do not see effective sharing of back-end processes among your sessions, you must consider using shared database links.

Choose the appropriate call to log off from the database in connection pooling mode.

From the following calls, choose the one that corresponds to the logon call and use it to log off from the database in connection pooling mode.

• OCILogoff():

  If OCILogon2() was used to make the connection, OCILogoff() must be used to log off.

• OCISessionRelease()

  If OCISessionGet() was called to make the connection, then OCISessionRelease() must be called to log off.

• OCISessionEnd() and OCIServerDetach()

  If OCIServerAttach() and OCISessionBegin() were called to make the connection and start the session, then OCISessionEnd() must be called to end the session and OCIServerDetach() must be called to release the connection.

OCIConnectionPoolDestroy() destroys it.

Use OCIConnectionPoolDestroy() to destroy the connection pool.

The pool handle is freed using OCIHandleFree().

These last three actions are illustrated in this code fragment:

 for (i = 0; i < MAXTHREADS; ++i)
  {
    checkerr(errhp, OCILogoff((void *) svchp[i], errhp));
  }
  checkerr(errhp, OCIConnectionPoolDestroy(poolhp, errhp, OCI_DEFAULT));
  checkerr(errhp, OCIHandleFree((void *)poolhp, OCI_HTYPE_CPOOL));