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) <[email protected]>:
> 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: [email protected] [mailto:[email protected]]
> On Behalf Of Asif Momen
> Sent: Friday, March 13, 2009 6:44 AM
> To: Oracle Discussion List; [email protected]
> 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 <[email protected]> wrote:
>
> From: Ujang Jaenudin <[email protected]>
> Subject: session_cached_cursors
> To: "Oracle Discussion List" <[email protected]>
> 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
>
