Re: How to find the session holding the library cache pin.
run catblock.sql to create dba_blockers and dba_waiters @?\rdbms\admin\catblock.sql - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, August 29, 2003 01:54 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/[EMAIL PROTECTED] Date: 2003/08/28 Thu PM 04:14:26 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] 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 the class into the db. He did this on production db while users are accessing the application...and then complained that his session is just sitting in idle state I queried the v$session_wait and found that his session is waiting for the library cache pinquestion is how to tell which session is holding the enqueue? Thanks in advance for your help. Murali. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: Murali_Pavuloori/[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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] 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).
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: * a DML operation that is hanging because the table which is 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(200) on thousands of records). * The compilation of package will hang on Library Cache Lock and Library Cache Pin if some users are executing any Procedure/Function defined in the same package. 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: 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: (41) USR/- BSY/-/-/-/-/- DID: 0001-0008-0002, short-term DID: -- 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: [EMAIL PROTECTED] (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' 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: 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 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
How to find the session holding the library cache pin.
Gurus: One of the developers has changed his java code and wants to load the class into the db. He did this on production db while users are accessing the application...and then complained that his session is just sitting in idle state I queried the v$session_wait and found that his session is waiting for the library cache pinquestion is how to tell which session is holding the enqueue? Thanks in advance for your help. Murali. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: Murali_Pavuloori/[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).
Re: How to find the session holding the library cache pin.
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/[EMAIL PROTECTED] Date: 2003/08/28 Thu PM 04:14:26 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] 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 the class into the db. He did this on production db while users are accessing the application...and then complained that his session is just sitting in idle state I queried the v$session_wait and found that his session is waiting for the library cache pinquestion is how to tell which session is holding the enqueue? Thanks in advance for your help. Murali. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: Murali_Pavuloori/[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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] 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).
RE: How to find the session holding the library cache pin.
Murali, Running this should help in identifying the object being locked and the SID holding that pin. John Kanagaraj DB Soft Inc Phone: 408-970-7002 (W) Grace - Getting something we do NOT deserve Mercy - NOT getting something we DO deserve Click on 'http://www.needhim.org' for Grace and Mercy that is freely available! ** The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers ** column waiter format a15 column holder format a15 column held_object format a47 column lock_or_pin format a15 column address format a15 column mode_requested format a15 set feedback off set echo off select /*+ ORDERED */ w1.sid || '/' || w1.username waiter, h1.sid || '/' || h1.username holder, o.to_owner || '.' || o.to_name held_object, 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, v$object_dependen cy o 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 and w.kgllkhdl = o.to_address ; -Original Message- [mailto:Murali_Pavuloori/[EMAIL PROTECTED] Sent: Thursday, August 28, 2003 1:14 PM To: Multiple recipients of list ORACLE-L Gurus: One of the developers has changed his java code and wants to load the class into the db. He did this on production db while users are accessing the application...and then complained that his session is just sitting in idle state I queried the v$session_wait and found that his session is waiting for the library cache pinquestion is how to tell which session is holding the enqueue? Thanks in advance for your help. Murali. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: Murali_Pavuloori/[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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: John Kanagaraj 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).
RE: How to find the session holding the library cache pin.
$ORACLE_HOME/rdbms/admin/catblock.sql -- Mladen Gogala Oracle DBA -Original Message- [EMAIL PROTECTED] Sent: Thursday, August 28, 2003 4:24 PM To: Multiple recipients of list ORACLE-L 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/[EMAIL PROTECTED] Date: 2003/08/28 Thu PM 04:14:26 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] 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 the class into the db. He did this on production db while users are accessing the application...and then complained that his session is just sitting in idle state I queried the v$session_wait and found that his session is waiting for the library cache pinquestion is how to tell which session is holding the enqueue? Thanks in advance for your help. Murali. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: Murali_Pavuloori/[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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] 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). Note: This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. Wang Trading LLC and any of its subsidiaries each reserve the right to monitor all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala 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).
Re: How to find the session holding the library cache pin.
Take a look at Metalink note 122793.1, HOW TO FIND THE SESSION HOLDING A LIBRARY CACHE LOCK. At 12:14 PM 8/28/2003 -0800, Murali_Pavuloori/[EMAIL PROTECTED] wrote: Gurus: One of the developers has changed his java code and wants to load the class into the db. He did this on production db while users are accessing the application...and then complained that his session is just sitting in idle state I queried the v$session_wait and found that his session is waiting for the library cache pinquestion is how to tell which session is holding the enqueue? Thanks in advance for your help. Murali. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: Murali_Pavuloori/[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). Andy Rivenes Email: [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).
RE: How to find the session holding the library cache pin.
Murali: After identifying a LC pin wait in v$session_wait, use dba_lock_internal (it shows DDL/DML Locks, LC Locks, Latch Locks, etc) select to_char(SESSION_ID,'999') sid , substr(LOCK_TYPE,1,30) Type, substr(lock_id1,1,23) Object_Name, substr(mode_held,1,4) HELD, substr(mode_requested,1,4) REQ, lock_id2 Lock_addr from dba_lock_internal where mode_requested'None' and mode_requestedmode_held and session_id in ( select sid from v$session_wait where wait_time=0 and event like 'library cache pin%') ; You can also join v$session_wait.p1raw to x$kglpn.KGLPNHDL and v$session.saddr to x$kglpn.kglpnuse to get the sid of the session that's holding the pin. HTH Greetings Diego Cutrone Gurus: One of the developers has changed his java code and wants to load the class into the db. He did this on production db while users are accessing the application...and then complained that his session is just sitting in idle state I queried the v$session_wait and found that his session is waiting for the library cache pinquestion is how to tell which session is holding the enqueue? Thanks in advance for your help. Murali. Internet GRATIS es Yahoo! Conexión 4004-1010 desde Buenos Aires. Usuario: yahoo; contraseña: yahoo Más ciudades: http://conexion.yahoo.com.ar -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: =?iso-8859-1?q?Diego=20Cutrone?= 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).
RE: How to find the session holding the library cache pin.
Murali: After identifying a LC pin wait in v$session_wait, use dba_lock_internal (it shows DDL/DML Locks, LC Locks, Latch Locks, etc) select to_char(SESSION_ID,'999') sid , substr(LOCK_TYPE,1,30) Type, substr(lock_id1,1,23) Object_Name, substr(mode_held,1,4) HELD, substr(mode_requested,1,4) REQ, lock_id2 Lock_addr from dba_lock_internal where mode_requested'None' and mode_requestedmode_held and session_id in ( select sid from v$session_wait where wait_time=0 and event like 'library cache pin%') ; You can also join v$session_wait.p1raw to x$kglpn.KGLPNHDL and v$session.saddr to x$kglpn.kglpnuse to get the sid of the session that's holding the pin. HTH Greetings Diego Cutrone Gurus: One of the developers has changed his java code and wants to load the class into the db. He did this on production db while users are accessing the application...and then complained that his session is just sitting in idle state I queried the v$session_wait and found that his session is waiting for the library cache pinquestion is how to tell which session is holding the enqueue? Thanks in advance for your help. Murali. Internet GRATIS es Yahoo! Conexión 4004-1010 desde Buenos Aires. Usuario: yahoo; contraseña: yahoo Más ciudades: http://conexion.yahoo.com.ar -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: =?iso-8859-1?q?Diego=20Cutrone?= 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).
