session_cached_cursor
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: session_cached_cursor

  1. #1
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    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


  2. #2
    Join Date
    Nov 2000
    Posts
    344
    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!)

  3. #3
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    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...?

  4. #4
    Join Date
    Nov 2000
    Posts
    344
    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

  5. #5
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    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!)

  6. #6
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    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...?

  7. #7
    Join Date
    Nov 2000
    Posts
    344
    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]

  8. #8
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    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'
    /



  9. #9
    Join Date
    Nov 2000
    Posts
    344
    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


  10. #10
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width