Last night, the business system introduced the information and reconstructed the index. A session suddenly stagnated, using toad, I have been waiting for Library Cache Lock. You can't see this lock in Toad, OEM, and the session starts once every three seconds, but every time you wait this lock. Obviously, this is related to the data dictionary, it should be an indexed data dictionary to be locked, resulting in unreadable. But killing other Active sessions, the problem is still not resolved, it seems to have a lock that is killed, and the session has not rolled completely, the process is still hanging. The question now is to find this session.
The first thing that you think is
Oracle9i Database Reference, find Appendix A, explain the following:
THIS Event Controls The Concurrency Between Clients of The Library Cache. It acquires a Lock on The Object Handle So That Either:
One Client Can Prevent Other Clients from Accessing The Same Object
The Client Can Maintain A Dependency for A Long Time (for Example, No Other Client Chan Change The Object)
This Lock is also obtained to locate an 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 not the same thing as a latch or an enqueue, it is a State Object. mode Indicates the data pieces of the object which need to be loaded Namespace See "Namespace"
Almost equal nothing, but Lock Address should be a bit used. Turning the Internet search solution, finally found a document on Metalink: DOC ID: NOT: 122793.1SUBJECT: How to Find The session Holding a library cache lockty: BulletInstatus: Published Content Type: Text / PlainCreation Date: 23-OCT- 2000Last Revision Date: 17-JUL-2002
Purpose ------- in Some Situations It May Happen Your session is 'Hanging' and is awaiting for a 'library cache lock'. This document describes how to find the session That in Fact Has The Lock You are waiting for. Scope & Application ------------------- Support analysis, dba's, .. how to find the session Holding Aa 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 (eg 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 EXCL usive 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 Err OR: 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 PISPI: 15161 ----------------- ----------------------- SO: 5005F294, TYPE: 3, OWNER: 50050B08, FLAG: INIT / - / - / 0X00 (session) TRANS: 0 , Creator: 50050B08, FLAG: (41) 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' You CAN Look Up The Process That Is Keeping a Lock ON Your Resource by Doing a Search On The Address Wtem. 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
[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 do not 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 $ sessions and doing the following selection: select * from x $ kgllk where kgllkses = 'saddr_from_v $ session' this will show you all the library locks held by this session WHER e 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'
