comment on

Dear Perlmonkers,

I'm fighting a memory leak with DBI/DBD::Oracle which I do not really know how to tackle. I tracked it down to a simple code sequence like

{
my $sth;

sub _read_from_db {
    my ($array_ref) = @_;

    if (not defined $sth) {
        $sql = qq/ ... /;
        $sth = $dbh->prepare( $sql );
    }
    $sth->bind_param( ... );
    $sth->execute();
   
    @$array_ref = $sth->fetchrow_array();    
} 
}
[download]

The problem is that the script using the code steadily grows over runtime, always in 4 byte increments; the increment happens not with each iteration, but (apparently random) after 5, 6 or 7 iterations. I also found that it's the call to $sth->execute() that causes the increase in memory footprint.

The very same code runs OK on another (White Box Enterprise Linux release 3.0) system.

On the problematic system CentOS release 5 (Final) is installed.

I've tried to upgrade DBI, DBD::Oracle and Perl to the latest versions; no improvement. I downgraded DBI, DBD::Oracle and Perl with a few version numbers - same behavior.

But when I then compiled Perl without threads - SUCCESS! - problem gone, code worked OK.

No joy however: I specifically want and need to use threads once the script stops to leak memory.

It seems clear that it is a OS-specific problem; I'm just a bit lost for now not knowing really which direction to head.

I'm pretty sure that some of the monastery veterans have fought such wars before; so what would others do in this situation ?

Many thanks in advance.

Final update: it turned out to be not at all CentOS specific, not even a Perl or thread problem, but just an Oracle bug. I was able to overcome the problem without re-installing anything on server-side Oracle after (correctly - that was rather harder than expected!) rebuilding DBD::Oracle with Oracle Instant Client 10.2.0.3 (might work with other versions as well, it's just the version I happened to use). It was gsiems's suggestion that helped me find the solution. Thanks to all those who lent a helpful hand!

Update: after further googling and looking through documentation, it seems that it is more an Oracle 9iR2 problem (which is what I use):

Same problem on Mac OS
Same problem on Linux Suse 9.0

As I won't be able to change the installed Oracle at least for some time, I guess I won't have a quick answer here.

Update 2: Seems that HP-UX 11 has seen this too. But this last link also might bring the long awaited light on the matter:

There is a memory leak from the Oracle Client Interface on HP-UX 11

    * Scenario: A memory leak has been observed from the Oracle Client
+ Interface (OCI) 
              when using Oracle 9i Release 2 (9.2.0.1) on HP-UX 11.

    * Solution: This problem has been fixed by Oracle and the fix is a
+vailable by upgrading 
              to Oracle 9i Release 2 Database Server Patch Set 2 for H
+P9000 Series HP-UX (64-bit).
                This has the Oracle patch number of 2761332.
                To install the above Oracle patch, you might need to u
+pgrade your Oracle installer (OUI) 
              to version 2.2.0.18.0 first. The Oracle patch number for
+ this upgrade is 2878462.
[download]

Now, as it seems that the Oracle I deal with hasn't the released patches installed, it looks like there is nothing more to do or try before getting those patches applied.

Thanks and sorry for the noise ;)

Krambambuli

In reply to Centos 5 specific DBI memory leak by Krambambuli

Are you posting in the right place? Check out Where do I post X? to know for sure.
Posts may use any of the Perl Monks Approved HTML tags. Currently these include the following:
<code> <a> <b> <big> <blockquote> <br /> <dd> <dl> <dt> <em> <font> <h1> <h2> <h3> <h4> <h5> <h6> <hr /> <i> <li> <nbsp> <ol> <p> <small> <strike> <strong> <sub> <sup> <table> <td> <th> <tr> <tt> <u> <ul>
Snippets of code should be wrapped in <code> tags not <pre> tags. In fact, <pre> tags should generally be avoided. If they must be used, extreme care should be taken to ensure that their contents do not have long lines (<70 chars), in order to prevent horizontal scrolling (and possible janitor intervention).
Want more info? How to link or How to display code and escape characters are good places to start.


laziness, impatience, and hubris
	PerlMonks