Memory leak with DBI 1.52 & Oracle - Please Help

------=_NextPart_000_0017_01C6D997.AF20A880
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: 7bit

Hi, I'm no Perl expert, but I also would not consider myself a newbie. I
seem to have a memory leak issue with the DBI module (1.52) on Windows
connecting to Oracle (10gR2).



For reasons I won't go into at this time, I need to have a continuous loop
that will connect to many Oracle instances. I'm doing this using
parallelized subroutine calls and I do have to connect, perform the required
queries, and then disconnect inside the parallelized subroutine (leaving a
persistent connection unfortunately is not an option). The subroutine calls
must be parallelized so they are non-blocking.



What I find is that with repeated subsequent database connections, the
working set memory continuously grows until the Perl script exhausts all of
the memory on the machine. It does go up and down as procedures begin and
end, but it also grows over time. I've developed an overly simplified
version of my program (shown below) to illustrate/replicate the memory leak.



I'm using ActivePerl 5.8.8 (builds 816 and 818). With build 816 (and
whatever version of DBI comes with it), the script will run and continue to
loop until it crashes from lack of memory due to the leak. Upgrading to
build 818, (or build 816 with DBI 1.52 and DBD-Oracle 1.17) still shows the
memory leak but also throws an occasional Windows error: "An unhandled win32
exception occurred in perl.exe [5488] ". The strange part is that the
unhandled exception error will sometimes come on pretty much any iteration
of the loop (sometimes as soon as the 3rd, sometimes as late as the 30th).



Here is a simple test script that illustrates the leak, and the unhandled
win32 exception (exception is only with the later DBI versions):



use strict;

use DBI;

use subs::parallel;



sub DB_Connect : Parallel {

print "DB_Connect: parallel subroutine $_[0] is starting\n";

my $dbh =
DBI->connect('dbi:Oracle:host=127.0.0.1;sid=ORA1020;port=152 1','SYSTEM','MAN
AGER',{RaiseError=>0,PrintError=>1,AutoCommit=>0});

print "Connected to Database\n";

$dbh->disconnect || warn $dbh->errstr;

print "DB_Connect: parallel subroutine $_[0] is ending\n";

sleep 4;

}



main {

while (1) {

print "Starting Loop\n";

&DB_Connect("A");

&DB_Connect("B");

&DB_Connect("C");

print "Finished Loop\n";

sleep 5

}

}



Can someone please help me by confirming if this is a true leak/bug with the
DBI 1.52 module or am I doing something incorrectly? I don't think that the
problem is with subs::parallel since if I take out the DBI calls, the loop
will run continuously without error or memory leaks.



Help is greatly appreciated - I've been scouring the net for days and see
reports of similar problems but reports that they were fixed in 1.52.
Help and advise is really appreciated since I can't work out anything that
I'm doing wrong.



Thanks in advance for any comments or suggestions!!



Simon.

(simon@integritydbs.com)


------=_NextPart_000_0017_01C6D997.AF20A880--