From arivenes@llnl.gov Mon, 07 Jul 2003 12:31:46 -0700
From: Andy Rivenes <arivenes@llnl.gov>
Date: Mon, 07 Jul 2003 12:31:46 -0700
Subject: Re: strange library cache lock/ping wait event
Message-ID: <F001.005C3B3E.20030707112425@fatcity.com>
MIME-Version: 1.0
Content-Type: text/plain

You can try this script and see if it works for your situation. We had an 
elusive problem like this that we were able to gain more information about 
using this query:

--  FILE:   libcache_lock.sql
--
--  AUTHOR: Andy Rivenes
--
--  DATE:   01/22/2003
--
--  DESCRIPTION:
--          Query to display library cache lock/pin blockers and waiters
--          Source: Note: 122793.1, HOW TO FIND THE SESSION HOLDING
--                  A LIBRARY CACHE LOCK
--
--          The address of the object should allow access through
--          v$open_cursor, v$sql views.
--
--  REQUIREMENTS:
--          Access to x$ tables (connect as sys or sysdba).
--
--  MODIFICATIONS:
--
--
SET LINESIZE 132;
SET PAGESIZE 60;
SET TRIMSPOOL off;
--
COLUMN sid             HEADING 'SID'    	 FORMAT 9999;
COLUMN objtyp          HEADING 'Object|Type'     FORMAT A25;
COLUMN lktyp           HEADING 'Lock|Type'       FORMAT A4;
COLUMN lkmod           HEADING 'Mode|Held'       FORMAT A10;
COLUMN lkreq           HEADING 'Mode|Request'    FORMAT A10;
COLUMN objaddr         HEADING 'Address'         FORMAT A10;
COLUMN objdef          HEADING 'Object'          FORMAT A30   WORD_WRAPPED;
--
BREAK ON lk.kgllkhdl SKIP 1;
--
SELECT s.sid,
       DECODE(ob.kglhdnsp, 0, 'Cursor', 1, 'Table/Procedure/Type', 2, 'Body',
               3, 'trigger', 4, 'Index', 5, 'Cluster', 13, 'Java Source',
              14, 'Java Resource', 32, 'Java Data', TO_CHAR(ob.kglhdnsp)) 
objtyp,
       lk.kgllktype lktyp,
       DECODE(lk.kgllkmod, 0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive',
	      TO_CHAR(lk.kgllkmod)) lkmod,
       DECODE(lk.kgllkreq,  0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive',
              TO_CHAR(lk.kgllkreq)) lkreq,
       RAWTOHEX(lk.kgllkhdl) objaddr,
       DECODE(ob.kglnaown, NULL, '', ob.kglnaown || '.') || ob.kglnaobj ||
       DECODE(ob.kglnadlk, NULL, '', '@' || ob.kglnadlk) objdef
  FROM v$session s,
       x$kglob ob,
       -- dba_kgllock lk
       ( SELECT kgllkuse, kgllkhdl, kgllkmod, kgllkreq, 'Lock' kgllktype 
FROM x$kgllk
          UNION ALL
         SELECT kglpnuse, kglpnhdl, kglpnmod, kglpnreq, 'Pin'  kgllktype 
FROM x$kglpn ) lk
 WHERE lk.kgllkhdl = ob.kglhdadr
   AND lk.kgllkuse = s.saddr
   AND lk.kgllkhdl IN ( SELECT DISTINCT kgllkhdl
                          FROM ( SELECT kgllkhdl, kgllkreq FROM x$kgllk
                                  UNION ALL
                                 SELECT kglpnhdl, kglpnreq FROM x$kglpn )
                         WHERE kgllkreq > 0 )
 ORDER BY lk.kgllkhdl,
          lk.kgllkreq ASC,
          lk.kgllkmod DESC
/

Andy Rivenes
[EMAIL PROTECTED]
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Andy Rivenes
 INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).