Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: RE: shared pool latch

Re: RE: shared pool latch

From: <>
Date: Wed, 31 Mar 2004 14:57:38 -0500 (EST)
Message-ID: <>

when they were born(or hatched)? it just had to be said.


original message below

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

 From: Riyaj Shamsudeen <>  Date: 2004/03/31 Wed PM 12:15:42 EST
 Subject: RE: shared pool latch

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

 -----Original Message-----
 [] On Behalf Of Sultan Syed  Sent: Wednesday, March 31, 2004 4:21 AM  To: oracle
 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

 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.

Please see the official ORACLE-L FAQ:

To unsubscribe send email to: put 'unsubscribe' in the subject line.

Archives are at FAQ is at
Received on Wed Mar 31 2004 - 13:56:36 CST

Original text of this message