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 atMINSIZE
. 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 byMAX_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