DBD::Oracle and Memory problem

[Suresh <su...@yahoo.com>]

Hi all,

I am running RedHat Linux 6.1 / Apache 1.3.12 /Mod_Perl / DBI and using Oracle 8.0.5 as database
back end for web application we are developing. Everything works fine, but when i was testing for
memory problems, i found that each request of any file accessing the database is increasing the
process size.
So i wrote a small program (i have included the code down ) which just access the db for a table
and prints the results, and still found that to happen. ( i set up apache to have 1 child process,
and used top ( with sorted by memory) to find this out ). 

Does the oracle libraries ( or DBD::Oracle )not free up some memory or am i missing some thing. 


<%
use strict;
use DBI;
use DBD::Oracle qw(:ora_types);

my $dbh2 = DBI->connect("DBI:Oracle:ORCL", "scott" , "tiger" );
my $sql = "select * from user_sec" ;
my $sth = $dbh2->prepare($sql);
my @data;

$sth->execute();

while( @data = $sth->fetchrow_array() )
{
        print "$data[0] , $data[1] <br>";
}
$sth->finish;
$dbh2->disconnect;
%>


thanks in advance,
Suresh

__________________________________________________
Do You Yahoo!?
Get Yahoo! Mail - Free email you can access from anywhere!
http://mail.yahoo.com/

Re: DBD::Oracle and Memory problem

[ar...@goshen.edu]

I don\'t know what\'s causing the memory \"leak\", but I know
that you don\'t have to include any DBD drivers, if you are
using DBI. DBI parses the connect string and locates the
appropriate DBD driver (DBD::Oracle in your case).

Arsh

Quoting Suresh <su...@yahoo.com>:

> Hi all,
> 
> I am running RedHat Linux 6.1 / Apache 1.3.12 /Mod_Perl /
DBI and using
> Oracle 8.0.5 as database
> back end for web application we are developing. Everything
works fine, but
> when i was testing for
> memory problems, i found that each request of any file
accessing the
> database is increasing the
> process size.
> So i wrote a small program (i have included the code down
) which just
> access the db for a table
> and prints the results, and still found that to happen. (
i set up apache
> to have 1 child process,
> and used top ( with sorted by memory) to find this out ). 
> 
> Does the oracle libraries ( or DBD::Oracle )not free up
some memory or am i
> missing some thing. 
> 
> 
> <%
> use strict;
> use DBI;
> use DBD::Oracle qw(:ora_types);
> 
> my $dbh2 = DBI->connect(\"DBI:Oracle:ORCL\", \"scott\" ,
\"tiger\" );
> my $sql = \"select * from user_sec\" ;
> my $sth = $dbh2->prepare($sql);
> my @data;
> 
> $sth->execute();
> 
> while( @data = $sth->fetchrow_array() )
> {
>         print \"$data[0] , $data[1] <br>\";
> }
> $sth->finish;
> $dbh2->disconnect;
> %>
> 
> 
> thanks in advance,
> Suresh
> 
> __________________________________________________
> Do You Yahoo!?
> Get Yahoo! Mail - Free email you can access from anywhere!
> http://mail.yahoo.com/
> 

Re: DBD::Oracle and Memory problem

[Perrin Harkins <pe...@primenet.com>]

On Wed, 26 Jul 2000, Suresh wrote:
> I am running RedHat Linux 6.1 / Apache 1.3.12 /Mod_Perl / DBI and
> using Oracle 8.0.5 as database back end for web application we are
> developing. Everything works fine, but when i was testing for memory
> problems, i found that each request of any file accessing the database
> is increasing the process size.

The Oracle 8i OCI libraries do have a bug (or a design consequence) that
causes them to allocate space for the entire row cache that you've
enabled, i.e. if you set row cache to 10000 and select something with 3
rows of data it will still allocate memory for 10000 rows.  However, even
here the memory gets freed when your statement handle gets destroyed
(which is never if you use prepare_cached), so I'm not sure why this would
be happening to you with the older OCI and normal prepare statements.

One thing to keep in mind is that (as Doug has explained in an earlier
e-mail to this list) your perl variables will not free up their memory
unless you explicitly undef them.  If your data is the same for each query
though, the process shouldn't be growing.  I'd suspect a problem in OCI.

- Perrin