Re: Oracle cursors and purge

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

On Wed, Nov 03, 2004 at 09:45:49AM -0500, Jim Brandt wrote:
> I've got a web app using Class::DBI and I recently had an "edge case" 
> for a particular script that caused Oracle to start spewing:
> 
> ORA-01000 maximum open cursors exceeded
> 
> I investigated and found a few loops creating quite a few CDBI objects 
> because of the large number of records.
> 
> I experimented with the purge_object_index_every method, and setting 
> that to a lower value appeared to solve the problem. I think our Oracle 
> cursor setting is at 1000, so the script was going over.
> 
> My question is, would folks expect setting the purge this way would 
> solve the problem?

No.

> I just want to make sure it's actually doing what I think it's doing.
> 
> If this is the case, I can add some info to the Kwiki regarding the 
> open cursors error.

I'd guess the problem is due to Class::DBI defaulting to telling
Ima::DBI to use prepare_cached().

That's normally the right thing to do - unless you've got a very
large number of statements.

Try:	printf "Was caching %d statement handles\n", scalar keys %{$dbh->{CachedKids}};
	$dbh->{CachedKids} = undef;	# uncache prepare_cached sth's

That'll clear out the cache - but it'll grow again as prepare_cached is called.

An alternative is to alter some of your set_sql() calls (if you
have any) to pass a defined but false value for the $cache parameter.
See the Ima::DBI docs.

Tim.