RE: Interesting Locking Problem

Interesting indeed... I didn't see a version number in your note, but I am assuming that it is 8.1.7.x. We have also had some _very_ strange occurrences of 'library cache lock' / 'library cache pin' problems - sessions en-masse went into this state during high activity times on a Solaris based 8.1.7.3 Apps 11i DB supporting about 300 users. [One would have thought that lib cache pins/locks arose from voluntary/involuntary compiles of stored proc/packages caused by Duhlevelopers changing code on the sly]. We tightened up code changes, but this still occurred. We finally traced it down to a bug - one of the sessions obtains a 'row cache lock' for manipulating the dd cache for a specific table, and then 'hangs' and many other session queue behind this one with lib cache pins/locks on that same table. Oracle of course told us to upgrade to 8.1.7.4 - and we did after a long round of testing. And this occurred again yesterday - and Oracle is telling us to go 9.2.x as it was _really_ fixed in 9... Apparently this occurs when either a MV refresh on that object takes place, or a combination of TRUNCATEs (possibly on Partitioned tables) and INSERTs take place at the same time, during heavy load. Both these situations were likely in our case.

I think a ML search of certain keywords would generate a bug list of sufficient size!

John Kanagaraj
Oracle Applications DBA
DB Soft Inc
Work : (408) 970 7002

Listen to great, commercial-free christian music 24x7x365 at http://www.klove.com

• The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers **

>-----Original Message-----
>From: Jared.Still_at_radisys.com [mailto:Jared.Still_at_radisys.com]
>Sent: Thursday, July 31, 2003 4:59 PM
>To: Multiple recipients of list ORACLE-L
>Subject: Interesting Locking Problem
>
>
>List,
>
>Below is the description of a locking problem I ran into
>today. Any help
>to figure out what was happening here would be appreciated. Maybe
>I was overlooking something obvious, but I hope not. :)
>
>An MS Word version is available at:
>
>http://www.cybcon.com/~jkstill/CIM_enqueue_wait_2003_07_31.doc
>
>as it may be a little easier to read.
>
>----------------------
>
>
>I ran into a rather interesting problem today, and I am hoping that
>someone can help me determine what was happening here.
>
>The app owner informed me that there were some problems with
>our CIM app
>this morning.
>
>As this app sometimes has issues with locking, I first checked
<snip>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: John Kanagaraj
  INET: john.kanagaraj_at_hds.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).