Re: session_cached_cursors
Date: Fri, 13 Mar 2009 13:29:48 +0900
Message-ID: <43c2e3d60903122129v370e1581obba9a78433a009ec_at_mail.gmail.com>
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 <ujang.jaenudin_at_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
>
>
>
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Mar 12 2009 - 23:29:48 CDT