Re: DBD::Oracle DRCP_1.25
On Jul 6, 7:36 am, sco...@pythian.com (John Scoles) wrote: > > Gee didn't know anyone else was even interested in DRCP. good to see > there is some interest I know this post is more than a month old, but I am very excited about this. I just found that DBD::Oracle 1.2.5 was nearly ready. I have been looking forward to DRCP support for awhile now. We have Oracle 11.1 and 11.2 running on HP-UX with Perl 5.10, and we have a production app that makes thousands of connections a day using Perl (and a web interface in PHP that does the same thing, but it has pooling there.) Anyway, I am looking forward to getting this onto our test system, and trying it out. Thanks for all your hard work. -- Cliff
Re: DBD::Oracle DRCP_1.25
On 9.07.2010 00:41, John Scoles wrote:
Hi Luben
I have incorporated your patch into the DRCP branch and I have also merged
that branch back into trunk for any testing that you will be doing I would
try it with the Trunk which you can find here
http://svn.perl.org/modules/dbd-oracle/trunk
I went with the ORA_DRCP_* for the attribute names rather than POOL.
as well I made ORA_DRCP_CLASS an optional value. Some users my not want to
use it.
Thanks again
John Scoles
Nice, this are better names. Here comes a patch to suppress warnings in
scripts that are invoked with "perl -w"
luben
Index: Oracle.pm
===
--- Oracle.pm (revision 14240)
+++ Oracle.pm (working copy)
@@ -189,10 +189,10 @@
} split /\s*;\s*/, $dbname;
my %dbname = ( PROTOCOL => 'tcp', @dbname );
-if ($dbname{SERVER} eq "POOLED") {
- $attr->{ora_drcp}=1;
-
+if ((exists $dbname{SERVER}) and ($dbname{SERVER} eq "POOLED")) {
+$attr->{ora_drcp}=1;
}
+
# extract main attributes for connect_data portion
my @connect_data_attr = qw(SID INSTANCE_NAME SERVER SERVICE_NAME );
my %connect_data = map { ($_ => delete $dbname{$_}) }
Re: DBD::Oracle DRCP_1.25
Hi Luben I have incorporated your patch into the DRCP branch and I have also merged that branch back into trunk for any testing that you will be doing I would try it with the Trunk which you can find here http://svn.perl.org/modules/dbd-oracle/trunk I went with the ORA_DRCP_* for the attribute names rather than POOL. as well I made ORA_DRCP_CLASS an optional value. Some users my not want to use it. Thanks again John Scoles On Wed, Jul 7, 2010 at 4:34 PM, luben karavelov wrote: > As I have promissed, here comes the documentation patch. I am not native > speeker, so may be it will need an edit. > > Also, I have added processing of environment valiables ORA_POOL_CLASS, > ORA_POOL_MIN, ORA_POOL_MAX, ORA_POOL_INCR if there is ORA_DRCP env set. > > Best regards > luben > -- New! Learn why & how to love your data with Pythian's new webinar series. Topics, details & register: http://www.pythian.com/webinars
Re: DBD::Oracle DRCP_1.25
As I have promissed, here comes the documentation patch. I am not native
speeker, so may be it will need an edit.
Also, I have added processing of environment valiables ORA_POOL_CLASS,
ORA_POOL_MIN, ORA_POOL_MAX, ORA_POOL_INCR if there is ORA_DRCP env set.
Best regards
luben
Index: Oracle.pm
===
--- Oracle.pm (revision 14227)
+++ Oracle.pm (working copy)
@@ -227,14 +227,26 @@
# create a 'blank' dbh
$user = '' if not defined $user;
-(my $user_only = $user) =~ s:/.*::;
-
-if (substr($dbname,-7,7) eq ':POOLED'){
- $dbname=substr($dbname,0,-7);
- $attr->{ora_drcp} = 1;
+(my $user_only = $user) =~ s:/.*::;
+
+if (substr($dbname,-7,7) eq ':POOLED'){
+ $dbname=substr($dbname,0,-7);
+ $attr->{ora_drcp} = 1;
+}
+elsif ($ENV{ORA_DRCP}){
+$attr->{ora_drcp} = 1;
+if (exists $ENV{ORA_POOL_CLASS}) {
+$attr->{ora_pool_class} = $ENV{ORA_POOL_CLASS}
}
-elsif ($ENV{ORA_DRCP}){
- $attr->{ora_drcp} = 1;
+if (exists $ENV{ORA_POOL_MIN}) {
+$attr->{ora_pool_min} = $ENV{ORA_POOL_MIN}
+}
+if (exists $ENV{ORA_POOL_MAX}) {
+$attr->{ora_pool_max} = $ENV{ORA_POOL_MAX}
+}
+if (exists $ENV{ORA_POOL_INCR}) {
+$attr->{ora_pool_incr} = $ENV{ORA_POOL_INCR}
+}
}
my ($dbh, $dbh_inner) = DBI::_new_dbh($drh, {
@@ -1346,7 +1358,7 @@
$dbh = DBI->connect('dbi:Oracle:','usern...@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_pool_class=>'my_app', ora_pool_min=>10})
$dbh =
DBI->connect('dbi:Oracle:host=foobar;sid=ORCL;port=1521;SERVER=POOLED',
'scott/tiger', '')
@@ -1354,7 +1366,7 @@
(ADDRESS=(PROTOCOL=TCP)(HOST= foobar)(PORT=1521))
(CONNECT_DATA=(SID=ORCL)(SERVER=POOLED)))}, "")
- if ORA_DRCP environemt var is set the just this
+ if ORA_DRCP and ORA_POOL_CLASS environemt var are set you could use just this
$dbh = DBI->connect('dbi:Oracle:DB','username','password')
@@ -1530,13 +1542,60 @@
=item ora_drcp
-If you have an 11.2 or greater database your can utilize the the DRCP by
setting
-this attribute to 1 at connect time.
+If you have an 11.2 or greater database your can utilize the the DRCP
+(Database Resident Connection Pooling) 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. If used it will take the value at the connect stage.
+=item ora_pool_class
+
+If you use DRCP, you could set CONNECTION_CLASS at connection time.
+
+The connections are pooled based on unique SID/USERNAME/CONNECTION_CLASS.
+If you do not set the class attribute the connections will be pooled only
+within the boundaries of the process, becoming equivalent to a plain
+client-side session pooling application.
+
+For convenience I have added support for a 'ORA_POOL_CLASS'
+environment variable that you can use at the OS level to set this
+value. It will be used only if you use 'ORA_DRCP' environment variable.
+If used it will take the value at the connect stage.
+
+=item ora_pool_min
+
+If you use DRCP, you could set initial servers in the pool that will get
+started. Default value is 4 as per DBMS_CONNECTION_POOL default.
+
+For convenience I have added support for a 'ORA_POOL_MIN'
+environment variable that you can use at the OS level to set this
+value. It will be used only if you use 'ORA_DRCP' environment variable.
+If used it will take the value at the connect stage.
+
+
+=item ora_pool_max
+
+If you use DRCP, you could set maximum servers in the pool. Default value
+is 40 as per DBMS_CONNECTION_POOL default.
+
+For convenience I have added support for a 'ORA_POOL_MAX'
+environment variable that you can use at the OS level to set this
+value. It will be used only if you use 'ORA_DRCP' environment variable.
+If used it will take the value at the connect stage.
+
+=item ora_pool_incr
+
+If you use DRCP, you could set how much new servers will get started if all
+servers in the pool are busy. Default value is 2 as per DBMS_CONNECTION_POOL
+default.
+
+For convenience I have added support for a 'ORA_POOL_INCR'
+environment variable that you can use at the OS level to set this
+value. It will be used only if you use 'ORA_DRCP' environment variable.
+If used it will take the value at the connect stage.
+
=item ora_session_mode
The ora_session_mode attribute can be used to connect with SYSDBA
Re: DBD::Oracle DRCP_1.25
On 6.07.2010 14:36, John Scoles wrote:
luben wrote:
Gee didn't know anyone else was even interested in DRCP. good to see
there is some interest
Just to let you know that branch is in the very Beta stages so expect a
few things to be missing
Yes, I know it is in beta. I was just intereseted.
Ok would like to see the code for that/
I have attached the patch against DRCP branch
Finally, if this work is accepted I will add proper documentation for
the new attributes.
That would save me some trouble.
Now I have a little favour to ask of you. Seems I am getting a problem
with 31lob.t test 8 the sql "BEGIN ? := DBMS_LOB.GETLENGTH( ? ); END;";
does not return the correct length when used with DRCP
If you have half a moment and can take a look at that one that would be
great.
Tomorrow I will send a patch for the documentation.
I am not experienced with perl driver hacking but I will take a look on
the failing LOB test and try to figure what is going on.
Best regards
luben
Index: dbdimp.c
===
--- dbdimp.c(revision 14227)
+++ dbdimp.c(working copy)
@@ -388,8 +388,21 @@
#ifdef ORA_OCI_112
/*check to see if the user is connecting with DRCP */
+STRLEN svp_len;
if (DBD_ATTRIB_TRUE(attr,"ora_drcp",8,svp))
imp_dbh->using_drcp = 1;
+if ((svp=DBD_ATTRIB_GET_SVP(attr, "ora_pool_class", 14)) && SvOK(*svp)) {
+if (!SvPOK(*svp))
+croak("ora_pool_class is not a string");
+imp_dbh->pool_class = (text *) SvPV (*svp, svp_len );
+imp_dbh->pool_classl= (ub4) svp_len;
+}
+if (DBD_ATTRIB_TRUE(attr,"ora_pool_min",12,svp))
+DBD_ATTRIB_GET_IV( attr, "ora_pool_min", 12, svp, imp_dbh->pool_min);
+if (DBD_ATTRIB_TRUE(attr,"ora_pool_max",12,svp))
+DBD_ATTRIB_GET_IV( attr, "ora_pool_max", 12, svp, imp_dbh->pool_max);
+if (DBD_ATTRIB_TRUE(attr,"ora_pool_incr",13,svp))
+DBD_ATTRIB_GET_IV( attr, "ora_pool_incr", 13, svp,
imp_dbh->pool_incr);
#endif
/* check to see if DBD_verbose or ora_verbose is set*/
if (DBD_ATTRIB_TRUE(attr,"dbd_verbose",11,svp))
@@ -793,6 +806,10 @@
if (imp_dbh->using_drcp) { /* connect uisng a
DRCP */
ub4 purity = OCI_ATTR_PURITY_SELF;
+/* some pool default values */
+if (!imp_dbh->pool_min ) imp_dbh->pool_min=4;
+if (!imp_dbh->pool_max ) imp_dbh->pool_max=40;
+if (!imp_dbh->pool_incr) imp_dbh->pool_incr=2;
OCIHandleAlloc_ok(imp_dbh->envhp,
&imp_dbh->poolhp, OCI_HTYPE_SPOOL, status);
@@ -801,14 +818,14 @@
imp_dbh->poolhp,
(OraText **)
&imp_dbh->pool_name,
(ub4 *)
&imp_dbh->pool_namel,
- dbname,
+ (OraText *) dbname,
strlen(dbname),
- 10,
- 100,
- 4,
- uid,
+ imp_dbh->pool_min,
+ imp_dbh->pool_max,
+ imp_dbh->pool_incr,
+ (OraText *) uid,
strlen(uid),
- pwd,
+ (OraText *) pwd,
strlen(pwd),
status);
@@ -827,6 +844,10 @@
OCIAttrSet_log_stat(imp_dbh->authp,
(ub4) OCI_HTYPE_AUTHINFO,
&purity, (ub4)
0,(ub4) OCI_ATTR_PURITY, imp_dbh->errhp, status);
+ OCIAttrSet_log_stat(imp_dbh->authp,
(ub4) OCI_HTYPE_AUTHINFO,
+ (OraText *)
imp_dbh->pool_class, (ub4) imp_dbh->pool_classl,
+(ub4) OCI_ATTR_CONNECTION_CLASS,
imp_dbh->errhp, status);
+
cred_type = ora_parse_uid(imp_dbh,
&uid, &pwd);
OCISessionGet_log_stat(imp_dbh->envhp,
imp_dbh->errhp, &imp_dbh->svchp, imp_dbh->authp,
@@ -1103,6 +1124,7 @@
{
dTHX;
STRLEN kl;
+STRLEN vl;
char *key = SvPV(
Re: DBD::Oracle DRCP_1.25
luben wrote: Gee didn't know anyone else was even interested in DRCP. good to see there is some interest Just to let you know that branch is in the very Beta stages so expect a few things to be missing Hello, I have noticed and installed DRCP_1.25 branch from DBD::Oracle repository. It seems to work fine but it's missing some essential functionality in order to make it usefull. So I have made local modifications (some of them are not essential) and all seems to work fine here. What I have done: 1. essential - added ora_pool_class connection attribute that sets OCI_ATTR_CONNECTION_CLASS on the obtained connection. It is essential because without setting this attribute the pooled sessions are not shared between processes. From oracle documentation: > If no connection class is specified, the OCISessionPool’s name is implicitly used > as the connection class by all the requests using that OCISessionPool, resulting in > sharing only within a session pool, becoming equivalent to a plain client-side > session pooling application. Ok would like to see the code for that/ 2.non-essential - Added ora_pool_min, ora_pool_max, ora_pool_incr connections attributes that are used to set corresponding values for OCISessionPoolCreate. - Set default values for the above parameters to follow default values of DBMS_CONNECTION_POOL package (principle of least surprise) I have those stubbed in for now as I was deliberately using small min and max for testing and will check them in soon I most likely. Part of the plan was to use the default values. I have some questions: 1. Do you have suggestions for different names of attributes? I was considering ora_drcp_class etc. but I am not sure what will be the best. ora_drcp_conn_class would be better so as not to get it confused with any other thing that might be a 'class' of some sort 2. If my work is interesting for the further development of DBD::Oracle, whom I should send a patch? Just post it here on this thread or on DBI-users just make sure it is patched from the DRCP branch. Finally, if this work is accepted I will add proper documentation for the new attributes. That would save me some trouble. Now I have a little favour to ask of you. Seems I am getting a problem with 31lob.t test 8 the sql "BEGIN ? := DBMS_LOB.GETLENGTH( ? ); END;"; does not return the correct length when used with DRCP If you have half a moment and can take a look at that one that would be great. Cheers John Scoles Best regards luben karavelov
