Database Resident Connection Pooling in Oracle 11g

In traditional client/server architectures, there is a one-to-one correspondence between a user session and a database connection. In Web-based systems however, this may not be the case.

Web based systems are “stateless” in nature--when we visit a page, a database connection is established with the database and when the page loading is over, the connection to the database is severed. Later, when the user clicks again on the page, a new connection is established that is severed after the desired effect. This process makes it unnecessary to maintain a large number of simultaneous connections.

According to the Tom Kytes : 

Connection pooling is generally the practice of a middle tier (application server) getting N connections to a database.These connections are stored in a pool in the middle tier, an "array" if we will.  Each connection is set to "not in use" . When a user submits a web page to the application server, it runs a piece of code, our code says "i need to get to the database", instead of connecting right there and then (that takes time), it just goes to this pool and says "give me a connection please". the connect pool software marks the connection as "in use" and gives it to us.  We generate the page, format the html whatever -- and then return the connection to the pool where someone else can use it.In this fashion, using connections to the database, we can avoid the connect/disconnect overhead.

Establishing connections is expensive in overhead, so connection pooling is an important requirement in the apps. when a page needs database access, it allocates one of the already established connections out of the pool. After the work is done, the Web session returns the connection back to the pool.

The problem with traditional client-side or middle-tier connection pooling, however, is:

• Each pool is confined to a single middle-tier node.

• A proliferation of pools results in excessive number of pre-allocated database servers and excessive database server memory consumption.

• Workload is distributed unequally across pools.

To obviate these problems, Oracle Database 11g provides a server-side pool called Database Resident Connection Pool (DRCP). DRCP is available to all database clients that use the OCI driver including C, C++, and PHP.

Oracle Database 11g comes preinstalled with a default connection pool but it is shut down. To start it, use:
SQL> execute dbms_connection_pool.start_pool;
Now to connect to the pooled connections instead of a regular session, all we have to do is add a line (SERVER=POOLED) to the TNS entry as shown below :
e.g;

PRONE3_POOL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = xxxx)(PORT = 1521))
(CONNECT_DATA =
(SERVER = POOLED)
(SID = PRONE3)
)
)

The clients can connect to the connection pool, using the connect string PRONE3_POOL. That’s it. Now our apps will connect to the pool instead of the server. If we use the standard connect string without the TNSNAMES.ORA file, we can use the POOLED clause. For instance, in PHP, you will connect as:

$c = oci_pconnect(‘myuser’, ‘mypassword’,’xxxx/PRONE3:POOLED’);
or
$c = oci_pconnect(‘myuser’, ‘mypassword’,’PRONE3_POOLED’);

In the above description we started the default pool that comes with Oracle with the default options. we can use the procedure CONFIGURE_POOL in the supplied package DBMS_CONNECTION_POOL :

For More detail visit below links :
http://download.oracle.com/docs/cd/B14117_01/win.101/b10117/features001.htm
http://download.oracle.com/docs/cd/B10501_01/java.920/a96654/connpoca.htm


Enjoy     :-)