Database Resident Connection Pooling (DRCP )

If your app is unable to create connection pool or you are just searching for a fast and lazy way to implement one, you will find out that Oracle Database can do it for you. DRCP is a database side connection pool witch provides fast and easy way to implement a connection pooling for sharing your database connections.

In Oracle Database Administrator’s Guide there is nothing special about DRCP, just a procedure and few parameters to tune.

Lets try it out:

oracle@oracle112 ~]$ sql

SQL*Plus: Release 11.2.0.3.0 Production on Thu Oct 10 17:34:42 2013

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

SQL> EXECUTE DBMS_CONNECTION_POOL.RESTORE_DEFAULTS();

PL/SQL procedure successfully completed.

SQL> EXECUTE DBMS_CONNECTION_POOL.START_POOL();

PL/SQL procedure successfully completed.

SQL>

Now lets see what will happen when we try to open few connections to the database from a simple Perl script:

[oracle@oracle112 ~]$ cat drcp.pl
#!/usr/bin/perl
use DBI;

for(my $i=0;$i connect("dbi:Oracle:host=oracle112;sid=oratmp;port=1521;SERVER=POOLED","x","x") or die $DBI::errstr;
$dbh->disconnect;
}
[oracle@oracle112 ~]$ ./drcp.pl
[oracle@oracle112 ~]$

Seems our DRCP is working… does it actually:


SQL> col CCLASS_NAME for a45
SQL> select num_hits, num_misses,num_requests from v$cpool_cc_stats;

NUM_HITS NUM_MISSES NUM_REQUESTS
---------- ---------- ------------
   0   1   1
   0   1   1
   0   1   1
   0   1   1
   0   1   1

SQL>

That looks strange, we have just created 5 connections and none of them had been reused.

According to the OCI documentation, DRCP is sharing the connections between the same username, but also implements another separation based on the connection class /OCI_ATTR_CONNECTION_CLASS/. In addition there is another attribute called “OCI_ATTR_PURITY/connection purity” which specify whether you shall reuse the connection or create a new one. Lets take a look to our case and why our connections are not reused:

In order to catch what is happens on the database side I add a sleep in the script.

[oracle@oracle112 ~]$ cat drcp.pl
#!/usr/bin/perl
use DBI;

for(my $i=0;$i connect("dbi:Oracle:host=oracle112;sid=oratmp;port=1521;SERVER=POOLED","x","x") or die $DBI::errstr;
sleep(5);
$dbh->disconnect;
}
[oracle@oracle112 ~]$ ./drcp.pl

###while the script is running:

SQL> select CMON_ADDR,SESSION_ADDR,CONNECTION_ADDR,USERNAME,CCLASS_NAME,PURITY,CONNECTION_MODE from v$cpool_conn_info;

CMON_ADDR SESSION_ADDR CONNECTION_ADDR USERNAME CCLASS_NAME PURITY CONNECTION_MODE
---------------- ---------------- ---------------- --------------------------- --------------------------------------------- --------------------------- ---------------------------
000000007B961CE8 000000007B963728 X X.OCI:SP:6LL3t+AaSLTgQ3ABqMAa2A SELF RESERVED

SQL> /

CMON_ADDR SESSION_ADDR CONNECTION_ADDR USERNAME CCLASS_NAME PURITY CONNECTION_MODE
---------------- ---------------- ---------------- --------------------------- --------------------------------------------- --------------------------- ---------------------------
000000007B961CE8 000000007B963728 X X.OCI:SP:6LL5npgBSMHgQ3ABqMBqxA SELF RESERVED

SQL>

It seems that our script is connecting through our DRCP pool and is using PURITY=SELF which means to reuse the session, but actually the session is not reused, because there is a self generated connection class name – Defaults. So it seems that we shall put a common connection class and try again.

[oracle@oracle112 ~]$ cat drcp.pl
#!/usr/bin/perl
use DBI;

for(my $i=0;$i connect("dbi:Oracle:host=oracle112;sid=oratmp;port=1521;SERVER=POOLED","x","x",{ora_drcp_class=>'X'}) or die $DBI::errstr;
        sleep(5);
        $dbh->disconnect;
}
[oracle@oracle112 ~]$
[oracle@oracle112 ~]$ ./drcp.pl
[oracle@oracle112 ~]$ ./drcp.pl
[oracle@oracle112 ~]$

###while the script is running:

SQL> select CMON_ADDR,SESSION_ADDR,CONNECTION_ADDR,USERNAME,CCLASS_NAME,PURITY,CONNECTION_MODE from v$cpool_conn_info;

CMON_ADDR SESSION_ADDR CONNECTION_ADDR USERNAME CCLASS_NAME PURITY CONNECTION_MODE
---------------- ---------------- ---------------- ------------------ ------------------ ------------------ ------------------
000000007B961CE8 000000007B963728 X X.X SELF RESERVED

SQL> select num_hits, num_misses,num_requests from v$cpool_cc_stats where cclass_name='X.X';

NUM_HITS NUM_MISSES NUM_REQUESTS
---------- ---------- ------------
   9   1   10

SQL>

Quite different, No?

That was the Perl way, but there are different implementations in different languages. For example in PHP you shall put those parameters in your .ini file, for Python if you are using cx_Oracle you can set those parameters while creating the connection and so on.

The point is to be aware that its not enough just to start the pool and connect to the database with “SERVER=POOLED“.

Cheers,
Kovachev

Advertisements

2 thoughts on “Database Resident Connection Pooling (DRCP )

  1. Good post. I wanted to know that for a specific use case – in which I have say 100 databases and 1 database in the central database, now my app connects to that one database which spawns connections to any of the 100 databases as per requested by user to get some information about them. In this case does using DRCP makes same as I don`t want the connection to be killed if the user is running the query at the same time I don`t want too many connections to be opened to the db which I control by creating a profile on the database which limits the number of active sessions.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s