RENAME and Library cache pin waits

From: Herring Dave - dherri <Dave.Herring_at_acxiom.com>
Date: Thu, 18 May 2006 09:45:07 -0500
Message-ID: <7ED53A68952D3B4C9540B4EFA5C76E3601C822CF@CWYMSX04.Corp.Acxiom.net>

I'm about to open an SR on a situation I've got with a RENAME hanging on event 'library cache pin', but thought I'd bounce it off all of you first, in case anyone's got any helpful suggestions on how to further investigate this or resolve it.

Here's the situation: We have an app that performs a RENAME at a certain point. There's a chance the table won't exist, so the statement should fail with an ORA-04043. I have no control over the app so I can't have the code check for existence of the table first or use ALTER TABLE ... RENAME instead. Anyway, what happens is the statement hangs on wait event 'library cache pin' for 5 minutes, then returns an ORA-04021. This is on 10.2.0.2 under Linux x86-64 RHEL4.

While the statement is hanging I've run the following in a separate session:

SELECT /*+ ORDERED */ w1.sid waiting_session

, h1.sid holding_session, w.kgllktype lock_or_pin, w.kgllkhdl address
, DECODE(h.kgllkmod, 0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive', 'Unknown') mode_held
, DECODE(w.kgllkreq, 0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive', 'Unknown') mode_requested
FROM dba_kgllock w, dba_kgllock h, v$session w1, v$session h1 WHERE ( ( h.kgllkmod != 0

             AND h.kgllkmod != 1 
             AND (   h.kgllkreq = 0 
                  OR h.kgllkreq = 1))
        AND (    (   w.kgllkmod = 0 
                  OR w.kgllkmod= 1) 
             AND (    w.kgllkreq != 0 
                  AND w.kgllkreq != 1)))
   AND w.kgllktype = h.kgllktype
   AND w.kgllkhdl = h.kgllkhdl

AND w.kgllkuse = w1.saddr
AND h.kgllkuse = h1.saddr;

... which shows that the RENAME is waiting on itself:

WAITING_SESSION HOLDING_SESSION LOCK ADDRESS MODE_HELD MODE_REQU

--------------- --------------- ---- ---------------- --------- ---------
            465             465 Pin  00000001FC7F7A30 Share     Exclusive

The ADDRESS listed above points to the table I'm trying to rename. I've got 2 databases on this server that exhibit the same behavior. I thought maybe it was an issue with 10.2.0.2, but I created a 3rd database and can't recreate the problem on it.

Any ideas of what else I could check?

Dave

Dave Herring, DBA
Acxiom Corporation
3333 Finley
Downers Grove, IL 60515
wk: 630.944.4762
<mailto:dherri_at_acxiom.com>

"When I come home from work and see those little noses pressed against the windowpane, then I know I am a success" - Paul Faulkner

The information contained in this communication is confidential, is intended only for the use of the recipient named above, and may be legally privileged.

If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited.

If you have received this communication in error, please resend this communication to the sender and delete the original message or any copy of it from your computer system.

Thank You.

--
http://www.freelists.org/webpage/oracle-l

Received on Thu May 18 2006 - 09:45:07 CDT