Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Aruna.

Asked: July 05, 2010 - 2:38 pm UTC

Last updated: November 08, 2019 - 5:03 am UTC

Version: 11g

Viewed 1000+ times

You Asked

Hi Tom,

The Oracle users in the world should be thankful to you for all your help all the way .Many thanks for that. We are learning many things about Oracle that we could not learn by reading several Oracle books (other than your books)
though the questions look lengthy - I hope you could clarify my doubts with in 3-5 minutes - many many thanks !!
I have 5 questions (sorry for this but I wont ask again about this topic) - Can you please clarify ? Your clarifications for these 5 questions will help me a lot and gives me clear concept completely about cursors.
I request for your time and help


1.Ref_cursor can not be cached by PL/SQL - (in one of your book 'Effective Oracle design' - you mentioned that - it is because Pl/SQL does not know at compile time which SQL can be attached , I think the same issue with Session_cached_curosrs also - the ref_cursor can not be even session_cached_cursor - Correct ??

2.In GENERAL - there MAY be 3 types of cursors in PGA of a session
a. PL/SQL cache open cursors (They are OPEN in Oracle point of view)
b.Session Cache Cursor (If the Client closes the cursor and opens at least 3 times - We keep a Soft pointer to the SQL in the share pool - 'softer soft parse ' you say)
c.Currently OPEN cursors -
V$OPEN_CURSOR - What does this view show among the above 3 type of cursors ? Does it show Pl/SQL cache,Session_cached_cursors and OPEN cursors currently (I mean all the 3 types) ?How to find only truly OPEN cursors at the moment in the system ?



3.I know that the SQL pointed by the session_cached_cursor can be 'flushed out' if sapce needed for some other SQL- Is it the same with PL/SQL Cache also ? Is it the same with currently OPEN cursor (let say a JAVA cursor) ? If a java cursor is OPEN now and is there possibility that SQL STILL can be 'flushed' out if necessary to give space to other SQL by Oracle ? Does this 'flushing' follow LRU algorithm like Data buffer cache (OR) Is the SQL pinned in the shared pool until the cursor closed?


4.In the Oracle 11g R2 documentation-" Database PL/SQL Language Reference"- in the chapter "6 PL/SQL Static SQL" - It was mentioned that
"The cursors that this chapter explains are session cursors. A session cursor lives in session memory until the session ends, when it ceases to exist. Session cursors are different from the cursors in the private SQL area of the program global area (PGA), explained in Oracle Database Concepts <../../server.112/e10713/memory.htm> .A session cursor that is constructed and managed by PL/SQL is called an implicit cursor. A session cursor that you construct and manage is called an explicit cursor"
I don't understand how the session cursors are different from ' the cursors in private SQL area of the PGA'- I want to know what are actually the 'cursors in the pivate sql area of the PGA' - Can you please explain the difference between them ?

5.Oracle raises Error "'ORA-01000: maximum open cursors exceeded" when the 'current open_cursors'+'session cached cursors' >'open_cursors' parameter - correct ?


Most of the times - once we connect to database - we open and close a cursor Only ONCE otherwise we may not see the 'consistent data' if we open/close - so PL/SQL cache or Session_cached_cursors really don't have much to do with the cursors in WELL-BEHAVED application - is this my understanding correct ?

and Tom said...

1) it is because it is a pointer to a cursor, and a pointer that could point to many different cursors over time.

for example:

if ( x = 1 ) then open c for select * from dual;
else open c for select * from all_users;


If we 'cached' C open the first time around, it would tend to point to the same query every time, even if (x=1) changes its boolean result.

Additionally, you could have a function:

create function foo return sys_refcursor
as
  l_c sys_refcursor;
begin
  open l_c for select * from t;
  return l_c;
end;


and one could call foo like this:

begin
  l_cursor1 := foo;
  l_cursor2 := foo;
end;


If foo cached l_c, there would be one occurrence - in general there needs to be "many" and each one in turn would point to a completely different result set. So, in short, a ref cursor is just a pointer to some arbitrary (not really arbitrary, you choose) query. It many have multiple open occurrences and each may be a completely different query.


2) you would have to define "truly open cursors". I define that as (a) and (b). That is what v$open_cursor in general will show you.


3) the plsql cursor cache works for the client mostly. Say you have open cursors set to 100. and suppose you have session cached cursors set to 50. So, plsql will cache up to 50 open cursors for you in plsql.

Now, suppose your client application has 50 cursors open and plsql has cached 50 cursors as well. You now have 100 cursors open. If your client code attempts to open one more cursor - what will happen?

what happens is that plsql will silently close one of its cursors so that you may open yours. Your client will have 51 cursors open and plsql will have 49. Plsql will keep doing that until your client has 100 cursors open (and plsql will have none) before you receive an error regarding max open cursors exceeded.

the sql area representation of what plsql points to is subject to flushing just as any open cursor is. For example, if plsql has a query "select * from t" open AND you create a new index on T, the sql for T will be invalidated in the cache - and plsql will implicitly cause that sql to be recompiled again if and when you execute it.


4) the quote in question is here:

http://docs.oracle.com/cd/E11882_01/appdev.112/e10472/static.htm#LNPLS00602

it is better to read in context, it makes more sense in context. They are calling PLSQL cursors 'session cursors', it is the plsql managed bit they are referring to. PLSQL is a layer, a programming layer and it manages its own cache of cursors.

The client code would have its concept of a session cursor as well - if you open a cursor in the client, it really just points to a cursor area in the SGA/PGA on the server side. PLSQL is the client in this cases.


5) no, it would raise that error when your client attempts to open more than open_cursors number of cursors (see #3 above)








I did not understand your last paragraph - you would need to restate that somehow.


Rating

  (11 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, July 06, 2010 - 10:05 am UTC

Thanks tom for your response - many thanks

1.I understood that as ref_cursor is a pointer - pl/sql can not cache it (from your nice book -effective oracle by design' - my question was - Can' it be session_cached_cursor also?

2. truly open cursor means - a cursor that is opened for the first time and is still open (I believe that can not be a) pl/sql cache and b) session_cached cursor.
How to get count of those cursors ?

3. ok, I have understood that pl/sql will give slot if required - ok, what about session_cached_cursors ?
Ex: open_cursors=100 and now session_cached_cursors=100 (which were opened/closed at least 3 times) Now when the client tries to open one more cursor - will it throw the error 'ORA-01000: maximum open cursors exceeded ?

4.Sorry sir , Can you please explain this in some other words - I would not able to understand the difference - sorry for that .

5.What I meant to say was - 'Pl/sql cache and session_cached_cursors' are useful when we open/close the cursors many times
But it is not 'good thing' that opening and closing the same cursor in the same session ' correct ? You mentioned in 'effective oracle by design' that 'an ill-behaved application closes and opens a cursor multiple times in the same session When discussing the session_cached_cursorss.

Once again thanks



Tom Kyte
July 06, 2010 - 4:41 pm UTC

1) before 11g, native dynamic sql can be session cached so

open c for 'select ...'

will session cache.

but static sql will not, so

open c for select ...

will not session cache, in 11g BOTH session cache.


2) the client will be able to open 100 cursors, plsql will give up all of theirs. The 100/50 example I spelled out explains that, just change my 50 to 100 and the story would be the say. the client can open 100 cursor, plsql will close all of theirs if needed.

4) session cursor = client cursor. plsql is the client. Pretend plsql was java or visual basic. they have "session cursors" outside of the database.

plsql is just like that, but it runs "in" the database.

5) ... But it is not 'good thing' that opening and closing the same cursor in the same
session ' correct ?...

it is if you don't actually OPEN IT!!!

In plsql, it is typically 100% safe to open and close a cursor 1,000,000 times - SINCE plsql caches it. Plsql silently hides this from you (one of the major reasons to put all sql in plsql by the way!!).

re-read that chapter, it slides right into a section entitled "Use PL/SQL to Reduce Parses" which starts with:

By far, the easiest way to reduce the parses is to simply use PL/SQL. As demonstrated in the previous section, all static SQL found in PL/SQL is cached (kept open) for you. That way, the only places you need to worry about reducing the parsing would be calls to stored procedures. Instead of your application having hundreds of SQL statements that you would need to cache yourself, you can have instead just tens of stored procedure calls to handle. Each stored procedure would have many SQL statements in it, giving you a reduced number of PL/SQL statements in your application




I said you need to use a cursor cache of some sort

A reader, July 06, 2010 - 5:11 pm UTC

Sir,

1.Bind Variables ----? to send the 'SAME' Sql to the database so that the database can do a 'soft' parse instead of hard parse, when programmer uses the bind variables the client sends the same SAME sql to the database,
See below a loop in a 3GL (other than pl/sql)

for (i=1,i<10000,i++)
loop
select x into y from emp where emp_id=:i;
end;

here the client opens - ONLY ONE CURSOR for the first iteration and for the second iteration it understands that it is the same SQL (same CURSOR) and uses the same cursor -
DOES NOT MAKE A PARSE CALL....

is it correct understanding of 'bind variables' ?

2.how are the SQL/plans flushed from shared pool ?is it similar to the data buffer cache (LRU algorithm) - Can be the SQLs pointed by pl/sql cache and session_cache also be flushed from the shared pool if space is needed for any other SQL ??
Tom Kyte
July 06, 2010 - 6:38 pm UTC

1) in your example, it is not clear that the programmer opens one cursor. In pro*c it would be more like:

exec sql open c for select .... ;

for ( i = 1; i < 10000; i++ )
{
exec sql execute c using :i;
}

exec sql close c;


2) it is an LRU with caveats. Some sql cannot be flushed because it is pinned by something. sql pointed to by anyone can be flushed from the shared pool due to invalidation of course. If you open:

select * from t;

and execute it - and leave it open, but someone issues:

create index i on t(x);

then the sql plan in the shared pool will be invalidated - needs to be recomputed - and during your next execute, we will implicitly compile it for you - nothing you need to do, just happens.

A reader, July 08, 2010 - 2:58 pm UTC

Hi Tom,
Sorry to trouble you -
Finally two question about these cursors

1. v$open_cursors is a view which shows all pl/sql cached cursors,session_cached_cursors and the curosrs which are open right now ,,,,basically it shows all the 3 types of cursors ... but I want to run a report which DOES NOT SHOW pl/sql cached and session_cached_cursors , it should contain only the cursors which are open right now in the database ? how can I achieve this ? DO i need to depend on v$session and v$mysysstat??

2.Oracle will say -'Max cursors exceeded' - when ?
when the pl/sql cache SHRINK to zero and session_cached_cusors+currently open_cursors > open_cursors (parameter) - Is this correct ?

My question is - is the count of session_cached_curosrs wil be taken into account when raising the error ?

Many thanks


Tom Kyte
July 08, 2010 - 4:22 pm UTC

1) it doesn't have to show session cached cursors

we don't differentiate in the database level between "plsql cached" and otherwise, I don't know of any way to differentiate them at that level, that is information plsql has - it isn't really in the server, it is in your session.

2) when you exceed max open cursors :) plsql will give up everything it can before it happens. session cached cursors are not open - so they never counted against you.


thanks

A reader, July 08, 2010 - 4:44 pm UTC

many thanks Tom, its cleared now. thanks a lot .

I have doubt - whenver I ask a dount quoting a paragraph from concepts guide ( a big big documentation), you quickly get the same link/info - how is it possible ? do you know every paragraph in concepts guide where it is ?


Tom Kyte
July 08, 2010 - 6:24 pm UTC

A reader, July 09, 2010 - 9:48 am UTC

Hi Tom,

"1) it doesn't have to show session cached cursors "

you said above in one of the follow-ups .

I am reading your book 'Expert one-on-one' (PDF version)
in the chapter - 'tuning strategies and tool'

V$OPEN_CURSOR
This view contains a list of every open cursor for all sessions. This is very useful for tracking down
'cursor leaks' and to see what SQL your session has been executing. Oracle will cache cursors even after
you explicitly close them so don't be surprised to see cursors in there that you thought you closed (you
might have). For example, in the same SQL*PLUS session I was using to compute the Soft Parse.....



Which is correct ? does the V$OPEN_CURSOR show the session_cached cursors or not ??
Tom Kyte
July 09, 2010 - 10:48 am UTC

you don't have to see session cached cursors in there - Oracle will cache cursors even after you close them (think plsql).

A reader, July 09, 2010 - 11:13 am UTC

thanks Tom-
you said-
"Oracle will cache cursors even after you close them (think plsql)"


then it should not be 'Oracle' - it is client (pl/sql or may be java etc) which cached the closed cursors but keep 'open' at database level.


So fianlly - v$open_cursors will show - pl/sql(any clent) cache (as these are open at database) and the 'open' cursors , it does not show session_cached_cursors as they are closed .

correct ?

Tom Kyte
July 09, 2010 - 4:56 pm UTC

plsql is oracle.

plsql can be thought of as a client, but plsql is fundamentally "Oracle"


the plsql cache is unique amongst all 'client' caches in that it uses session_cached_cursors as a bound AND it silently 'really' closes a cursor when cursor space is pressed - there are no other 'clients' that can do that and plsql can only do that because plsql is in fact 'Oracle'


A reader, July 09, 2010 - 5:14 pm UTC

excellent point that plsql is 'oracle' - many thanks Tom

So fianlly - v$open_cursors will show - pl/sql(ANY CLIENT) cache (as these are open at database) and
the 'open' cursors , it does not show session_cached_cursors as they are closed .

is this correct ?

Tom Kyte
July 09, 2010 - 7:07 pm UTC

it does not have to show session cache cursors... they can be there

A reader, July 09, 2010 - 7:10 pm UTC

"it does not have to show session cache cursors... they can be there"


Sorry , my poor mind is still not able to understand this..

what does meant by ' they can be there ' ?

happy week-end .


Tom Kyte
July 19, 2010 - 7:46 am UTC

can - might, could be, not will be. they are session cached, not open.

cursors in PL/SQL

Sridhar, January 21, 2014 - 12:04 pm UTC

Hi Tom,

In the current project I am working, there is one lookup table that looks something like this:

KEY VARCHAR2(100)
GA1 VARCHAR2(100)
GA2 VARCHAR2(100)
GA3 VARCHAR2(100)
GA4 VARCHAR2(100)

For each key value, they might choose to have values in either 1 or 2 GA columns.

Inside a pl/sql block, we are supposed to fetch these GA values based on the KEY value.
What I suggested was write one cursor :

CURSOR c1(pkey VARCHAR2)
is
select ga1, ga2, ga3, ga4
from lookup_table
where key = pkey;

and inside the begin block, we could do something like this:

IF c1%ISOPEN then
close c1;
end if;
open c1('VALUE1');
fetch c1 into vga1, vga2, vga3, vga4;
close c1;

IF c1%ISOPEN then
close c1;
end if;
open c1('VALUE2');
fetch c1 into vga1, vga2, vga3, vga4;
close c1;


But my counterparts are insisting that I write individual select statements hardcoding the KEY value, that looks something like this:
select ga1, ga4
into vga1, vga4
from lookup_table
where key = 'VALUE1';

They argue that if at all they add a new GA columns to the table, need to fetch those newly added GA columns, they need to change in all occurences of the cursor, whereas in their approach, they just need to change only some particular select statements.
Which would be a better way to approach this problem?

cursor confusion

Boris, November 01, 2019 - 6:06 pm UTC

PL/SQL documentation states that
Managing Cursors in PL/SQL
PL/SQL uses implicit and explicit cursors. PL/SQL declares a cursor implicitly for all SQL data manipulation statements, including queries that return only one row. If you want precise control over query processing, you can declare an explicit cursor in the declarative part of any PL/SQL block, subprogram, or package. You must declare an explicit cursor for queries that return more than one row.

https://docs.oracle.com/cd/B19306_01/appdev.102/b14261/sqloperations.htm#BABHICAF

Now my query is

I hope we can process the queries that return more than one row using the implicit cursor( cursor for loop implicit ).

Could you please shed some lights on this?

Thanks a lot for your service to oracle community.
Chris Saxon
November 04, 2019 - 1:29 pm UTC

You're right, you can use implicit cursors to return many rows!

Either via a cursor for loop.

Or select bulk collect into.

Remember: that doc is over 10 years old!

cursor confusion

Boris, November 08, 2019 - 1:54 am UTC

Thanks a lot for your time in answering my queries.
Connor McDonald
November 08, 2019 - 5:03 am UTC

glad we could help