Re: Closing Cursors

On Fri, 11 Feb 2000 14:43:50 -0500, K Stahl <BlueSax_at_Unforgettable.com> wrote:

>Peter L wrote:
>>
>> On Fri, 11 Feb 2000 13:42:06 -0500, K Stahl
>> <BlueSax_at_Unforgettable.com> wrote:
>>
>> >Peter L wrote:
>> >>
>> >> I seem to remember reading in this newsgroup about a parameter that
>> >> had to set to make sure that Oracle really closed cursors when you
>> >> told it to. The default action was to leave them open in case you
>> >> needed them again.
>> >>
>> >> Can anyoen supply details of this or have I dreamt it?
>> >
>> >If you are talking about Pro-C then what you need are the parameters
>> >RELEASE_CURSOR=YES and HOLD_CURSOR=NO
>> >
>> Is this just a Pro C problem? Can we just forget about it if we are
>> using PL/SQL?
>
>It really shouldn't cause any problems in PL/SQL. Can you describe a
>scenario where you think it would make a difference?
>

We have a number of jobs we are running on an 8i database. Basically they are PL/SQL procedures that open a cursor, on account records, and loop through each record. For each account record they open another cursor, to retrieve all the transaction details for the account, then loop through these transaction records to load a PL/SQL table. An interest calculation is then performed using the PL/SQL table.

These jobs start off processing about 2000 records a minute and run at this rate until they've processed about 200,000 records in 2 hours. Performance then dies and they drop to about 150 accounts per minute. The DBAs upped the size of the shared pool and this held back the problem to the 300,000 account mark. Rather than just keep upping the shared pool I was trying to think of things that could be filling it and how to eliminate them. The close_cached_open_cursors parameter that Thomas Kyte mentioned was one theory but any others are welcome given this shouldn't be a problem on 8i. Received on Fri Feb 11 2000 - 16:27:39 CST