Re: Oracle cursors and purge

[prev] [thread] [next] [Date index for 2004/11/12]

That's it!

I narrowed down the part of the code where I'm having the problem, and 
it is with some regular DBI-style database access. I do this:

      my $dbh = Package::DBI->db_Main();

to get the handle and pass it off to some subs that run standard 
DBI-style queries. These subs all use bind parameters and call finish. 
When I run this code with the suggested debug info in there, using the 
module versions I sent before, I get the output below (the stars are 
each loop with ~200 iterations):

******************************
we have 24statements
we have 0databases
There are 38 statement handles, of which 0 are active.
Was caching 4 statement handles
******************************
we have 24statements
we have 0databases
There are 914 statement handles, of which 0 are active.
Was caching 5 statement handles
******************************
we have 24statements
we have 0databases
There are 791 statement handles, of which 0 are active.
Was caching 0 statement handles

DBD::Oracle::db prepare failed: ORA-01000: maximum open cursors 
exceeded (a bunch of these)

******************************
we have 24statements
we have 0databases
There are 667 statement handles, of which 0 are active.
Was caching 0 statement handles

DBD::Oracle::db prepare failed: ORA-01000: maximum open cursors 
exceeded (a bunch more)

******************************
we have 24statements
we have 0databases
There are 543 statement handles, of which 0 are active.
Was caching 0 statement handles

I was able to build a dev setup on my Mac with updated versions of all 
modules. When I ran the same tests, I get the following:

******************************
we have 24statements
we have 0databases
There are 4 statement handles, of which 0 are active.
Was caching 4 statement handles
  got entities: 217
******************************
we have 24statements
we have 0databases
There are 5 statement handles, of which 0 are active.
Was caching 5 statement handles
  got entities: 217
******************************
we have 24statements
we have 0databases
There are 5 statement handles, of which 0 are active.
Was caching 5 statement handles
  got entities: 217
******************************

This tells me that something was fixed or changed somewhere along the 
way. With the new version of the modules, I changed RootClass => 
'Ima::DBI' to RootClass => 'DBIx::ContextualFetch' in my connect 
statement.

So it seems the $dbh I am passing (which is really an Ima::DBI, right?) 
in the older version treats each of the DBI-style queries as a new 
statement.

It seems the answer is to upgrade. I'll run the same tests after we 
upgrade our official Solaris dev server and verify that it goes away. 
I'll also try to change my connect to use 'connect_cached' as Perrin 
suggested.

Thanks for the help!
Jim


On Nov 12, 2004, at 7:40 AM, Tim Bunce wrote:

> And even if you do that doesn't destroy the cursor, just makes it
> inactive/idle/whatever. It still exists in Oracle ready to be executed 
> again.
>
> To find out how many statement handles exist for a $dbh use:
>
>   print "There are $dbh->{Kids} statement handles, of which 
> $dbh->{ActiveKids} are active.\n";
>
> Tim.
==========================================
Jim Brandt
Administrative Computing Services
University at Buffalo