Perl DBD-Oracle 1.25 support for DRCP (Database Resident Connection Pool)

John Scoles from the Pythian Group released Perl DBD::Oracle 1.25. We will find something Changes. It supports DRCP. How to code it? we can check from "perldoc DBD::Oracle".

Oracle DRCP
DBD::Oracle now supports DRCP (Database Resident Connection Pool) so if you have an 11.2 database and the DRCP
is turned on you can now direct all of your connections to it simply adding ":POOLED" to the SID or setting a
connection attribute of ora_drcp, or set the SERVER=POOLED when using a TNSENTRY style connection or even by
setting an environment variable ORA_DRCP. All of which are demonstrated below;

$dbh = DBI->connect("dbi:Oracle:DB:POOLED","username","password")

$dbh = DBI->connect("dbi:Oracle:","username@DB:POOLED","password")

$dbh = DBI->connect("dbi:Oracle:DB","username","password",{ora_drcp=>1})

$dbh = DBI->connect("dbi:Oracle:DB","username","password",{ora_drcp=>1, ora_drcp_class=>"my_app", ora_drcp_min=>10})

$dbh = DBI->connect("dbi:Oracle:host=foobar;sid=ORCL;port=1521;SERVER=POOLED", "scott/tiger", "")

$dbh = DBI->connect("dbi:Oracle:", q{scott/tiger@(DESCRIPTION=
(ADDRESS=(PROTOCOL=TCP)(HOST= foobar)(PORT=1521))
(CONNECT_DATA=(SID=ORCL)(SERVER=POOLED)))}, "")

if ORA_DRCP environment var is set the just this

$dbh = DBI->connect("dbi:Oracle:DB","username","password")

Check perl module version and then start example for DRCP:

$ perl -e 'use DBI; print $DBI::VERSION,"\n";'
1.614
$ perl -e 'use DBD::Oracle; print $DBD::Oracle::VERSION,"\n";'
1.25

tnsnames.ora file. -- we can read more... Configuring Database Resident Connection Pooling

ORCL_POOL=
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=linuxtest01)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=orcl)(SERVER=POOLED)))

Code 1: (testdrcp.pl)

#!/usr/bin/perl -w
use DBI;
my $dbh = DBI->connect("dbi:Oracle:orcl_pool","scott","tiger")
||
die( $DBI::errstr . "\n" );
my $sth = $dbh->prepare("begin dbms_lock.sleep(5); end;");
$sth->execute();

test code 1:

$ perl testdrcp.pl
DBI connect('orcl_pool','scott',...) failed: ORA-28547: connection to server failed, probable Oracle Net admin error (DBD ERROR: OCISessionBegin) at testdrcp.pl line 4 ORA-28547: connection to server failed, probable Oracle Net admin error (DBD ERROR: OCISessionBegin)

because we don't start connection pool on database.

SQL> execute dbms_connection_pool.start_pool;

PL/SQL procedure successfully completed.

test code 1 again:

$ perl testdrcp.pl
-- NO ERROR --

SQL> select username, status, server, program from v$session where username='SCOTT'; -- while code running

USERNAME STATUS SERVER PROGRAM
------------------------------ -------- --------- ------------------------------------------------
SCOTT ACTIVE POOLED perl@linuxtest01 (TNS V1-V3)

SQL> select username, status, server, program from v$session where username='SCOTT'; -- code finished

no rows selected

That mean, database created connection pooling and closed after executed.
Code 2: (testdrcp.pl) -- use "ora_drcp"

#!/usr/bin/perl -w
use DBI;
my $dbh = DBI->connect("dbi:Oracle:orcl_pool","scott","tiger",{ora_drcp=>1})
||
die( $DBI::errstr . "\n" );
my $sth = $dbh->prepare("begin dbms_lock.sleep(5); end;");
$sth->execute();

test code 2:

$ perl testdrcp.pl
-- NO ERROR --

SQL> select username, status, server, program from v$session where username='SCOTT'; -- while code running

USERNAME STATUS SERVER PROGRAM
------------------------------ -------- --------- ------------------------------------------------
SCOTT ACTIVE POOLED perl@linuxtest01 (TNS V1-V3)

SQL> select username, status, server, program from v$session where username='SCOTT'; -- code finished

USERNAME STATUS SERVER PROGRAM
------------------------------ -------- --------- ------------------------------------------------
SCOTT INACTIVE POOLED perl@linuxtest01 (TNS V1-V3)

$ perl testdrcp.pl -- test again
-- NO ERROR --

SQL> select username, status, server, program from v$session where username='SCOTT'; -- while code running

USERNAME STATUS SERVER PROGRAM
------------------------------ -------- --------- ------------------------------------------------
SCOTT INACTIVE POOLED perl@linuxtest01 (TNS V1-V3)
SCOTT ACTIVE POOLED perl@linuxtest01 (TNS V1-V3)

SQL> select username, status, server, program from v$session where username='SCOTT'; -- code finished

USERNAME STATUS SERVER PROGRAM
------------------------------ -------- --------- ------------------------------------------------
SCOTT INACTIVE POOLED perl@linuxtest01 (TNS V1-V3)
SCOTT INACTIVE POOLED perl@linuxtest01 (TNS V1-V3)

From code, ora_drcp=>1, that make connection pooling be "INACTIVE" after executed (hold connection), but it created new connection class every time when connect database.

SQL> select * from V$CPOOL_CC_INFO;

POOL_NAME CCLASS_NAME
-------------------------------------------------- --------------------------------------------------
SYS_DEFAULT_CONNECTION_POOL SCOTT.OCI:SP:kJxcQGCWisTgQKjAIwEaOB
SYS_DEFAULT_CONNECTION_POOL SCOTT.OCI:SP:kJxcq/eyU8PgQKjAIwEaOD

Then How to reuse connection class?
Code 3:

#!/usr/bin/perl -w
use DBI;
my $dbh = DBI->connect("dbi:Oracle:orcl_pool","scott","tiger",{ora_drcp=>1, ora_drcp_class=>"my_app", ora_drcp_min=>10})
||
die( $DBI::errstr . "\n" );
my $sth = $dbh->prepare("begin dbms_lock.sleep(5); end;");
$sth->execute();

test code 3:

$ perl testdrcp.pl
-- NO ERROR --

SQL> select username, status, server, program from v$session where username='SCOTT'; -- while code running

USERNAME STATUS SERVER PROGRAM
------------------------------ -------- --------- ------------------------------------------------
SCOTT ACTIVE POOLED perl@linuxtest01 (TNS V1-V3)

SQL> select username, status, server, program from v$session where username='SCOTT'; -- code finished

USERNAME STATUS SERVER PROGRAM
------------------------------ -------- --------- ------------------------------------------------
SCOTT INACTIVE POOLED perl@linuxtest01 (TNS V1-V3)

$ perl testdrcp.pl --test again
-- NO ERROR --

SQL> select username, status, server, program from v$session where username='SCOTT'; -- while code running

USERNAME STATUS SERVER PROGRAM
------------------------------ -------- --------- ------------------------------------------------
SCOTT ACTIVE POOLED perl@linuxtest01 (TNS V1-V3)

SQL> select username, status, server, program from v$session where username='SCOTT'; -- code finished

USERNAME STATUS SERVER PROGRAM
------------------------------ -------- --------- ------------------------------------------------
SCOTT INACTIVE POOLED perl@linuxtest01 (TNS V1-V3)

From code, we use connection class, so connection reused connection class (my_app).

SQL> select * from V$CPOOL_CC_INFO;

POOL_NAME CCLASS_NAME
------------------------------ ------------------------------
SYS_DEFAULT_CONNECTION_POOL SCOTT.my_app

- Connect Attributes for DRCP :
ora_drcp - If you have an 11.2 or greater database your can utilize the DRCP by setting this attribute to 1 at connect time.
For convenience I have added support for a "ORA_DRCP" environment variable that you can use at the OS level to set this value.

ora_drcp_class - If you are using DRCP, you can set a CONNECTION_CLASS for your pools as well. As sessions from a DRCP cannot be shared by users, you can use this setting to identify the same user across different applications. OCI will ensure that session belonging to a "class" are not shared outside the class".
The values for ora_drcp_class cannot contain an "*" and must be less than 1024 characters.
This value can be set at the environment level with "ORA_DRCP_CLASS".

ora_drcp_min - Is an optional value that specifies the minimum number of sessions that are initially opened. New sessions are only opened after this value has been reached.
The default value is "4" and any value above "0" is valid.
Generally, it should be set to the number of concurrent statements the application is planning or expecting to run.
This value can be set at the environment level with "ORA_DRCP_MIN".

ora_drcp_max - Is an optional value that specifies the maximum number of sessions that can be open at one time. Once reached no more session can be opened until one becomes free. The default value is "40" and any value above "1" is valid. You should not set this value lower than ora_drcp_min as that will just waste resources.
This value can be set at the environment level with "ORA_DRCP_MAX".

ora_drcp_incr - Is an optional value that specifies the next increment for sessions to be started if the current number of sessions are less than ora_drcp_max. The default value is "2" and any value above "0" is valid as long as the value of ora_drcp_min + ora_drcp_incr is not greater than ora_drcp_max.
This value can be set at the environment level with "ORA_DRCP_INCR".

- Configuration Parameters for DRCP (Database)
- Data Dictionary Views for DRCP

read more ...about DBD-Oracle