RE: RE: shared pool latch

  • From: Riyaj Shamsudeen <rshamsud@xxxxxxxxxxxx>
  • To: oracle-l@xxxxxxxxxxxxx
  • Date: Wed, 31 Mar 2004 14:17:27 -0600

>  what is the difference between a child cursor and a parent cursor? 

Every cursor (aka sql statements)  gets two cursors in the library
cache. One parent and one child cursor. This is the implementation for
cursor multi versioning.
 
Let us consider a case: A SQL statement was executed first time, then it
would get a parent and child. If the same sql statement was executed
again (with no change to the body of SQL keeping the same hash_value ),
with a different non-shareable attributes, then a new child cursor will
be added to this parent cursor. As you can see, you can have many child
cursors associated with a parent cursor. Non-shareable attributed
includes : such as different optimizer_mode, different session
parameters etc. 
 
Here is an example. 
 
Executed following SQL statements, First one with a different
optimizer_mode and what follows is the corresponding library cache dump.
As you can see there are two child cursors for the statement "select
sysdate from dual" and one child for the statement "select sysdate+1
from dual". Understandably +1 is converted to +"SYS_B0" due to
cursor_sharing testing I was doing..
 
Second statement "select sysdate from dual" is not shareable as the
optimizer_mode is different. But the second "select sysdate+1 from dual"
is shareable and so only one child. 
 
select sysdate from dual;
alter session set optimizer_mode=first_rows;
select sysdate from dual;
select sysdate+1 from dual;
select sysdate+1 from dual;
 
------------------------------------------------------------------------
------------
  LIBRARY OBJECT HANDLE: handle=385eb93f8
  name=select sysdate from dual
  hash=81b62de3 timestamp=03-31-2004 13:56:03
  namespace=CRSR flags=RON/TIM/PN0/SML/[12010000]
  kkkk-dddd-llll=0000-0001-0001 lock=0 pin=0 latch=0
  lwt=385eb9428[385eb9428,385eb9428] ltm=385eb9438[385eb9438,385eb9438]
  pwt=385eb9458[385eb9458,385eb9458] ptm=385eb94e8[385eb94e8,385eb94e8]
  ref=385eb9408[385eb9408,385eb9408] lnd=385eb9500[385eb9500,385eb9500]
    LIBRARY OBJECT: object=385eb9068
    type=CRSR flags=EXS[0001] pflags= [00] status=VALD load=0
    CHILDREN: size=16
    child#    table reference   handle
    ------ -------- --------- --------
         0 385ee0d38 385eb92b8 385ee0980
         1 385ee0d38 385eb92e8 385ebcb48
    DATA BLOCKS:
    data#     heap  pointer status pins change
    ----- -------- -------- ------ ---- ------
        0 385eb9338 385eb9158 I/-/A     0 NONE
 
 
------------------------------------------------------------------------
-------------
 LIBRARY OBJECT HANDLE: handle=385eaf4a8
  name=select sysdate+:"SYS_B_0" from dual
  hash=bf11a22e timestamp=03-31-2004 14:04:58
  namespace=CRSR flags=RON/TIM/PN0/SML/[12010000]
  kkkk-dddd-llll=0000-0001-0001 lock=0 pin=0 latch=1
  lwt=385eaf4d8[385eaf4d8,385eaf4d8] ltm=385eaf4e8[385eaf4e8,385eaf4e8]
  pwt=385eaf508[385eaf508,385eaf508] ptm=385eaf598[385eaf598,385eaf598]
  ref=385eaf4b8[385eaf4b8,385eaf4b8] lnd=385eaf5b0[385eaf5b0,385eaf5b0]
    LIBRARY OBJECT: object=385eaf1c0
    type=CRSR flags=EXS[0001] pflags= [00] status=VALD load=0
    CHILDREN: size=16
    child#    table reference   handle
    ------ -------- --------- --------
         0 385eaf0e0 385eaf410 385eae4d8
    DATA BLOCKS:
    data#     heap  pointer status pins change
    ----- -------- -------- ------ ---- ------
        0 385eb3678 385eaf2b0 I/-/A     0 NONE
------------------------------------------------------------------------
---------
HTH

Thanks
Riyaj "Re-yas" Shamsudeen
Certified Oracle DBA

-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of ryan.gaffuri@xxxxxxx
Sent: Wednesday, March 31, 2004 1:38 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: Re: RE: shared pool latch


Let me try to keep this alive.. Responses in line..
HTH
Thanks
Riyaj "Re-yas" Shamsudeen
Certified Oracle DBA

-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Sultan Syed
Sent: Wednesday, March 31, 2004 4:21 AM
To: oracle freelists.org
Subject: shared pool latch


Hi guys
The following questions are confusing me,please could someone give
explanation 
on the below
 
1)Is librarycache latch held during the whole process of parse,execute
and fetch phases ,
or it is held upto the time searching for the object in hash table and
release. 
 
Library cache latch is held while searching for a parent cursor and
subsequent child cursor with similar attributes with parent cursor
pinned. Also library cache latch is held when creating a new library
cache object if the child cursor is not_found or
found_but_not_shareable. Since, parent cursor is pinned while searching
for a child cursor, I am not sure whether the search for child cursor
needs to hold on to the library cache latch or not. But if a new parent
cursor is to be created then the library cache latch must be held.
 
Execute and Fetch uses library cache pin and library cache locks to
maintain the objects from undergoing concurrent modification.
 
2)session_cached_cursor caches the child cursor or parent cursors also. 
As far as I know, session_cached_cursor is searched for pinned child
cursor. I don't know whether the parent is also kept in the session
cached cursor or not. 
 
3)While doing hard parse which latch is acquired ,shared pool or library
cache latch or both,
if it is both which latch will be acquired first? 
 
library cache latch is already held while requesting for shared pool
latch. Shared pool latch is acquired to request/release space from the
shared pool free memory area and released immediately after that.
Request for library cache latch can never be made while holding the
shared pool latch (at least up to 9i) as latch level semantics will
prevent that ( as shared pool latch gets are always in willing-to-wait
mode).
 
Thanks in advance
Syed
 
 
 

The information transmitted is intended only for the person or entity to
which it is addressed and may contain confidential and/or privileged
material.  If the reader of this message is not the intended recipient,
you are hereby notified that your access is unauthorized, and any review,
dissemination, distribution or copying of this message including any
attachments is strictly prohibited.   If you are not the intended
recipient, please contact the sender and delete the material from any
computer.

Other related posts: