OCIStmtExecute and other calls returns ORA-24401 when the connection pool is set very low

(Doc ID 1589617.1)

Last updated on OCTOBER 04, 2013

Applies to:

Oracle Database - Enterprise Edition - Version 10.2.0.5 to 11.2.0.2.0 [Release 10.2 to 11.2]
Information in this document applies to any platform.
This document is intended to workaround this problem.

Symptoms

Multithreaded OCI application uses minimal connection pool to keep number of connections to manageable levels. OCISessionGet from the pool succeeds, but OCIStmtExecute, which uses the handle acquired by OCISessionGet, fails with the error '24401: cannot open further connections':

The number of connections is deliberatily kept at low level so that the resource utilization is to the minimal but at the same time number of users requesting the connection are more.

In such situations, the application might occasionally throw the ORA-24401 error which is quite expected but the main problem is the point (OCI call) at which the error is thrown is not constant. Due to which the error could not be handled efficently.

$ ./ConnectionPoolTest localhost $ORACLE_SID colddba colddba 1 2 1 8
Creating 8 threads
203: successfully acquired connection
203: Fetched something from DUAL in 203
405: successfully acquired connection
405: Fetched something from DUAL in 405
304: successfully acquired connection
304: worker(void *)(ConnectionPoolTest.C, 128): 'Error - ORA-24401: cannot open further connections' while executing 'OCIStmtExecute(hContext, stmt, hError, 1, 0, (CONST OCISnapshot *) NULL, (OCISnapshot *) NULL, OCI_DEFAULT)'
$

Environment:
This is reproducible on IBM AIX/Oracle 10.2.0.5 and SuSE Linux Enterprise Server/Oracle Instant Client 11.2.0.3.

Changes

  OCI_ATTR_CONN_NOWAIT is set in this case which causes the application threads to not to wait on the connection request and error out immeidately if there is no connection readily available in the pool.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms