Functions

Oracle Pools


Detailed Description

OCILIB support the connections and sessions pooling featurse introduced in Oracle 9i.

Let's Oracle talk about this features !

Connection pools (from Oracle Oracle Call Interface Programmer's Guide)

Connection pooling is the use of a group (the pool) of reusable physical connections by several sessions, in order to balance loads. The management of the pool is done by OCI, not the application. Applications that can use connection pooling include middle-tier applications for Web application servers and e-mail servers.

Session Pools (from Oracle OraclŪ Call Interface Programmer's Guide)

Session pooling means that the application will create and maintain a group of stateless sessions to the database. These sessions will be handed over to thin clients as requested. If no sessions are available, a new one may be created. When the client is done with the session, the client will release it to the pool. Thus, the number of sessions in the pool can increase dynamically.

Note:
OCILIB implements homogeneous session pools only.
When using Pools (from Oracle Oracle Call Interface Programmer's Guide)

If database sessions are not reusable by mid-tier threads (that is, they are stateful) and the number of back-end server processes may cause scaling problems on the database, use OCI connection pooling.

If database sessions are reusable by mid-tier threads (that is, they are stateless) and the number of back-end server processes may cause scaling problems on the database, use OCI session pooling.

If database sessions are not reusable by mid-tier threads (that is, they are stateful) and the number of back-end server processes will never be large enough to potentially cause any scaling issue on the database, there is no need to use any pooling mechanism.

Oracle 8i support

Pooling has bee introduced in :

Example
#include "ocilib.h"

#define MAX_THREADS 50
#define MAX_CONN    10
#define SIZE_STR   260

void worker(OCI_Thread *thread, void *data)
{
    OCI_Connection *cn = OCI_PoolGetConnection(data, NULL);
    char str[SIZE_STR+1];

    /* application work here */

    str[0] = 0;

    OCI_Immediate(cn, "select to_char(sysdate, 'YYYYMMDD HH24:MI:SS') from dual", OCI_ARG_TEXT, str);
    
    printf("%s\n", str);
    
    /* ... */

    OCI_ConnectionFree(cn);
}

int main(void)
{
    OCI_Thread *th[MAX_THREADS];
    OCI_ConnPool *pool;

    int i;

    if (!OCI_Initialize(NULL, NULL, OCI_ENV_DEFAULT | OCI_ENV_THREADED))
        return EXIT_FAILURE;

    /* create pool */

    pool = OCI_PoolCreate("db", "usr", "pwd", OCI_POOL_CONNECTION, OCI_SESSION_DEFAULT, 0, MAX_CONN, 1);

    /* create threads */

    for (i = 0; i < MAX_THREADS; i++)
    {
        th[i] = OCI_ThreadCreate();
        OCI_ThreadRun(th[i], worker, pool);
    }
  
    /* wait for threads and cleanup */

    for (i = 0; i < MAX_THREADS; i++)
    {
       OCI_ThreadJoin(th[i]);
       OCI_ThreadFree(th[i]);
    }

    OCI_PoolFree(pool);

    OCI_Cleanup();

 
    return EXIT_SUCCESS;
}

Functions

OCI_EXPORT OCI_Pool *OCI_API OCI_PoolCreate (const mtext *db, const mtext *user, const mtext *pwd, unsigned int type, unsigned int mode, unsigned int min_con, unsigned int max_con, unsigned int incr_con)
 Create an Oracle pool of connections or sessions.
OCI_EXPORT boolean OCI_API OCI_PoolFree (OCI_Pool *pool)
 Destroy a pool object.
OCI_EXPORT OCI_Connection *OCI_API OCI_PoolGetConnection (OCI_Pool *pool, mtext *tag)
 Get a connection from the pool.
OCI_EXPORT unsigned int OCI_API OCI_PoolGetTimeout (OCI_Pool *pool)
 Get the idle timeout for connections/sessions in the pool.
OCI_EXPORT boolean OCI_API OCI_PoolSetTimeout (OCI_Pool *pool, unsigned int value)
 Set the connections/sessions idle timeout.
OCI_EXPORT boolean OCI_API OCI_PoolGetNoWait (OCI_Pool *pool)
 Get the waiting mode used when no more connections/sessions are available from the pool.
OCI_EXPORT boolean OCI_API OCI_PoolSetNoWait (OCI_Pool *pool, boolean value)
 Set the waiting mode used when no more connections/sessions are available from the pool.
OCI_EXPORT unsigned int OCI_API OCI_PoolGetBusyCount (OCI_Pool *pool)
 Return the current number of busy connections/sessions.
OCI_EXPORT unsigned int OCI_API OCI_PoolGetOpenedCount (OCI_Pool *pool)
 Return the current number of opened connections/sessions.
OCI_EXPORT unsigned int OCI_API OCI_PoolGetMin (OCI_Pool *pool)
 Return the minimum number of connections/sessions that can be opened to the database.
OCI_EXPORT unsigned int OCI_API OCI_PoolGetMax (OCI_Pool *pool)
 Return the maximum number of connections/sessions that can be opened to the database.
OCI_EXPORT unsigned int OCI_API OCI_PoolGetIncrement (OCI_Pool *pool)
 Return the increment for connections/sessions to be opened to the database when the pool is not full.

Function Documentation

OCI_EXPORT OCI_Pool* OCI_API OCI_PoolCreate ( const mtext *  db,
const mtext *  user,
const mtext *  pwd,
unsigned int  type,
unsigned int  mode,
unsigned int  min_con,
unsigned int  max_con,
unsigned int  incr_con 
)

Create an Oracle pool of connections or sessions.

Parameters:
db- Oracle Service Name
user- Oracle User name
pwd- Oracle User password
type- Type of pool
mode- Session mode
min_con- minimum number of connections/sessions that can be opened.
max_con- maximum number of connections/sessions that can be opened.
incr_con- next increment for connections/sessions to be opened

Possible values for parameter 'type':

  • OCI_POOL_CONNECTION
  • OCI_POOL_SESSION

Possible values for parameter 'mode':

  • OCI_SESSION_DEFAULT
  • OCI_SESSION_SYSDAB
  • OCI_SESSION_SYSOPER
Note:
External credentials are supported by supplying a null value for the 'user' and 'pwd' parameters If the param 'db' is NULL then a connection to the default local DB is done
Returns:
Connection or session pool handle on success or NULL on failure

Definition at line 142 of file pool.c.

References OCI_PoolFree().

OCI_EXPORT boolean OCI_API OCI_PoolFree ( OCI_Pool pool )

Destroy a pool object.

Parameters:
pool- Pool handle
Returns:
TRUE on success otherwise FALSE

Definition at line 391 of file pool.c.

Referenced by OCI_PoolCreate().

OCI_EXPORT OCI_Connection* OCI_API OCI_PoolGetConnection ( OCI_Pool pool,
mtext *  tag 
)

Get a connection from the pool.

Parameters:
pool- Pool handle
tag- user tag string
Session tagging

Session pools have a nice feature that is 'session tagging' It's possible to tag a session with a string identifier when the session is returned to the pool, it keeps its tags. When requesting a connection from the session pool, it's possible to request a session that has the given 'tag' parameter If one exists, it is returned. If not and if an untagged session is available, it is then returned. So check the connection tag property with OCI_GetSessionTag() to find out if the returned connection is tagged or not.

This features is described in the OCI developper guide as the following :

"The tags provide a way for users to customize sessions in the pool. A client may get a default or untagged session from a pool, set certain attributes on the session (such as NLS settings), and return the session to the pool, labeling it with an appropriate tag. The user may request a session with the same tags in order to have a session with the same attributes"

Returns:
Connection handle otherwise NULL on failure

Definition at line 415 of file pool.c.

References OCI_ConnectionFree(), OCI_MutexAcquire(), and OCI_MutexRelease().

OCI_EXPORT unsigned int OCI_API OCI_PoolGetTimeout ( OCI_Pool pool )

Get the idle timeout for connections/sessions in the pool.

Parameters:
pool- Pool handle
Note:
Connections/sessions idle for more than this time value (in seconds) is terminated
Timeout is not available for internal pooling implementation (client < 9i)
Returns:

Definition at line 508 of file pool.c.

OCI_EXPORT boolean OCI_API OCI_PoolSetTimeout ( OCI_Pool pool,
unsigned int  value 
)

Set the connections/sessions idle timeout.

Parameters:
pool- Pool handle
value- Timeout value
Note:
connections/sessions idle for more than this time value (in seconds) is terminated
This call has no effect if pooling is internally implemented (client < 9i)
Returns:

Definition at line 524 of file pool.c.

OCI_EXPORT boolean OCI_API OCI_PoolGetNoWait ( OCI_Pool pool )

Get the waiting mode used when no more connections/sessions are available from the pool.

Parameters:
pool- Pool handle
Returns:
  • FALSE to wait for an available object if the pool is saturated
  • TRUE to not wait for an available object

Definition at line 574 of file pool.c.

OCI_EXPORT boolean OCI_API OCI_PoolSetNoWait ( OCI_Pool pool,
boolean  value 
)

Set the waiting mode used when no more connections/sessions are available from the pool.

Parameters:
pool- Pool handle
value- wait for object
Note:
Pass :
  • FALSE to wait for an available object if the pool is saturated
  • TRUE to not wait for an available object
Returns:

Definition at line 590 of file pool.c.

OCI_EXPORT unsigned int OCI_API OCI_PoolGetBusyCount ( OCI_Pool pool )

Return the current number of busy connections/sessions.

Parameters:
pool- Pool handle

Definition at line 648 of file pool.c.

OCI_EXPORT unsigned int OCI_API OCI_PoolGetOpenedCount ( OCI_Pool pool )

Return the current number of opened connections/sessions.

Parameters:
pool- Pool handle

Definition at line 698 of file pool.c.

OCI_EXPORT unsigned int OCI_API OCI_PoolGetMin ( OCI_Pool pool )

Return the minimum number of connections/sessions that can be opened to the database.

Parameters:
pool- Pool handle

Definition at line 748 of file pool.c.

OCI_EXPORT unsigned int OCI_API OCI_PoolGetMax ( OCI_Pool pool )

Return the maximum number of connections/sessions that can be opened to the database.

Parameters:
pool- Pool handle

Definition at line 764 of file pool.c.

OCI_EXPORT unsigned int OCI_API OCI_PoolGetIncrement ( OCI_Pool pool )

Return the increment for connections/sessions to be opened to the database when the pool is not full.

Parameters:
pool- Pool handle

Definition at line 780 of file pool.c.