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