Michael Styer wrote, on 21/04/2005 13:17:
Charles Jardine said:
I am still convinced that Michael should not need explicitly to
close his cursors. I would be interested to know if tracing shows
his cursors being destroyed in a timely fashion, since I suspect
that something may be maintaining references to the DBI handles for
longer than necessary.
I am sure that Oracle can close the cursors automatically in
Oracle 9.2.0.6. I would be very grateful if you could find out
for me if the same is true in your Oracle 8i database.
Could you please run the following simplified test program.
It doesn't run out of cursors in my setup. Does it in yours?
=====================================================
#!/bin/perl -w
use strict;
use DBI;
use DBD::Oracle qw(:ora_types);
my $dbh = DBI->connect('dbi:Oracle:', 'scott', 'tiger',
{RaiseError=>1, AutoCommit=>0, PrintError=>0});
{ my $proc = $dbh->prepare(
q{BEGIN OPEN :c FOR SELECT :i FROM DUAL; END;}
);
for (my $i = 0; $i < 1500; $i++) {
my $cursor;
$proc->bind_param(':i', $i);
$proc->bind_param_inout(':c', \$cursor, 0, {ora_type=>ORA_RSET});
$proc->execute;
print $cursor->fetchrow_array, "\n";
}
}
$dbh->disconnect;
=====================================================
and Charles Jardine also said:
Note that bind_param_inout creates a reference from the pl/sql
statement handle to the cursor statement handle, so the last
cursor retrieved will not be destroyed until the pl/sql handle
is destroyed.
OK. This sounds like we might be getting somewhere. I agree that it
sounds like something is keeping a reference to the cursor statement
handle. What you're saying, I believe, is that after
$sth1->bind_param_inout(':curref', \$sth2, 0, { ora_type => ORA_RSET }
);
I have a ref cursor statement handle in $sth2, but $sth1, the pl/sql
statement handle, *also* keeps a reference to the ref cursor statement
handle. Is that correct?
Yes. I have taken a level 2 trace of the above program (after modifying
it to do two iterations rather than 1500). This trace is included
below with my annotations - lines starting with ' [cj]'.
It shows that the first cursor handle is not DESTROYed when
the first instance of $cursor goes out of scope at the end of
the loop. The destructor is actually called from within
bind_param_input as the binding of the second $cursor causes
the reference count of the first $cursor to drop to zero.
Similarly the second cursor handle is not DESTROYed until
the pl/sql handle is destroyed.
I'm going to go through my code again this morning and see if I can
figure out what might be keeping references open. In general, what would
be a good way of tracing or otherwise checking on where I'm leaking
references? I've tried using DBI->trace, and that works, but it's not
the easiest way to track what has references to what. Does anyone know
of a better way of tracking Perl references?
Even a level 1 trace will tell you when statement handles are being
destroyed. Sadly, if they aren't being destroyed, it won't tell you why.
=====================================================
DBI::db=HASH(0x1affc0) trace level set to 0x0/2 (DBI @ 0x0/0) in DBI
1.46-nothread (pid 16900)
-> prepare for DBD::Oracle::db (DBI::db=HASH(0x32ab8)~0x1affc0 'BEGIN OPEN
:c FOR SELECT :i FROM DUAL; END;')
dbd_preparse scanned 2 distinct placeholders
<- prepare= DBI::st=HASH(0x1b0404) at perl2 line 13
-> bind_param for DBD::Oracle::st (DBI::st=HASH(0x1b0404)~0x1afff0 ':i' 0)
bind :i <== 0 (type 0)
bind :i <== 0 (size 1/2/0, ptype 5, otype 1)
<- bind_param= 1 at perl2 line 17
-> bind_param_inout for DBD::Oracle::st (DBI::st=HASH(0x1b0404)~0x1afff0
':c' SCALAR(0x1ad2f4) 0 HASH(0x1ad414))
bind :c <== undef (type 0, inout 0x1ad2f4, maxlen 0, attribs:
HASH(0x1ad414))
[cj] The above line is where the first curso hale is created. Its address is
1ad414.
<- bind_param_inout= 1 at perl2 line 18
-> execute for DBD::Oracle::st (DBI::st=HASH(0x1b0404)~0x1afff0)
dbd_st_execute BEGIN (out1, lob0)...
<> FETCH= 'DBD::Oracle::db' ('ImplementorClass' from cache) at DBI.pm line
1227
dbd_st_execute BEGIN returned (SUCCESS, rpc1, fn34, out1)
fbh 1: ':B1' NULLable, otype 1-> 5, dbsize 32/33, p32.s0
<- execute= 1 at perl2 line 19
-> fetchrow_array for DBD::Oracle::st (DBI::st=HASH(0x1b050c)~0x1ad414)
<- fetchrow_array= ( '0' ) [1 items] row1 at perl2 line 20
-> bind_param for DBD::Oracle::st (DBI::st=HASH(0x1b0404)~0x1afff0 ':i' 1)
bind :i <== 1 (type 0)
bind :i <== 1 (size 1/2/0, ptype 5, otype 1)
<- bind_param= 1 at perl2 line 17
-> bind_param_inout for DBD::Oracle::st (DBI::st=HASH(0x1b0404)~0x1afff0
':c' SCALAR(0x1b0620) 0 HASH(0x1b05e4))
bind :c <== undef (type 0, inout 0x1b0620, maxlen 0, attribs:
HASH(0x1b05e4))
-> DESTROY for DBD::Oracle::st (DBI::st=HASH(0x1ad414)~INNER)
[cj] This is where the first corsor hanle is destroyed. Note we are
[cj] within the second call of bind_param_inout
<- DESTROY= undef at perl2 line 18
<- bind_param_inout= 1 at perl2 line 18
-> execute for DBD::Oracle::st (DBI::st=HASH(0x1b0404)~0x1afff0)
dbd_st_execute BEGIN (out1, lob0)...
<> FETCH= 'DBD::Oracle::db' ('ImplementorClass' from cache) at DBI.pm line
1227
dbd_st_execute BEGIN returned (SUCCESS, rpc2, fn34, out1)
fbh 1: ':B1' NULLable, otype 1-> 5, dbsize 32/33, p32.s0
<- execute= 2 at perl2 line 19
-> fetchrow_array for DBD::Oracle::st (DBI::st=HASH(0x1b05f0)~0x1b03ec)
<- fetchrow_array= ( '1' ) [1 items] row1 at perl2 line 20
-> DESTROY for DBD::Oracle::st (DBI::st=HASH(0x1afff0)~INNER)
[cj] We have just entered the destructor for the pl/sql handle
-> DESTROY for DBD::Oracle::st (DBI::st=HASH(0x1b03ec)~INNER)
[cj] and now recursively entered the destructor for the second and last cursor
<- DESTROY= undef at perl2 line 13
<- DESTROY= undef at perl2 line 13
-> disconnect for DBD::Oracle::db (DBI::db=HASH(0x32ab8)~0x1affc0)
<- disconnect= 1 at perl2 line 24
-> DESTROY for DBD::Oracle::db (DBI::db=HASH(0x1affc0)~INNER)
<- DESTROY= undef
--
Charles Jardine - Computing Service, University of Cambridge
[EMAIL PROTECTED] Tel: +44 1223 334506, Fax: +44 1223 334679
