Re: Closing Cursors

Peter L wrote:
>
> 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.

I have two suggestions:

1. Always use cursor FOR loops. They guarantee that the cursor will close as soon as the cursor goes out of scope. I know a lot of people think of Steve Feuerstein as a god in the pl/sql world but this is one that he got wrong because he does not understand the nature of how FOR loops work. The problem with the OPEN/FETCH/CLOSE scenario is that you must guarantee programatically that every path that can be taken will ultimately perform a close whereas if you use FOR loops the cursor is guaranteed to close no matter what else happens the moment that the cursor goes out of scope.
2. Create a shell script that will log sqlplus in as a DBA and then issue the following:

ALTER SYSTEM FLUSH SHARED_POOL; I know that I'll catch flak for this from purists, but I have found that in certain situations it is a sure cure for fixing problems like you are experiencing. The purists will say that you should never do this on a routine basis - however, there is nothing in any oracle documentation which gives any reason why it should not be done. The only penalty is that there is a slight performance hit when the cursors are re-parsed. But with a processor that has any decent speed you won't even notice this performance hit.

Here is the job I use:

#!/bin/ksh
ORACLE_SID="ORCL";export ORACLE_SID
ORACLE_HOME="/oracle/app/oracle/product/7.3.4" sqlplus -s system/manager <<-sqlEOF
set feedback off;
set pages 0;
set echo off;
whenever sqlerror exit failure;
alter system flush shared_pool;
exit success;
sqlEOF
if [[ $? -ne 0 ]];then

    print -u2 "sga flush failed";
    exit 1;
fi
exit 0;

Of course, you'll have to substitute your own SID and ORACLE_HOME to make it work right on your system. Received on Mon Feb 14 2000 - 07:01:31 CST