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.
|
|
Re: Oracle cursors and purge
Tim Bunce 17:05 on 03 Nov 2004
|