Grokbase › Groups › Oracle › oracle-l › March 2009
FAQ
lists,

reading materials from both url, need clarification or does anyone has
a way how to prove it? is there event or oradebug for this case?

I'm confusing on which part of memory affected by
session_cached_cursors....SGA or PGA ?

http://www.freelists.org/post/oracle-l/SESSION-CACHED-CURSORS,6
http://www.dba-oracle.com/t_session_cached_cursors_optimal_size.htm

--
thanks and regards
ujang | oracle dba | mysql dba
http://ora62.wordpress.com
--
http://www.freelists.org/webpage/oracle-l

Search Discussions

  • Ujang Jaenudin at Mar 13, 2009 at 1:55 am ⇧
    siyeon,

    thanks for your mini lab :)

    on my exercise, got this:

    user session:

    SQL> create table a1 (id number);
    SQL> select count(*) from a1;
    SQL> insert into a1(1);
    SQL> insert into a1(2);
    SQL> insert into a1(3);
    SQL> insert into a1(4);
    SQL> commit;

    sys session:

    SQL> oradebug setospid 6208
    SQL> oradebug dump processstate 10

    select sql_id,SQL_TEXT,ADDRESS,HASH_VALUE from v$sqlarea where
    sql_text like '%from a1%';

    SQL_ID SQL_TEXT ADDRESS HASH_VALUE

    9yh0k0ktrf37m select count(*) from a1 0000000388b11c48 3010923763

    convert hash_value to hex, got this:
    3010923763 = b3770cf3

    SO: 38daf57b8, type: 53, owner: 4253c3b70, flag: INIT/-/-/0x00

    LIBRARY OBJECT LOCK: lock=38daf57b8 handle=388b11c48 mode=N
    ^^^^^^^^^
    call pin=0 session pin=0 hpc=0000 hlc=0000
    htl=38daf5838[38db51e40,38db58a30] htb=38db51e40 ssga=38db50df8
    user=4253c3b70 session=4253c3b70 count=1 flags=[0000] savepoint=0x49b9b635
    LIBRARY OBJECT HANDLE: handle=388b11c48 mtx=388b11d78(1) cdp=1
    ^^^^^^^^^
    name=select count(*) from a1
    hash=86ac6ca8038e9ffc9f401204b3770cf3 timestamp=03-13-2009 08:26:15
    ^^^^^^^^
    namespace=CRSR flags=RON/KGHP/TIM/PN0/SML/KST/DBN/MTX/[120100d0]
    kkkk-dddd-llll=0000-0001-0001 lock=N pin=0 latch#=4 hpc=0002 hlc=0002
    lwt=388b11cf0[388b11cf0,388b11cf0] ltm=388b11d00[388b11d00,388b11d00]
    pwt=388b11cb8[388b11cb8,388b11cb8] ptm=388b11cc8[388b11cc8,388b11cc8]
    ref=388b11d20[388b11d20,388b11d20] lnd=388b11d38[388b11d38,388b11d38]
    LIBRARY OBJECT: object=387bed580
    type=CRSR flags=EXS[0001] pflags=[0000] status=VALD load=0
    CHILDREN: size=16
    child# table reference handle
    ------ -------- --------- --------
    0 387bed048 387beccb8 388b11a20
    DATA BLOCKS:
    data# heap pointer status pins change whr
    ----- -------- -------- --------- ---- ------ ---
    0 388b11b88 387bed698 I/P/A/-/- 0 NONE 00
    ----------------------------------------

    I think that 86ac6ca8038e9ffc9f401204b3770cf3 is pointer at user
    process to 3010923763 hash_value in the library cache, am I correct??

    so, regarding the cursor is copied to "session_cached_cursor area",
    where is the actual area? SGA or PGA?

    my apologize.....cross posting to Indonesian oracle groups.

    --
    thanks and regards
    ujang | oracle dba | mysql dba
    http://ora62.wordpress.com

    2009/3/13 ±è½Ã¿¬(gmail) :
    Hi.



    I think that SESSION_CACHED_CURSORS are placed in Process memory in
    dedicated mode.



    Follow simple test>>



    Session1>

    SQL> select spid from v$process where addr=(select paddr from v$session
    where sid=(select sid from v$mystat where rownum=1));

    SPID

    ------------

    26178



    SQL> create table a1 (id number);

    SQL> select count(*) from a1;



    n SYS Connect and Process dump

    SQL> oradebug setospid 26178

    SQL> oradebug dump processstate 10

    SQL> oradebug tracefile_name

    /opt/oracle/admin/orcl/udump/orcl_ora_26178.trc

    SQL> !grep "from a1" /opt/oracle/admin/orcl/udump/orcl_ora_26178.trc

    --no result



    Session 1>

    SQL> select count(*) from a1;

    SQL> select count(*) from a1;



    n Process Dump After execute 3 times same cursors

    SQL> oradebug dump processstate 10

    SQL> !grep "from a1" /opt/oracle/admin/orcl/udump/orcl_ora_26178.trc

    name=select count(*) from a1





    AND, After execute 4 times same cursors "session cursor cache hits"
    performance statistics increase.



    Regards



    PS) sorry for my poor English skill.bb





    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Asif Momen
    Sent: Friday, March 13, 2009 6:44 AM
    To: Oracle Discussion List; ujang.jaenudin_at_gmail.com
    Subject: Re: session_cached_cursors



    Hi Ujang,

    SESSION_CACHED_CURSORS are placed in Shared_pool which is part of SGA.

    Have a look at:

    http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2588723819082

    Regards

    Asif Momen
    http://momendba.blogspot.com


    --- On Thu, 3/12/09, Ujang Jaenudin wrote:

    From: Ujang Jaenudin
    Subject: session_cached_cursors
    To: "Oracle Discussion List"
    Date: Thursday, March 12, 2009, 3:11 AM

    lists,



    reading materials from both url, need clarification or does anyone has

    a way how to prove it? is there event or oradebug for this case?



    I'm confusing on which part of memory

    affected by

    session_cached_cursors....SGA or PGA ?



    http://www.freelists.org/post/oracle-l/SESSION-CACHED-CURSORS,6

    http://www.dba-oracle.com/t_session_cached_cursors_optimal_size.htm





    --

    thanks and regards

    ujang | oracle dba | mysql dba

    http://ora62.wordpress.com
    --
    http://www.freelists.org/webpage/oracle-l
  • Dion Cho at Mar 13, 2009 at 4:29 am ⇧
    Session cursor caching does not mean that server process caches the whole
    SQL area in the PGA.
    It just means that Oracle does not release the cursor object when the same
    statement was executed more than 2 times in the same process.

    The key point here is that the cursor object is just the pointer to shared
    library cache object of cursor type.
    When the cursor object is cached in the PGA, the server process does not
    release the cursor even when the cursor was considered to be closed.

    To summarize.
    1. The server process caches the cursor when the same statement is executed
    more than 2 times.
    2. With cached cursor, Oracle does not release the cursor object and caches
    it in PGA.
    3. Cached cursor holds the pointer to the shared library cache object(which
    is located in SGA).
    4. The server process does not need to search the library cache chain to
    find matching statement just because the cursor is not closed. Hence, cursor
    sharing. This enables the fast and light soft parse.

    If you need concrete test case, let me know. I would post the result of
    simpe test case.

    Dion Cho - Oracle Performance Storyteller

    http://dioncho.wordpress.com (english)
    http://ukja.tistory.com (korean)

    2009/3/13 Ujang Jaenudin
    siyeon,

    thanks for your mini lab :)

    on my exercise, got this:


    user session:

    SQL> create table a1 (id number);
    SQL> select count(*) from a1;
    SQL> insert into a1(1);
    SQL> insert into a1(2);
    SQL> insert into a1(3);
    SQL> insert into a1(4);
    SQL> commit;


    sys session:

    SQL> oradebug setospid 6208
    SQL> oradebug dump processstate 10

    select sql_id,SQL_TEXT,ADDRESS,HASH_VALUE from v$sqlarea where
    sql_text like '%from a1%';

    SQL_ID SQL_TEXT ADDRESS HASH_VALUE
    ----------------------------------------------------------------------------
    9yh0k0ktrf37m select count(*) from a1 0000000388b11c48 3010923763

    convert hash_value to hex, got this:
    3010923763 = b3770cf3


    SO: 38daf57b8, type: 53, owner: 4253c3b70, flag: INIT/-/-/0x00
    LIBRARY OBJECT LOCK: lock=38daf57b8 handle=388b11c48 mode=N
    ^^^^^^^^^
    call pin=0 session pin=0 hpc=0000 hlc=0000
    htl=38daf5838[38db51e40,38db58a30] htb=38db51e40 ssga=38db50df8
    user=4253c3b70 session=4253c3b70 count=1 flags=[0000]
    savepoint=0x49b9b635
    LIBRARY OBJECT HANDLE: handle=388b11c48 mtx=388b11d78(1) cdp=1
    ^^^^^^^^^
    name=select count(*) from a1
    hash=86ac6ca8038e9ffc9f401204b3770cf3 timestamp=03-13-2009 08:26:15
    ^^^^^^^^
    namespace=CRSR flags=RON/KGHP/TIM/PN0/SML/KST/DBN/MTX/[120100d0]
    kkkk-dddd-llll=0000-0001-0001 lock=N pin=0 latch#=4 hpc=0002 hlc=0002
    lwt=388b11cf0[388b11cf0,388b11cf0] ltm=388b11d00[388b11d00,388b11d00]
    pwt=388b11cb8[388b11cb8,388b11cb8] ptm=388b11cc8[388b11cc8,388b11cc8]
    ref=388b11d20[388b11d20,388b11d20] lnd=388b11d38[388b11d38,388b11d38]
    LIBRARY OBJECT: object=387bed580
    type=CRSR flags=EXS[0001] pflags=[0000] status=VALD load=0
    CHILDREN: size=16
    child# table reference handle
    ------ -------- --------- --------
    0 387bed048 387beccb8 388b11a20
    DATA BLOCKS:
    data# heap pointer status pins change whr
    ----- -------- -------- --------- ---- ------ ---
    0 388b11b88 387bed698 I/P/A/-/- 0 NONE 00
    ----------------------------------------


    I think that 86ac6ca8038e9ffc9f401204b3770cf3 is pointer at user
    process to 3010923763 hash_value in the library cache, am I correct??

    so, regarding the cursor is copied to "session_cached_cursor area",
    where is the actual area? SGA or PGA?


    my apologize.....cross posting to Indonesian oracle groups.

    --
    thanks and regards
    ujang | oracle dba | mysql dba
    http://ora62.wordpress.com


    2009/3/13 ±è½Ã¿¬(gmail) :
    Hi.



    I think that SESSION_CACHED_CURSORS are placed in Process memory in
    dedicated mode.



    Follow simple test>>



    Session1>

    SQL> select spid from v$process where addr=(select paddr from v$session
    where sid=(select sid from v$mystat where rownum=1));

    SPID
    ------------

    26178



    SQL> create table a1 (id number);

    SQL> select count(*) from a1;



    n SYS Connect and Process dump

    SQL> oradebug setospid 26178

    SQL> oradebug dump processstate 10

    SQL> oradebug tracefile_name

    /opt/oracle/admin/orcl/udump/orcl_ora_26178.trc

    SQL> !grep "from a1" /opt/oracle/admin/orcl/udump/orcl_ora_26178.trc

    --no result



    Session 1>

    SQL> select count(*) from a1;

    SQL> select count(*) from a1;



    n Process Dump After execute 3 times same cursors

    SQL> oradebug dump processstate 10

    SQL> !grep "from a1" /opt/oracle/admin/orcl/udump/orcl_ora_26178.trc

    name=select count(*) from a1





    AND, After execute 4 times same cursors "session cursor cache hits"
    performance statistics increase.



    Regards



    PS) sorry for my poor English skill.bb





    From: oracle-l-bounce_at_freelists.org [mailto:
    [email protected]]
    On Behalf Of Asif Momen
    Sent: Friday, March 13, 2009 6:44 AM
    To: Oracle Discussion List; ujang.jaenudin_at_gmail.com
    Subject: Re: session_cached_cursors



    Hi Ujang,

    SESSION_CACHED_CURSORS are placed in Shared_pool which is part of SGA.

    Have a look at:

    http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2588723819082
    Regards

    Asif Momen
    http://momendba.blogspot.com


    --- On Thu, 3/12/09, Ujang Jaenudin wrote:

    From: Ujang Jaenudin
    Subject: session_cached_cursors
    To: "Oracle Discussion List"
    Date: Thursday, March 12, 2009, 3:11 AM

    lists,



    reading materials from both url, need clarification or does anyone has

    a way how to prove it? is there event or oradebug for this case?



    I'm confusing on which part of memory

    affected by

    session_cached_cursors....SGA or PGA ?



    http://www.freelists.org/post/oracle-l/SESSION-CACHED-CURSORS,6

    http://www.dba-oracle.com/t_session_cached_cursors_optimal_size.htm





    --

    thanks and regards

    ujang | oracle dba | mysql dba

    http://ora62.wordpress.com
    --
    http://www.freelists.org/webpage/oracle-l

    --
    http://www.freelists.org/webpage/oracle-l
  • Yong Huang at Mar 13, 2009 at 3:45 pm ⇧

    I'm confusing on which part of memory affected by
    session_cached_cursors....SGA or PGA ?
    Session cursor cache exists in UGA. It stores the addresses of child cursors that exist in shared pool. The idea is that once you have the addresses (pointers), you go straight to where the child cursors are without scanning shared pool. It also implies the child cursors are not supposed to be removed as easily as if you didn't have those pointers in session cursor cache.

    I did some research on this. See
    http://yong321.freeshell.org/oranotes/SessionCursorCache.txt

    Yong Huang

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedMar 12, '09 at 10:11a
activeMar 13, '09 at 3:45p
posts4
users3
websiteoracle.com

3 users in discussion

Ujang Jaenudin: 2 posts Yong Huang: 1 post Dion Cho: 1 post

Content

People

Support

Translate

site design / logo © 2023 Grokbase