DRCP database resident connection pool 11G

Database Resident Connection Pooling (DRCP) is pool of connections. Establishing connection is very expensive process so DRCP solves it by sharing such connections between users. It’s important for “stateless” applications which are establishing connections very often (Web based).

DRCP is available to all database clients that use the OCI driver including C, C++, and PHP.

Dedicated Server Shared Server Database Resident Connection Pooling
When a client request is received, a new server process and a session are created for the client. When the first request is received from a client, the Dispatcher process places this request on a common queue. The request is picked up by an available shared server process. The Dispatcher process then manages the communication between the client and the shared server process. When the first request is received from a client, the Connection Broker picks an available pooled server and hands off the client connection to the pooled server.If no pooled servers are available, the Connection Broker creates one. If the pool has reached its maximum size, the client request is placed on the wait queue until a pooled server is available.
Releasing database resources involves terminating the session and server process. Releasing database resources involves terminating the session. Releasing database resources involves releasing the pooled server to the pool.
Memory requirement is proportional to the number of server processes and sessions. There is one server and one session for each client. Memory requirement is proportional to the sum of the shared servers and sessions. There is one session for each client. Memory requirement is proportional to the number of pooled servers and their sessions. There is one session for each pooled server.
Session memory is allocated from the PGA. Session memory is allocated from the SGA. Session memory is allocated from the PGA.

Memory usage for dedicated server, shared server, and DRCP

Application memory requirements and settings (dedicated, shared, drcp):

  • 400k per session
  • 4MB per server process
  • pool size is 100
  • shared servers 100

For 5000 client connections memory usage would be following:

  • dedicated server = 5000 X (400 KB + 4 MB) = 22 GB
  • shared server = 5000 X 400 KB + 100 X 4 MB = 2.5 GB. Out of the 2.5 GB, 2 GB is allocated from the SGA.
  • DRCP = 100 X (400 KB + 4 MB) + (5000 X 35KB)= 615 MB

DRCP session limitations (can’t do)

  • shutdown database
  • stop DRCP
  • change password for connected user
  • Use shared database links to connect to a database resident connection pool
  • Use Advanced Security Option (ASO) options such as encryption, certificates, and so on
  • Use migratable sessions on the server side directly by using the OCI_MIGRATE option or indirectly through OCIConnectionPool

Package DBMS_CONNECTION_POOL

DBMS_CONNECTION_POOL is package which is managing DRCP. The package contains following procedures:

START_POOL - starts connection pool
STOP_POOL - stops connection pool
CONFIGURE_POOL - configure pool
ALTER_PARAM - change configuration parameter for connection pool
RESTORE_DEFAULTS - restore default settings for connection pool

Following attributes can be modified for a pool:

pool_name - pool name. Default is SYS_DEFAULT_CONNECTION_POOL.

minsize - minimum number of pooled sessions in the pool. Default is 4.

maxsize - maximum number of pooled session in the pool. Default is 40.

incrsize - The number of pooled servers by which the pool 
  is incremented if servers are unavailable when a client 
  application request is received. The default value is 3.

session_cached_cursors - The number of session cursors to cache 
in each pooled server session. The default value is 20.

inactivity_timeout - The maximum time, in seconds, the pooled server 
can stay idle in the pool. After this time, the server is terminated. 
The default value is 300.
This parameter does not apply if the pool is at MINSIZE.

max_think_time - The maximum time of inactivity, in seconds, 
for a client after it obtains a pooled server from the pool. 
After obtaining a pooled server from the pool, if the client 
application does not issue a database call for the time 
specified by MAX_THINK_TIME, the pooled server is freed and the 
client connection is terminated. The default value is 30.

max_use_session - The number of times a pooled server can be taken 
and released to the pool. The default value is 5000.

max_lifetime_session - The time, in seconds, to live for a pooled 
server in the pool. The default value is 3600.

Currently only one pool is defined in database SYS_DEFAULT_CONNECTION_POOL. There is no possibility to create pool with other name.

Start pool

SQL> exec dbms_connection_pool.start_pool;

Stop pool

SQL> exec dbms_connection_pool.stop_pool;

Changing pool parameters

ALTER_PARAM – enables to change one parameter for a pool

CONFIGURE_POOL – enables to configure many parameters in one call

RESTORE_DEFAULTS – restores default settings for a pool

begin
  dbms_connection_pool.alter_param
  (param_name  => 'INACTIVITY_TIMEOUT',
   param_value => 5);
end;
/

begin
  dbms_connection_pool.configure_pool
  (minsize=>5,
   maxsize=>10,
   incrsize=>2,
   max_use_session=>20
  );
end;
/

begin
  dbms_connection_pool.restore_defaults;
end;
/

Establishing connection

If pool is started, you can use easy connection method

connect user/password@host.domain:port/service:POOLED

or define connection in tnsnames.ora file by specifying SERVER=POOLED

connection_entry =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = host.domain)(PORT = port))
    (CONNECT_DATA =
      (SERVICE_NAME = service)
      (SERVER = POOLED)
    )
  )

So using connection_entry from tnsnames.ora you can establish connection

connect user/password@connection_entry

Using DRCP – quick sample

sql>connect / as sysdba
Connected.

sql>exec dbms_connection_pool.start_pool;
anonymous block completed

sql>select connection_pool, status from dba_cpool_info;
CONNECTION_POOL                  STATUS         
-------------------------------- ----------------
SYS_DEFAULT_CONNECTION_POOL      ACTIVE

sql>begin
  dbms_connection_pool.alter_param
  (param_name  => 'INACTIVITY_TIMEOUT',
   param_value => 1);
end;
/

sql>connect hr/hr@oel6:1521/ORA11G:POOLED
Connected.

For above example if you are not active for more than 1 minute you should get following error

SQL> select 1 from dual;
select sydate from dual
                      *
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 7688
Session ID: 29 Serial number: 536

Views

DBA_CPOOL_INFO  – Contains information about the connection pool such as the pool status, the maximum and minimum number of connections, and timeout for idle sessions.

V$CPOOL_STATS – Contains pool statistics such as the number of session requests, number of times a session that matches the request was found in the pool, and total wait time for a session request.

V$CPOOL_CC_STATS – Contains connection class level statistics for the pool.

Have a fun 🙂

Tomasz

Leave a Reply

Your email address will not be published. Required fields are marked *

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