Front page | perl.dbi.users |
Postings from February 2001
RE: Oracle DBD: space leak and max open cursors errors
Thread Previous
|
Thread Next
From:
andrew_roberts
Date:
February 21, 2001 09:07
Subject:
RE: Oracle DBD: space leak and max open cursors errors
Message ID:
7C4F091F6140D411A29300508B5C73B9021AD4FA@usrymx04.merck.com
For the record...the $sth->finish statement does NOT release the cursor
associated with the statement. A cursor is created by the "prepare" method
and is not released until the statement handle it creates goes out of scope
or is explicitly undefined. "Finish" releases the current "select list" on
a select statement that has been executed, but from which all returned rows
have not been fetched. This is NOT the same as releasing the cursor...a
"finished" statement can be executed again, precisely because the cursor is
retained.
As for prepare_cache, if I understand correctly the whole reason it exists
is to cache the statement handle (and thus the cursor) for re-use, so
statements returned by prepare_cache will by definition not go away
automatically (until, I guess, the cache "fills up" or is somehow cleansed).
> ----------
> From: Eli[SMTP:eli.venter@celera.com]
> Sent: Wednesday, February 21, 2001 10:37 AM
> To: Steven Lembark
> Cc: dbi-users@perl.org; Tim.Bunce@ig.co.uk
> Subject: Re: Oracle DBD: space leak and max open cursors errors
>
> We are doing something quite similar to your code below. The sth was
> prepared in a function and then returned where it is executed and then
> goes out of scope. Since we are calling a stored procedure there is no
> data to fetch. Reading you code gave me the idea to replace the
> prepare_cached with prepare and the problem seems to go away. So it
> seems the prepare_cached has some reference to the sth that prevents it
> from getting cleaned up. Calling $sth->finish before the sth goes out of
> scope doesn't seem to help.
>
> thanks for the brain stimulation...I guess my problem is solved, though
> it does seem prepare_cached or finish might not be working correctly
> with bound inout params.
>
> -Eli
>
> Steven Lembark wrote:
> >
> > Eli wrote:
> > >
> > > I guess I wasn't completely clear. This process is a long running
> server
> > > process that will do thousands of statements, so just increasing the
> max
> > > number of open cursors isn't the correct solution. What I need to do
> is
> > > figure out how to release the open cursors that DBI is creating during
> > > the binding or execution of the inout param. I don't really know OCI,
> so
> > > even a suggestion of what needs to be called where would be useful.
> > >
> > > Thanks for the help
> > >
> > > -Eli
> > >
> > > Eli wrote:
> > > >
> > > > I'm getting:
> > > >
> > > > DBD::Oracle::db disconnect failed: ORA-00600: internal error code,
> > > > arguments: [729], [360], [space leak], [], [], [], [], [] (DBD
> ERROR:
> > > > OCISessionEnd)
> > > >
> > > > This is after calling a stored procedure which returns some data
> through
> > > > a inout parameter around 50 times. The disconnect is in our error
> > > > handeling. The error that sends us to disconnect is:
> > > >
> > > > DBD::Oracle::st execute failed: ORA-01000: maximum open cursors
> > > > exceeded
> > > >
> > > > Our DBI code inside the loop looks like:
> > > >
> > > > my $procedure = q{
> > > > BEGIN
> > > > RAP.GET_NEXT_JOB(:id, :cmd, :bsub);
> > > > END;
> > > > };
> > > > my $sth = $dbh->prepare_cached( $procedure ) || confess "Can't
> > > > prepare";
> > > >
> > > > $sth->bind_param_inout(":id", $identifier, 20, DBI::SQL_INTEGER);
> > > > $sth->bind_param_inout(":cmd", $command, 512, DBI::SQL_VARCHAR);
> > > > $sth->bind_param_inout(":bsub", $bsub, 512, DBI::SQL_VARCHAR);
> > > >
> > > > $sth->execute;
> > > >
> > > > We tried putting a $sth->finish after the execute, but that doesn't
> > > > change anything. It seems to be something to do with the inout param
> > > > because we do something very similar with only in params and never
> have
> > > > the problem.
> > > >
> > > > Any ideas or suggestions?
> > > >
> > > > thanks
> > > >
> > > > -Eli
> >
> > use lexicals for statement handles that go out of scope after
> > you're done with them. the destructor will handle it. or call
> > the destructor explicitly. or store the statement handles in,
> > say, a hash and delete the hash element when you detect that the
> > statement handle will no longer be used.
> >
> > e.g.,
> >
> > sub runaquery
> > {
> > my $sth = $dbh->prepare( shift );
> >
> > $sth->execute( @_ );
> >
> > $sth->fetchall_arrayref
> > }
> >
> > if you need the statement handles to live longer than this
> > then you'll need an explicit 'zap' function for the caller:
> >
> > my %handlz = ();
> >
> > sub newquery
> > {
> > my $id = shift;
> >
> > my $sql = $handlz{$id}{sql} = shift;
> >
> > $handlz{$id}{sth} = 0;
> >
> > $id
> > }
> >
> > sub runaquery
> > {
> > my $id = shift;
> >
> > my $sth = $handlz{$id}{sth} ||= $dbh->prepare( $handlz{$id}{sql}
> ) or
> > croak ...
> >
> > $sth->execute( @_ );
> >
> > $sth->fethall_arrayref;
> > }
> >
> > sub zapaquery
> > {
> > my $id = shift;
> >
> > if( $handlz{$id} )
> > {
> > delete $handlz{$id};
> > "the deed is done, $id has been zapped"
> > }
> > else
> > {
> > "no such item, $id, go check your notes..."
> > }
> > }
> >
> > zapping the hash item does away with the statement handle via its
> > destructor.
> >
> > --
> > Steven Lembark 2930 W. Palmer St.
> > Chicago, IL 60647
> > lembark@wrkhors.com 800-762-1582
>
Thread Previous
|
Thread Next