8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
Database Resident Connection Pool (DRCP) in Oracle Database 11g Release 1
The database resident connection pool (DRCP) reduces the resource requirements of applications that currently don't support connection pooling, either because it is not supported by the application infrastructure, or it has not been implemented. DRCP is only supported for database connections using the OCI driver.
The pool is managed using the
DBMS_CONNECTION_POOL package. Although the package appears to support multiple connection pools, the document states that it currently only supports the default pool name (SYS_DEFAULT_CONNECTION_POOL).
The DRCP is started and stopped using the
STOP_POOL procedures respectively.
SQL> EXEC DBMS_CONNECTION_POOL.start_pool; PL/SQL procedure successfully completed. SQL>
ALTER_PARAM procedure allows us to configure an individual pool parameter, while the
CONFIGURE_POOL procedure allows us to configure all pool parameters in one call. The default settings are restored using the
RESTORE_DEFAULTS procedure. The pool parameters that are currently supported are listed below.
PROCEDURE CONFIGURE_POOL Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- POOL_NAME VARCHAR2 IN DEFAULT MINSIZE BINARY_INTEGER IN DEFAULT MAXSIZE BINARY_INTEGER IN DEFAULT INCRSIZE BINARY_INTEGER IN DEFAULT SESSION_CACHED_CURSORS BINARY_INTEGER IN DEFAULT INACTIVITY_TIMEOUT BINARY_INTEGER IN DEFAULT MAX_THINK_TIME BINARY_INTEGER IN DEFAULT MAX_USE_SESSION BINARY_INTEGER IN DEFAULT MAX_LIFETIME_SESSION BINARY_INTEGER IN DEFAULT
With the pool started, a connection can be made using the easy connect method.
SQL> CONNECT firstname.lastname@example.org:1521/DB11G.WORLD:POOLED Connected. SQL>
Set the server type to
POOLED in the tnsnames.ora file to allow connection pooling for a connection string.
DB11G_POOL = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = ole45.localdomain)(PORT = 1521)) (CONNECT_DATA = (SERVICE_NAME = DB11G.WORLD) (SERVER = POOLED) ) )
Pooled connections are then possible using the amended connection string.
SQL> CONNECT test/test@DB11G_POOL Connected. SQL>
If a session remains idle for longer than the
inactivity_timeout it is killed to free up space in the connection pool.
SQL> SELECT sydate FROM dual; select sydate from dual * ERROR at line 1: ORA-03113: end-of-file on communication channel Process ID: 7902 Session ID: 170 Serial number: 20 SQL>
Attempting to connect to the pool when the pool is stopped results in the following error.
SQL> conn / as sysdba Connected. SQL> EXEC DBMS_CONNECTION_POOL.stop_pool; PL/SQL procedure successfully completed. SQL> CONNECT test/test@DB11G_POOL ERROR: ORA-12520: TNS:listener could not find available handler for requested type of server Warning: You are no longer connected to ORACLE. SQL>
Information about connection pooling is displayed using the following views:
DRCP and Java
The JDBC OCI driver does not currently support OCI session pool, so although configuring a pooled connection will not fail, it will result in dedicated connections on the server side, as described in MOS Note [ID 1087381.1].
Until the JDBC OCI driver is updated to support OCI session pool, there will be no benefit in using DRCP with Java, so session pooling must be implemented in the application server layer.
Oracle Database 12c allows Java to use the Database Resident Connection Pool (DRCP), as described here.
For more information see:
- Configuring Database Resident Connection Pooling
- Enabling DRCP on the Client Side : 12c - Java
Hope this helps. Regards Tim...