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

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.