session_cached_cursor

anyone know what is this parameter for? I cant understand very well from the docs... or probably I am pretty confused lately with the concepts of cursors

http://www.plauder-smilies.de/sad/conf.gif

Hi Pando,

Check out this link :

http://metalink.oracle.com/metalink/...T&p_id=32895.1

Basically, that parameter is a way to tell Oracle how many cursors to keep open per session. The higher you set it, the more memory each session will use, but if the sessions are likely to re-use the same SQL then you might see a performance increase by increasing this (if you have the spare memory, that is!)

Do you know if a shared cursor is in SGA or PGA?

Also from this paper it says

1. A cursor is an address on the client that points to the memory
location of a SQL statement on the server.
Multiple-client cursors may point at the same address on the server

In PL/SQL isnt cursor the private SQL area and not an ddress...?

Hi Pando,

For Question#1, I would think SGA or it couldn't be shared across sessions.

For Question #2, I'm not sure.

-John

yea the problem is from Oracle Docs it states that a cursor is in PGA! that's what confuses me (I also thought since it's shared it must be in SGA!)

hey jlordon

do you know what is the difference between querying v$open_cursor and v$sqltext for the last sql statement issued by an user...?

Hi Pando.

As I understand it -

V$OPEN_CURSOR only has cursors that have not been closed by the sessions.

V$SQL, V$SQLTEXT, etc has all SQL statements that are in the cache. (they might not be open any more)

So if somethign is in V$OPEN_CURSOR, you should be able to find it in V$SQL, but not always the other way around...

In V$OPEN_CURSOR, the rows disappear when the session disappears. Not true with V$SQL.

Also, if the same query has been executed by many sessions, it will have a row for each session in V$OPEN_CURSOR. In V$SQL, etc, it will have only one row.

-John

[Edited by jdorlon on 06-04-2001 at 04:36 PM]

well I dont understand why when I execute this query

SELECT r.username, q.piece, q.sql_text
FROM V$SQLTEXT q, V$SESSION r
WHERE q.hash_value=r.sql_hash_value
and r.username='XXXXX'
order by r.sid, piece
/

it differs from

SELECT user_name, sql_text
from v$open_cursor
where user_name='XXXXX'
/

Hi Pando -

There are a few reasons why it can be different -

1) The first query will only return one row per session.

2) The 2nd query might return many rows per session- all open cursors for each session.

3) I have just noticed that some things make it into v$sqltext
that don't make it into v$open_cursor - Anonymous PL/SQL blocks.

If you are after the current (or most recent) query by a session, use the first query. If you are after many of their recent queries, use the 2nd one.

-John

Hi Jdorlon

thanx for the explanation, I just did a small test with session_cached_cursor, I set it to 4 and bounced the database. According to the paper on Metalink if session_cached_cursor is set then no soft and hard parse would happen if the cursor is cached.

I executed a simple query

select * from emp;

then checked parse_calls in v$sqlarea it says 1, then I executed again it says 2 then again it increased to 3 does this means that this select is not cached?

I then tested another pl/sql block

declare
cursor crs is
select dname from dept;
name1 dept.dname%type;
begin
open crs;
loop
fetch crs into name1;
dbms_output.put_line(name1);
exit when crs%notfound;
end loop;
close crs;
end;
/

This happens the samething as above, everytime I execute it the parse_calls increases, I dont understand why since I am using a cached cursor (supposed to be)



Then I am reading about

CLOSE_CACHED_OPEN_CURSORS, it states that when a cursor is closed it´s not really closed, it is moved to a Least Recently Used (LRU) list of open cursors and left open. If this was the case then cursor is always cached no matter the value of session_cached_cursor no? Or if I set session_cached_cursor to 0 then CLOSE_CACHED_OPEN_CURSORS has no effect at all since no cursors are cached (i.e these two parameters are closely related)

Cheers

Hi Pando,

Your guesses are as good as mine at this point. I'd suggest
you open a tar at this point. let me know what they say if
you do...I'd like to know.

Are you executing this stuff from SQL Plus? I know that
when going through the OCI as a lot of apps (including EZSQL)
do, there are different ways to re-execute a query. Some
make the parse count go up and some don't. I don't know
what does on at a low level though. Then again, I don't
know if SQLPlus uses the OCI or some other interface either

-John

Hi I am running from SQL*PLUS since my knowledge on programming (therefore OCI) is limited

Regarding a TAR I will have to see coz I am changing company this Friday so I cant use my current support number :p

I don't know how relevent this information to this topic is, the way the oracle handles its open_cursor parameter is the count keeps increasing as long as the session is alive. Once you close that session, the count would get reset to the old value. The reson for that is every time you open a cursor, oracle allocates resources to that cursor. Once you are done with that cursor operation, oracle wouldn't release the resources, instead it would hold on to those resourese to the life time of the session. You might wonder why, it was b'cos the aquring the resources is one of the expensive operations. As a result if you keep acquiring and releasing the resources like memory and etc, it would degrade the performance, instead, when another cursor gets called, oracle would try to use the already acquired resources instead of acquiring new set, but again if the resources are insufficient, it would acquire from the system.

This is how the cursor manipulation goes on in the system level.

Hope I'm in relevence to the subject of discussion. :)

Sam

Hi Sambavan

I understand that concept and that's why I mentioned CLOSE_CACHED_OPEN_CURSORS and SESSION_CACHED_CURSOR should be related to each other, anyway if session_cached_cursor is used to cache the cursor I dont get it since from your theory the cursors are cached anyway, then if the cursor is cached why there is always soft parses with same queries? (If you check the link jdorlon mentioned you can see what I mean)

cheers