Database Resident Connection Pooling (DRCP) in Oracle

Database Resident Connection Pooling (DRCP) in Oracle

DRCP is introduced in 11g version of Oracle. It is used for sharing connection to achieve scalability in multi process and multi threaded environment.
DRCP Pools are like dedicated it’s used to shared the connection between multiple application process from different hosts.

Note: Database Resident Connection Pooling is consuming less memory as compared to other dedicated or Shared connection.

POOLED SERVER
Database server processes and sessions combination is known as a pooled server.

CONNECTION BROKER
A connection broker manages the pooled server in database instance. Client are connected and authenticated to the broker. The background process called is Connection Broker process(CMON).

Steps follow by Client for Connection
1. Client request for connection
2. Broker authenticated and pick the pooled server and hand-off client to that pooled server.
3. The client directly communicates with the pooled server for all its database activity.
4. The pooled server is handed back to the broker when the client releases it.

Configure the DRCP

1. Start or Enabled the Pool by connecting with sysdba
Following command start the broker and register with database listener. It must be started before client started request.

execute dbms_connection_pool.start_pool;

2. Check DRCP is started or configured:

SQL> SELECT connection_pool, status, maxsize FROM dba_cpool_info;

CONNECTION_POOL                STATUS     MAXSIZE
------------------------------ ---------- ----------
SYS_DEFAULT_CONNECTION_POOL    INACTIVE   20

SQL> exec dbms_connection_pool.start_pool;
PL/SQL procedure successfully completed.

SQL> SELECT connection_pool, status, maxsize FROM dba_cpool_info;

CONNECTION_POOL                STATUS   MAXSIZE
------------------------------ -------- -------
SYS_DEFAULT_CONNECTION_POOL    ACTIVE   20

3. Disable or Stop the DRCP

execute dbms_connection_pool.stop_pool();

4. For using the Pool you need to make some connection changes at tns entry level or application level
Add POOLED keyword in tns entry and application connection string

host1.oracle.com:1521/orcl:POOLED
OR
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp) (HOST=host1.oracle.com)
(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=orcl)
(SERVER=POOLED)))

5.Configure and ALTER DRCP with package DBMS_CONNECTION_POOL.
This is step needed if you want to change the default setting before configuring the DRCP pool.
Following example already set with default value:

execute dbms_connection_pool.configure_pool(
pool_name => 'SYS_DEFAULT_CONNECTION_POOL',
minsize => 4,
maxsize => 40,
incrsize => 2,
session_cached_cursors => 20,
inactivity_timeout => 300,
max_think_time => 600,
max_use_session => 500000,
max_lifetime_session => 86400);

execute dbms_connection_pool.alter_param(
pool_name => 'DRPC_POOL',
param_name => 'MAX_THINK_TIME',
param_value => '1200');


Parameter meaning as:
POOL_NAME: Name of the pool. Default is SYS_DEFAULT_CONNECTION_POOL
minsize: minimum number of pooled server in pool. (default 4)
maxsize: maximum number of pooled server in pool. (default 40)
incrsize: increased number of pooled server is not available if pool is not max limit( default 2)
session_cached_cursors: SESSION_CACHED_CURSORS for all connections in the pool(default 20)
inactivity_timeout: time to remain an idle server in the pool. If a server remains idle upto time limit, it is killed. (default 300 seconds)
max_think_time: Maximum time of inactivity the PHP script is allowed after connecting. (default 120 seconds)
max_use_session: Maximum number of times a server can be taken and released to the pool before it is flagged for restarting. (default 500000)
max_lifetime_session: Time to live for a pooled server before it is restarted. (default 86400 seconds)
num_cbrok: The number of connection brokers that are created to handle connection (default 1)
maxconn_cbrok: The maximum number of connections that each connection broker can handle.(default 40000)

6. Monitor the DRCP pooling from following views:

select connection_pool, status, maxsize from dba_cpool_info;

select num_requests, num_hits, num_misses, num_waits from v$cpool_stats;

select cclass_name, num_requests, num_hits, num_misses from v$cpool_cc_stats;

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.