I was reviewing some features in Oracle and, basically, every single time I review them I find something new. Seems Oracle Databases’ features are near to infinite and we frequently find some that can really add value to our solutions.
So I decided to make a serie of posts with really quick notes about each one of them.
You can see all posts in this serie in my page of posts and some others more.
Ready? Here it goes:
Database Resident Connection Pool
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. he DRCP is started and stopped using the START_POOL and STOP_POOL procedures respectively.
SQL> EXEC DBMS_CONNECTION_POOL.start_pool; PL/SQL procedure successfully completed. SQL> EXEC DBMS_CONNECTION_POOL.stop_pool; PL/SQL procedure successfully completed.
The ALTER_PARAM procedure allows you to configure an individual pool parameter, while the CONFIGURE_POOL allows you 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, set the server type to POOLED in the tnsnames.ora file to allow connection pooling for a connection string.
EXAMPLE_POOL = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = mydb.localdomain)(PORT = 1521)) (CONNECT_DATA = (SERVICE_NAME = MYDB) (SERVER = POOLED) ) )
It’s also possible using easy connect method:
SQL> CONNECT firstname.lastname@example.org:1521/MYDB:POOLED Connected.
In 11g DRCP does not support JDBC connections. With makes sense, once pool is configured on application side.
Since Database 12c, DRCP is possible using JDBC. Don’t ask me why. 🙂
And that’s it!