Re: session_cached_cursors

From: Ujang Jaenudin <ujang.jaenudin_at_gmail.com>
Date: Fri, 13 Mar 2009 08:55:12 +0700
Message-ID: <3edcb66e0903121855h2c1d8527lffdf081ea1ccbb21_at_mail.gmail.com>



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) <siyeon70_at_gmail.com>:

> 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: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 <ujang.jaenudin_at_gmail.com> wrote:
>
> From: Ujang Jaenudin <ujang.jaenudin_at_gmail.com>
> Subject: session_cached_cursors
> To: "Oracle Discussion List" <oracle-l_at_freelists.org>
> 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
Received on Thu Mar 12 2009 - 20:55:12 CDT

Original text of this message