| Oracle® Database Administrator's Guide 11g Release 1 (11.1) Part Number B28310-01 |
|
|
View PDF |
| Oracle® Database Administrator's Guide 11g Release 1 (11.1) Part Number B28310-01 |
|
|
View PDF |
Database Resident Connection Pooling (DRCP) provides a connection pool in the database server for typical Web application usage scenarios where the application acquires a database connection, works on it for a relatively short duration, and then releases it. DRCP pools "dedicated" servers, which is the equivalent of a server foreground and a database session combined and henceforth are referred to as the "pooled" servers.
DRCP complements middle-tier connection pools that share connections between threads in a middle-tier process. In addition, DRCP enables sharing of database connections across middle-tier processes on the same middle-tier host and even across middle-tier hosts. This results in significant reduction in key database resources needed to support a large number of client connections, thereby reducing the database tier memory footprint and boosting the scalability of both middle-tier and database tiers. Having a pool of readily available servers also has the additional benefit of reducing the cost of creating and tearing down client connections.
DRCP is especially relevant for architectures with multi-process single threaded application servers (such as PHP/Apache) that cannot perform middle-tier connection pooling. The database can still scale to tens of thousands of simultaneous connections with DRCP.
When To Use Database Resident Connection Pooling
Database resident connection pooling is useful when multiple clients access the database and when any of the following apply:
A large number of client connections need to be supported with minimum memory usage.
The client applications are similar and can share or reuse sessions.
Applications are similar if they connect with the same database credentials and use the same schema.
The client applications acquire a database connection, work on it for a relatively short duration, and then release it.
Session affinity is not required across client requests.
There are multiple processes and multiple hosts on the client side.
Advantages of Database Resident Connection Pooling
Using database resident connection pooling provides the following advantages:
Enables resource sharing among multiple middle-tier client applications.
Improves scalability of databases and applications by reducing resource usage.
Table 4-1 lists the differences between dedicated servers, shared servers, and database resident connection pooling.
Table 4-1 Differences Between Dedicated Servers, Shared Servers, and Database Resident Connection Pooling
| Dedicated Servers | Shared Servers | 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. |
Example of Memory Usage for Dedicated Server, Shared Server, and Database Resident Connection Pooling
Consider an application in which the memory required for each session is 400 KB and the memory required for each server process is 4 MB. The pool size is 100 and the number of shared servers used is 100.
If there are 5000 client connections, the memory used by each configuration is as follows:
Dedicated Server
Memory used = 5000 X (400 KB + 4 MB) = 22 GB
Shared Server
Memory used = 5000 X 400 KB + 100 X 4 MB = 2.5 GB
Out of the 2.5 GB, 2 GB is allocated from the SGA.
Database Resident Connection Pooling
Memory used = 100 X (400 KB + 4 MB) + (5000 X 35KB)= 615 MB
You cannot perform the following activities with pooled servers:
Shut down the database.
Stop the database resident connection pool.
Change the password for the connected user.
Use shared database links to connect to a database resident connection pool that is on a different instance.
Use all 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 via OCIConnectionPool.
DDL statements that pertain to database users in the pool need to be performed carefully, as the pre-DDL sessions in the pool can still be given to clients post-DDL. For example, while dropping users, ensure that there are no sessions of that user in the pool and no connections to the Broker that were authenticated as that user.
Sessions with explicit roles enabled, that are released to the pool, can be later handed out to connections (of the same user) that need the default logon role. Avoid releasing sessions with explicit roles, and instead terminate them.
