Re: limitation of CURSORs

Wow that is a really good explanation - thanks. I tried and was able to submit a query via ADO "alter session set close_cached_open_cursors = true" in my application and it worked. But below it says that the behavior was changed in 8.1 so that my application will not work. Can I send the "Open_cursors" via a query from my application? I am worried about how my application runs against either 8.0 or 8.1 with one code body. My application has to support either version.

Thanks so much.

Tom

In article <37dcc088.80297701_at_newshost.us.oracle.com>,   tkyte_at_us.oracle.com wrote:
> A copy of this was sent to 12345tom_at_my-deja.com
> (if that email address didn't require changing)
> On Thu, 02 Sep 1999 17:18:18 GMT, you wrote:
>
> >Sorry, I do not understand.
> >
> >What happens in a web environment where you might run millions of
> >queries between reboots or bounces, yet you want cursors cached to
the
> >maximum extent possible.
>
> well the caching described below is on the session level. Typically
in the web
> environment -- the session lasts for the generation of a page and is
gone. so
> those cached OPEN cursors are gone with the session.
>
> there is also the shared_pool which caches compiled query plans. the
first time
> we parse a query after a cold start -- we cache the plan for that
query in the
> shared pool. subsequent parses of that same query will use the same
plan. the
> parse will happen much faster (but not as fast as if we just held the
cursor
> open in our session).
>
> below for example -- i have three cached open cursors in my session.
The first
> one took the longest to parse because it wasn't in the shared pool to
begin
> with. the second and third, since they weren't open during the first
execution,
> took lots less time to parse since we found and reused the query plan
in the
> shared_pool. On the second execute of the procedure IN THE SAME
SESSION, there
> was zero parse time for all three of the queries -- plsql had cached
those
> cursors. now, if i had opened lots and lots of cursors elsewhere in
that
> session -- these three might not still be cached but they probably
were.
>
> >Is it either a "cache none of them" or
> >"open_cursors = 10000000" situation? Cannot it be set to reclaim the
> >open cursors as it needs them?
> >
>
> yes it does this already. for some operations you need lots of
concurrent open
> cursors and they didn't have enough for that.
>
> the shared_pool is a big cache of cursor query plans for the entire
instance.
> the cursor cache below is on a session by session basis and yes it'll
reuse
> slots as needed. there are instances where, when using multiple
environments
> like C + Forms + stored procedures -- you can get into a bind because
each
> environment is somewhat unware of the other and in this case the only
solution
> is to up the limit or use something like close_cached_open_cursors.
>
> >Thank you.
> >
> >Tom
> >
> >
> >
> >In article <37b7d0cc.9623157_at_newshost.us.oracle.com>,
> > tkyte_at_us.oracle.com wrote:
> >> A copy of this was sent to Doug Cowles <dcowles_at_bigfoot.com>
> >> (if that email address didn't require changing)
> >> On Sat, 07 Aug 1999 21:23:19 -0400, you wrote:
> >>
> >> >It sounds to me like he's implying that the open_cursors is set
at a
> >> >reasonable
> >> >level, he just opened too many, and now what does he do... ??
> >> >
> >>
> >> reasonable is in the eye of the beholder. my concept of reasonable
> >for
> >> open_cursors is around 500-1,000... I run lots of plsql -- i use
> >1,000 myself.
> >>
> >> plsql will cache cursors by default. If you execute a procedure
that
> >runs 3
> >> selects -- you'll find that 3 cursors are open in your session.
For
> >example:
> >>
> >> SQL> create or replace procedure test_proc
> >> 2 as
> >> 3 begin
> >> 4 for x in ( select * from dual where 1=0 ) loop
> >> 5 null;
> >> 6 end loop;
> >> 7 for x in ( select * from dual where 1=1 ) loop
> >> 8 null;
> >> 9 end loop;
> >> 10 for x in ( select * from dual where 1=2 ) loop
> >> 11 null;
> >> 12 end loop;
> >> 13 end;
> >> 14 /
> >>
> >> Procedure created.
> >>
> >> SQL> select sql_text from v$open_cursor where sid = ( select sid
from
> >v$session
> >> 2 where audsid = userenv('sessionid'));
> >>
> >> SQL_TEXT
> >> ------------------------------------------------------------
> >> select sql_text from v$open_cursor where sid = ( select sid
> >>
> >> SQL> exec test_proc
> >> PL/SQL procedure successfully completed.
> >>
> >> SQL>
> >> SQL> select sql_text from v$open_cursor where sid = ( select sid
from
> >v$session
> >> 2 where audsid = userenv('sessionid'));
> >>
> >> SQL_TEXT
> >> ------------------------------------------------------------
> >> SELECT * FROM DUAL WHERE 1 = 0
> >> SELECT * FROM DUAL WHERE 1 = 2
> >> SELECT * FROM DUAL WHERE 1 = 1
> >> select sql_text from v$open_cursor where sid = ( select sid
> >>
> >> So, after running the procedure -- our cursors are still there even
> >though in
> >> PLSQL terms they are closed. plsql caches the cursors -- in the
even
> >we rerun
> >> the procedure they are already parsed and ready to go, just bind
and
> >execute
> >> them. The following shows that by default when we commit:
> >>
> >> SQL>
> >> SQL> commit;
> >>
> >> Commit complete.
> >>
> >> SQL>
> >> SQL> select sql_text from v$open_cursor where sid = ( select sid
from
> >v$session
> >> 2 where audsid = userenv('sessionid'));
> >>
> >> SQL_TEXT
> >> ------------------------------------------------------------
> >> SELECT * FROM DUAL WHERE 1 = 0
> >> SELECT * FROM DUAL WHERE 1 = 2
> >> SELECT * FROM DUAL WHERE 1 = 1
> >> select sql_text from v$open_cursor where sid = ( select sid
> >>
> >> they stay there. We can change this behaviour in 8.0 and before by
> >doing this:
> >>
> >> SQL>
> >> SQL> alter session set close_cached_open_cursors = true;
> >>
> >> Session altered.
> >>
> >> SQL> commit;
> >>
> >> Commit complete.
> >>
> >> SQL>
> >> SQL> select sql_text from v$open_cursor where sid = ( select sid
from
> >v$session
> >> 2 where audsid = userenv('sessionid'));
> >>
> >> SQL_TEXT
> >> ------------------------------------------------------------
> >> select sql_text from v$open_cursor where sid = ( select sid
> >>
> >> By setting close_cached_open_cursors -- the cursors plsql caches
are
> >closed when
> >> we commit. this can be done at the session level as above or at
the
> >system
> >> level permanently by setting it in the init.ora file.
> >>
> >> In 8.1, this parameter is obsoleted (close_cached_open_cursors) as
> >the algorithm
> >> for caching cursors has been changed. the system will flush and
> >reuse cursor
> >> entries as needed so you will just use open_cursors and
> >session_cached_cursors
> >> to control this behavior.
> >>
> >> >Johannes Wilhelm wrote:
> >> >
> >> >> Nandakumar <nanban_at_my-deja.com> wrote:
> >> >> > It looks like, i opened many dynamic cursors from within a
> >procedure
> >> >> > and didn't close them in the end. And now, i am at a level
> >> >> > where i can't open any more cursors. How could i close these
> >> >> > cursors so that further cursors can be opened.
> >> >>
> >> >> > There should be some DICT table that records the status of
> >cursors with
> >> >> > cursor ids, i guess.
> >> >>
> >>
> >> select * from v$open_cursor;
> >>
> >> >> > Would appreciate any solution to this probelm.
> >> >>
> >> >> > Thanks
> >> >>
> >> >> > In article <7o564l$bff$1_at_nnrp1.deja.com>,
> >> >> > Nandakumar <nanban_at_my-deja.com> wrote:
> >> >> >>
> >> >> >>
> >> >> >> When i try to drop a view, i get an error that says maximum
> >CURSOR
> >> >> > limit
> >> >> >> has exceeded.
> >> >> >>
> >> >> >> Anyone can give an idea on how this can be reset?
> >> >> >>
> >> >> >> drop view table1
> >> >> >> *
> >> >> >> ERROR at line 1:
> >> >> >> ORA-00604: error occurred at recursive SQL level 1
> >> >> >> ORA-01000: maximum open cursors exceeded
> >> >> >>
> >> >> >> Thanks
> >> >> >> --
> >> >> >> Nandakumar
> >> >> >> Systems Analyst
> >> >> >> New York
> >> >> >> (N.Kumar_at_rocketmail.com)
> >> >> >>
> >> >> >> Sent via Deja.com http://www.deja.com/
> >> >> >> Share what you know. Learn what you don't.
> >> >> >>
> >> >>
> >> >> > --
> >> >> > Nandakumar
> >> >> > Systems Analyst
> >> >> > New York
> >> >> > (N.Kumar_at_rocketmail.com)
> >> >>
> >> >> > Sent via Deja.com http://www.deja.com/
> >> >> > Share what you know. Learn what you don't.
> >> >>
> >> >> Add:
> >> >> open_cursors = #
> >> >> to the init.ora file with #>64 .
> >> >>
> >> >> Every access to a table or joined table lets an open cursor to
your
> >> >> session. If you need more than the default of 64 you can enlarge
> >the
> >> >> number without problems.
> >> >>
> >> >> Johannes
> >> >
> >> >
> >>
> >> --
> >> See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to
> >Oracle8i'...
> >> Current article is "Part I of V, Autonomous Transactions" updated
> >June 21'st
> >>
> >> Thomas Kyte tkyte_at_us.oracle.com
> >> Oracle Service Industries Reston, VA USA
> >>
> >> Opinions are mine and do not necessarily reflect those of Oracle
> >Corporation
> >>
> >
> >
> >Sent via Deja.com http://www.deja.com/
> >Share what you know. Learn what you don't.
>
> --
> See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to
Oracle8i'...
> Current article is "Part I of V, Autonomous Transactions" updated
June 21'st
>
> Thomas Kyte tkyte_at_us.oracle.com
> Oracle Service Industries Reston, VA USA
>
> Opinions are mine and do not necessarily reflect those of Oracle
Corporation
>

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Mon Sep 06 1999 - 23:15:04 CDT