Solution of Library Cache Lock

cache| solve last night's business system import data and rebuild the index when a session suddenly stalled, with Toad a look, has been waiting for the library Cache Lock. This lock is not visible in the TOAD or OEM, and the session starts every three seconds, but it waits for the lock every time. Obviously, this is related to the data dictionary, it should be an indexed data dictionary records are locked, resulting in the inability to rebuild. But to kill the other active session, the problem is still not resolved, it appears that some of the killed session hold the lock, and the session has not been rolled back completely, the process is still hanging. Now the problem is to find this conversation.
The first thought of the document is the Oracle9i Database reference, find Appendix A, as follows:

This event controls the concurrency between clients of the library cache. It acquires a lock on the object handle so either:

One client can prevent other clients from accessing the same object

The client can maintain a dependency for a long (for example, no other client can change the object)

This lock was also obtained to locate the "object in the" library cache.

Wait Time:3 seconds (1 second for Pmon)

Parameters:

Handle Address

Address of the object being loaded

Lock Address

Address of the load lock being used. This is isn't the same thing as a latch or a enqueue, it is a state Object.

Mode

Indicates the data pieces of the object which need to be loaded

Namespace

"Namespace"


Almost equal to nothing, but lock address should be of some use.
Instead of searching the internet for solutions, I finally found a document on the Metalink:
Doc ID:
note:122793.1
Subject:how to find the session HOLDING A LIBRARY CACHE LOCK
Type:bulletin
Status:published
Content Type:text/plain
Creation date:23-oct-2000
Last Revision date:17-jul-2002

Purpose
-------

In some situations it may happen your the session is ' hanging ' and are awaiting for
A ' Library cache lock '. This document describes the
In fact has the lock your are waiting for.


SCOPE & Application
-------------------

Support analysts, DBA ' s, ...


How do I find the session HOLDING a a LIBRARY CACHE LOCK
------------------------------------------------------

Common situations:

* A DML operation this is hanging because the table which be accessed is currently
Undergoing changes (ALTER TABLE). This may take quite a long time depending on
The size of the table and the type of the modification
(e.g. ALTER TABLE x MODIFY (col1 CHAR () on thousands of records).

* The compilation of package would hang on Library cache Lock and library cache Pin
If some users are executing any procedure/function defined in the same package.

In the the "the" situation the "V$lock view" the session doing the
' ALTER TABLE ' has a exclusive DML enqueue lock on the TABLE object (lmode=6,
Type=tm and ID1 is the object_id of the table. The waiting session however does
Not show up into v$lock yet so in a environment with a lot of concurrent
The V$lock information is insufficient to track down the culprit blocking
Operation.

Method 1:systemstate Analysis
------------------------------

One way of finding the session blocking the ' to analyze ' system State dump.
Using the SystemState event one can create a tracefile containing detailed
Information on every Oracle process. This information includes
Held & requested by a specific process.

Whilst an operation are hanging, open a new session and launch the following
Statement

ALTER session SET EVENTS ' IMMEDIATE TRACE NAME systemstate level 8 ';

Oracle would now create a systemstate tracefile in your user_dump_dest directory.
Get the PID (ProcessID) of the ' hanging ' sessions from the v$process by matching
Paddr from V$session and ADDR from v$process:

SELECT PID from V$process WHERE addr=
(SELECT paddr from v$session WHERE sid=sid_of_hanging_session);

The systemstate dump contains a separate with information for each
Process. Open the tracefile and do a search for ' PROCESS pid_from_select_stmt '.
The "process section" Look up the "wait event by doing a" search on ' waiting for '.

Example output:

PROCESS 8:
----------------------------------------
So:50050b08, Type:1, owner:0, flag:init/-/-/0x00
(process) Oracle pid=8, calls CUR/TOP:5007BF6C/5007BF6C, flag: (0)-
int error:0, call error:0, Sess error:0, txn error 0
(post info) Last Post received:82 0 4
Last Post RECEIVED-LOCATION:KSLPSR
Last process to post me:5004ff08 1 2
Last Post sent:0 0 13
Last Post sent-location:ksasnd
Last process posted by me:5004ff08 1 2
(latch info) wait_event=0 bits=0
Process Group:default, Pseudo PROC:50058AC4
O/S Info:user:daemon, TERM:PTS/1, ospid:15161
OSD PID info:15161
----------------------------------------
so:5005f294, Type:3, Owner:50050b08, flag:init/-/-/0x00
(session) trans:0, CREATOR:50050B08, flag: (a) usr/-bsy/-/-/-/-/-
did:0001-0008-00000002, Short-term did:0000-0000-00000000
Txn branch:0
Oct:6, prv:0, USER:41/LC
O/S Info:user:daemon, TERM:PTS/1, ospid:15160, Machine:goblin.forgotten.realms
Program:sqlplus@goblin.forgotten.realms (TNS v1-v3)
Application Name:sql*plus, hash value=3669949024
Waiting for ' library cache lock ' blocking sess=0x0 seq=253 wait_time=0
!>> handle address=5023ef9c, lock address=5019cad4, 10*mode+namespace=15

Using the ' handle address ', can look up the process, ' is keeping a lock
On your resource by doing a search on the address within the same tracefile.

Example output:

PROCESS 9:
----------------------------------------
So:50050e08, Type:1, owner:0, flag:init/-/-/0x00
(process) Oracle pid=9, calls CUR/TOP:5007BBAC/5007BBFC, flag: (0)-
int error:0, call error:0, Sess error:0, txn error 0

<cut> .....

----------------------------------------
So:5019d5e4, type:34, owner:5015f65c, flag:init/-/-/0x00
!>> LIBRARY OBJECT pin:pin=5019d5e4 handle=5023ef9c mode=x lock=0
User=5005fad4 session=5005fad4 count=1 mask=0511 savepoint=118218 flags=[00]

From the output we can and the Oracle process with PID 9 has an exclusive
Lock on the object we are trying to access. Using v$process and V$session we can
Retrieve the Sid,user,terminal,program,... for this process. The actual statement
That's launched by the, also listed in the Tracefile (statements and
Other library cache objects are preceded by ' name= ').


Method 2:examine the X$kgllk TABLE
-----------------------------------

The X$kgllk table (accessible as sys/internal) contains all
Library object Locks (both held & requested) for all sessions and
Are more complete than the V$lock view although the column names don ' t
Always reveal their meaning.

You can examine the locks requested (and held) by the waiting session
By looking the "session address" (SADDR) in V$session and doing the
Following select:

SELECT * from x$kgllk where kgllkses = ' saddr_from_v$session '

This'll show your all of the library locks held by that session where
Kglnaobj contains the name of the "the" the "the" object.
The value in KGLLKHDL corresponds with the ' handle '
Object in Method 1.

You'll be least one lock for the session has kgllkreq > 0
Which means this are a REQUEST for a lock (thus, the session is waiting).
If We are match the KGLLKHDL with the handles of sessions
X$kgllk that should give us to the blocking session since
Kgllkreq=0 for this session, meaning it has the lock.

SELECT * from X$kgllk lock_a
WHERE kgllkreq = 0
and EXISTS (SELECT lock_b.kgllkhdl from X$kgllk lock_b
WHERE kgllkses = ' saddr_from_v$session ' * BLOCKED session *
and LOCK_A.KGLLKHDL = LOCK_B.KGLLKHDL
and kgllkreq > 0);

If We look a bit further we can then again match kgllkses with SADDR
In V$session to find further information on the blocking session:

SELECT Sid,username,terminal,program from V$session
WHERE saddr in
(SELECT kgllkses from X$kgllk lock_a
WHERE kgllkreq = 0
and EXISTS (SELECT lock_b.kgllkhdl from X$kgllk lock_b
WHERE kgllkses = ' saddr_from_v$session ' * BLOCKED session *
and LOCK_A.KGLLKHDL = LOCK_B.KGLLKHDL
and Kgllkreq > 0)
);

In the same way we can also find all the blocked sessions:

SELECT Sid,username,terminal,program from V$session
WHERE saddr in
(SELECT kgllkses from X$kgllk lock_a
WHERE kgllkreq > 0
and EXISTS (SELECT lock_b.kgllkhdl from X$kgllk lock_b
WHERE kgllkses = ' saddr_from_v$session ' * BLOCKING session *
and LOCK_A.KGLLKHDL = LOCK_B.KGLLKHDL
and kgllkreq = 0)
);


RELATED DOCUMENTS
-----------------

[note:1020008.6] Script fully decoded locking script
[note:1054939.6] Compilation of PACKAGE is hanging on LIBRARY CACHE LOCK
.
The beginning of a long time to talk nonsense, the following gives two solutions, for event tracking or query x$kgllk table, or the latter easier. Search for half a day, finally asked Sysdna login method (low permissions, no password). The first time with x$ table, with sysdba hand still a little shiver (that is the official business system), typing command found a long time before the reaction, to find a SID, a careful look, incredibly is a background maintenance terminal, the program is Toad, state inactive. Ask the relevant personnel, never alter the table, it appears to be Toad look at the table structure or data when the problem, kill off finally normal, the index quickly completed. It's strange that inactive's conversations actually hold locks. The tool is too good, is also a double-edged sword ah.