Database Resident Connection Pooling (DRCP)
In versions of Oracle prior to 11g, there were two different kinds of client connections that could be made. First was the dedicated client connection and the second was a distributed connection method known as Shared Servers (or MTS in older Oracle versions). Dedicated client connections had the benefit of not requiring any configuration and generally being bug free. The dedicated server architecture started to experience problems when you ran into high concurrency and limited resources. For example, if you had 5000 connections using 200k of memory for the session plus another 5mb of memory for each server process you ended up with a 260GB memory requirement for the processes of that database.
Shared Servers have the benefit of pooling a small number of connections that could support a large number of users. Shared servers can reduce the overall memory and CPU requirements of a given database server significantly. The same memory requirements for 5000 connections of 200k per connection and 5mb of memory and 1000 shared server connections would be around 12GB, much less than the dedicated server requirements. One problem with a shared server configuration was that it required some configuration. Another problem has been a history of a number of bugs related to the shared server architecture. While these bugs are largely gone, there is still a reputation of sorts that the Shared Server architecture must live down.
There is another problem with both dedicated connections and shared server configurations and this has to do with web based architectures, particularly multi-process, single-threaded application servers, such as PHP and Apache servers, that cannot do middle-tier connection pooling.
Most DBA's know that web based systems are "stateless". As a result connections are short lived with the database connection being established, data extracted, page rendered and then the connection is ended. As pages are rendered, this process occurs over and over again which is hugely inefficient as the process of creating a connection incurs significant overhead. As a result, connection pooling (or a method of keeping connections opened and ready for use by any client process at any time, irrespective of previous connections) is critical.
There are a number of client-side and middle-tier solutions to meet the need for connection pooling. These solutions come with a few problems including:
- Each connection pool is confined to a single middle-tier node. Thus if multiple nodes are sharing a common resource, each node will need it's own middle-tier connection pool. As a result it is likely thatresources will be wasted across the mid-tier.
- Often with mid-tier connection pooling you find that the database workload is distributed unevenly across the connection pools.
- Because these connection pools pre-allocate database connections, there can frequently be an unnecessary number of server processes connected to the database in an idle state. This results in excessive database server memory consumption and other possible resource constraints.
As a solution to these issues, Oracle 11g introduced a server-side connection pool called Database Resident Connection Pool (DRCP). DRCP is available to all database clients that use the OCI driver (including C, C++, and PHP).
Using DRCP
DRCP requires little configuration. It is preinstalled in any Oracle 11g database, and merely needs to be started by calling the DBMS_CONNECTION_POOL.START_POOL procedure as seen in this example:
exec dbms_connection_pool.start_pool;
You can determine if DRCP is enabled by querying the DBA_CPOOL_INFO view as seen in this example. First the connection pool is INACTIVE. You can start connection pooling and then requery the view to find that connection pooling is now active:
SQL> SELECT connection_pool, status, maxsize FROM dba_cpool_info; CONNECTION_POOL STATUS MAXSIZE ------------------------------ ---------------- ---------- SYS_DEFAULT_CONNECTION_POOL INACTIVE 40 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 40
Note that SYS_DEFAULT_CONNECTION_POOL is the default name for the connection pool. You can stop the pool using the DBMS_CONNECTION_POOL.STOP_POOL procedure as seen here:
SQL> exec dbms_connection_pool.start_pool;
In order for clients to connect to the pooled connections, the naming resolution method will need to have the database connection information modified. For example, if you are using a tnsnames.ora file to resolve database service names, you would add a line that says (SERVER = POOLED) to the CONNECT_DATA section. Here is an example of a TNS entry where this modification has been made. First, here is the old entry:
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ROBERTSDELLXPS)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
Now, we will change the (SERVER = DEDICATED) entry to read (SERVER = POOLED) and save the file. Here is our changed example:
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ROBERTSDELLXPS)(PORT = 1521))
(CONNECT_DATA =
(SERVER = POOLED)
(SERVICE_NAME = orcl)
)
)
Now, the connection to the database will use DRCP. You would simply connect as you always have:
Sqlplus scott/tiger@orcl
The easy connection naming method is also supported as seen in the following example. Note the use of the pooled keyword at the end of the connection string. This indicates that the connection should be made to a DRCP connection pool:
Sqlplus scott/robert@robertsdellxps:1521/orcl:pooled
Note: See Metalink note 316165.1 if you receive an ORA-28547 error when trying to use the Easy Connection naming method.
The DBMS_CONNECTION_POOL Procedure
As noted in earlier examples, the DBMS_CONNECTION_POOL PL/SQL supplied package provides a means of managing DRCP. This package contains the following procedures:
| Procedure | Description |
|---|---|
| ALTER_PARAM | Modify a configuration parameter for a connection pool. |
| CONFIGURE_POOL | Configure the connection pool |
| START_POOL | Start the connection pool |
| STOP_POOL | Stop the connection pool |
| RESTORE_DEFAULTS | Restore the connection pool to default settings |
The DBMS_CONNECTION_POOL.ALTER_PARAM procedure provides the ability to modify pool specific parameters for a given connection pool. There are a number of attributes that can be modified as seen in the following table:
| Attribute Name | Description |
|---|---|
| Pool_name | This is the pool name. Currently only the default name of the pool is supported. |
| Minsize | This is the minimum number of pooled session that should be kept alive in the pool. |
| Maxsize | This is the maximum number of pooled session that should be kept alive in the pool at any time. |
| Incrsize | When there is not a pooled server available and new ones need to be created, this is how many will be created at that time. |
| Session_cached_cursors | Enables session_cached_cursors for the pooled connections. |
| Inactivity_timeout | Time that needs to pass (in seconds) before the session is killed. |
| Max_think_time | Once a client gets a server from the pool, this is the maximum time of inactivity that can pass before control of the session will be lost. |
| Max_use_session | This is the maximum number of times a given session can be taken from and then released to the pool. Helpful if there are applications that will benefit from having sessions closed and reopened once in a while. |
| Max_lifetime_session | This is the maximum time to live for a pooled session. Helpful if there are applications that will benefit from having sessions closed and reopened once in a while. |
The DBMS_CONNECTION_POOL.CONFIGURE_POOL procedure is used to configure all parameters associated with the connection pool. The parameters of the procedure are the same as the modifiable attributes of DBMS_CONNECTION_POOL.ALTER_PARAM.
You can use DBMS_CONNECTION_POOL.RESTORE_DEFAULTS to restore the connection pool settings to their default values.
DRCP Related Data Dictionary Views
Oracle 11g has added new data dictionary views to support DRCP. These include:
DBA_CPOOL_INFO
Provides information about the connection pool. This includes the connection pool status, and settings of various attributes associated with the BRCP pool (minsize, maxsize, incrsize, session_cached_cursors, etc).
SQL> SELECT connection_pool, status, maxsize FROM dba_cpool_info; CONNECTION_POOL STATUS MAXSIZE ------------------------------ ---------------- ---------- SYS_DEFAULT_CONNECTION_POOL INACTIVE 40
V$CPOOL_STATS
Provides statistics for connection pooling including the number of session requests, number of times a session that matches the request was found in the pool, and the total wait time for a session request.
SQL> SELECT pool_name, num_requests, num_hits, num_misses 2* FROM v$cpool_stats; POOL_NAME NUM_REQUESTS NUM_HITS NUM_MISSES ---------------------------- ------------ ---------- ---------- SYS_DEFAULT_CONNECTION_POOL 1 0 1
V$CPOOL_CC_INFO
This view displays information about the pool-to-connection class mapping.
SQL> SELECT * FROM v$cpool_cc_info; POOL_NAME CCLASS_NAME ---------------------------- -------------- SYS_DEFAULT_CONNECTION_POOL SCOTT.SHARED
V$CPOOL_CC_STATS
This view provides connection class level statistics for the DRCP pool (per instance). This is like the V$CPOOL_STATS view, except that it includes each instance in the cluster.
SQL> SELECT cclass_name, num_requests, num_hits, num_misses 2* FROM v$cpool_cc_stats; CCLASS_NAME NUM_REQUESTS NUM_HITS NUM_MISSES -------------------- ------------ ---------- ---------- SCOTT.SHARED 1 0 1
No comments:
Post a Comment