Front page | perl.dbi.users |
Postings from February 2001
Re: Oracle DBD: space leak and max open cursors errors
Thread Previous
|
Thread Next
From:
Eli
Date:
February 21, 2001 08:19
Subject:
Re: Oracle DBD: space leak and max open cursors errors
Message ID:
3A93EA76.6F55895E@celera.com
oops, it was working because I reverted to an earlier version which
disconnects from the database after every 40 queries, not because of any
change between prepare and prepare_cached. So I guess I still have the
same problem.
-Eli
Eli wrote:
>
> 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