Re: limitation of CURSORs

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 Received on Thu Sep 02 1999 - 13:29:24 CDT