session_cached_cursor

[pando]

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

[jdorlon]

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!)

[pando]

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

[jdorlon]

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

[pando]

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!)

[pando]

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

[jdorlon]

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]

[pando]

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'
/

[jdorlon]

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

[pando]

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