| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: How to find the session holding the library cache pin.
this is from metalink
HOW TO FIND THE SESSION HOLDING A A LIBRARY CACHE LOCK
Common situations:
In the first situation the V$LOCK view will show that the session doing the
'ALTER TABLE' has an 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 in V$LOCK yet so in an environment with a lot of concurrent
sessions
the V$LOCK information is insufficient to track down the culprit blocking
your
operation.
METHOD 1: SYSTEMSTATE ANALYSIS
One way of finding the session blocking you is to analyze the system state
dump.
Using the systemstate event one can create a tracefile containing detailed
information on every Oracle process. This information includes all the
resources
held & requested by a specific process.
Whilst an operation is hanging, open a new session and launch the following statement:
ALTER SESSION SET EVENTS 'IMMEDIATE TRACE NAME SYSTEMSTATE LEVEL 8';
Oracle will now create a systemstate tracefile in your USER_DUMP_DEST
directory.
Get the PID (ProcessID) of the 'hanging' session from the V$PROCESS by
matching
PADDR from V$SESSION with 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 section with information for each
process. Open the tracefile and do a search for 'PROCESS
pid_from_select_stmt'.
In the process section look up the wait event by doing a search on 'waiting
for'.
Example output:
PROCESS 8:
Using the 'handle address' you can look up the process that is keeping a
lock
on your resource by doing a search on the address within the same tracefile.
Example output:
PROCESS 9:
<cut> ....
>From the output we can see that 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 was launched by this session is 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 only as SYS/INTERNAL) contains all the library object locks (both held & requested) for all sessions and is 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 up the session address (SADDR) in V$SESSION and doing the following select:
select * from x$kgllk where KGLLKSES = 'saddr_from_v$session'
This will show you all the library locks held by this session where KGLNAOBJ contains the first 80 characters of the name of the object. The value in KGLLKHDL corresponds with the 'handle address' of the object in METHOD 1.
You will see that at least one lock for the session has KGLLKREQ > 0 which means this is a REQUEST for a lock (thus, the session is waiting). If we now match the KGLLKHDL with the handles of other sessions in X$KGLLK that should give us the address of 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)
);
> dba_blockers > > this doesnt install by default. i cant remember which script runs it.check metalink.
> > warning... its a VERY slow view. > > > > From: Murali_Pavuloori/Claritas_at_claritas.com > > Date: 2003/08/28 Thu PM 04:14:26 EDT > > To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> > > Subject: How to find the session holding the library cache pin. > > > > > > Gurus: > > > > One of the developers has changed his java code and wants to load theclass
> > state.... > > > > I queried the v$session_wait and found that his session is waiting forthe
> > enqueue? > > > > Thanks in advance for your help. > > > > Murali. > > > > -- > > Please see the official ORACLE-L FAQ: http://www.orafaq.net > > -- > > Author: > > INET: Murali_Pavuloori/Claritas_at_claritas.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). > > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: <rgaffuri_at_cox.net > INET: rgaffuri_at_cox.net > > 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). >
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Oracle-L INET: orclbabu_at_hotmail.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-LReceived on Fri Aug 29 2003 - 17:24:31 CDT
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
 |
 |