Re: limitation of CURSORs

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. 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?

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. Received on Thu Sep 02 1999 - 12:18:18 CDT