"Translated from MoS article" Find a way to hold the library cache lock session

Find a way to hold the library cache lock session

Reference from:
How to Find which Session is Holding a particular Library Cache Lock (document ID 122793.1)

In fact, there are two ways:

I.. systemstate analysis

Translation is not done here, reproduced in the original

SystemState event would create a tracefile containing detailed information on every Oracle process. This information includes all the resources held & requested by a specific process.

While an operation is hanging, open a new session and launch the following statement:

For Oracle 9.2.0.1 or higher:

$sqlplus '/As Sysdba ' Oradebug setmypid oradebug unlimit oradebug dump systemstate 266

For older versions, you can use the following syntax that's also possible in higher versions. The level 266 are not available before 9.2.0.6

Alter session set max_dump_file_size=unlimited; Alter session SET Events ' immediate trace name systemstate level 10 '

Oracle would create a systemstate tracefile in your user_dump_dest directory.

Get the PID (ProcessID) of the ' Hanging ' session:

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 above>".-----> First position the PID (non-ospid) corresponding to the waiting session
In the process section, search for the wait event by doing a search on ' waiting for '. -----> re-position with ' waiting for ', note the handle address of the next line of ' waiting for ' and then search with that address

PROCESS:----------------------------------------so:0x7d2bd820, Type:2, Owner: (Nil), flag:init/-/-/0x00 (proces  s) Oracle pid=20, calls CUR/TOP:0X7D3D62D0/0X7D3D85DC, flag: (0)-int error:0, call error:0, Sess error:0, TXN error 0 (Post info) Last Post received:109 0 4 Last Post RECEIVED-LOCATION:KSLPSR last process to post me:7d2b8d94 1 6 l  AST Post sent:0 0 Last Post sent-location:ksasnd last process posted by me:7d2b8d94 1 6 (latch info) wait_event=0  Bits=0 Process Group:default, pseudo PROC:0X7D2ED5DC o/s info:user:oracle, TERM:PTS/7, ospid:19759 OSD pid info: Unix process pid:19759, Image:goblin.forgotten.realms (TNS v1-v3) <cut> (session) sid:141 Trans: (nil), Creato r:0x7d2bd820, Flag: (+) usr/-bsy/-/-/-/-/-did:0001-0014-00000668, short-term did:0000-0000-00000000 TXN branch: (n IL) oct:6, prv:0, Sql:0x62d01c34, psql:0x7c20f24c, User:542/scott service name:sys$users O/S info:user:oracle, TERM:PTS/7, ospid:19758, Machine:goblin.forgotten.realms program: [email protected] (TNS v1-v3) application Name:sql*plus, hash value=3 669949024 waiting for ' library cache lock ' blocking sess=0x (nil) seq=36 wait_time=0 seconds since wait started=11 handle ADDRESS=62D064DC, lock Address=79f88a68, 100*MODE+NAMESPACE=C9

• Use the handle address to find information on the object locked:

  So:0x79f88a68, type:53, owner:0x7d3d62d0, flag:init/-/-/0x00 LIBRARY OBJECT lock:lock=79f88a68 handle=62d064dc reques T=s----> Note here call  pin= (nil) session pin= (nil) hpc=0000 hlc=0000 Htl=0x79f88ab4[0x79e71e60,0x79e71e60] htb= 0x79e71e60 ssga=0x79e716fc user=7d3a13b8 session=7d3a13b8 count=0 flags=[0000] savepoint=0xce LIBRARY OBJECT HANDLE: HANDLE=62D064DC mtx=0x62d06590 (0) Cdp=0name=scott. EMPLOYEES----> Note here

  
  We See the Library object lock was being requested in Shared mode (request=s)
  Name of the The object is SCOTT. EMPLOYEES
  
  
• Use the ' handle address ' to find the process of holding the lock on your resource by doing a search on the address WI Thin the same tracefile.

  PROCESS:  ----------------------------------------  so:0x7d2bcca8, Type:2, Owner: (Nil), flag:init/-/-/ 0x00  (process) Oracle pid=18, calls CUR/TOP:0X79F3AB84/0X7D3D5FC8, flag: (0)-  int error:0, call error:0, Sess  error:0, txn error 0  (post info) Last Post received:109 0 4 last  post RECEIVED-LOCATION:KSLPSR last  process To post me:7d2b8d94 1 6 last  post sent:0 0 last  post sent-location:ksasnd last  process posted by me:7d 2B8D94 1 6  <cut>  so:0x75fe8f7c, type:53, owner:0x7b751914, flag:init/-/-/0x00  LIBRARY OBJECT LOC k:lock=75fe8f7c HANDLE=62D064DC mode=x----> Note here, Holder here!!! Call  pin= (nil) session pin= (nil) hpc=0000 hlc=0000  Htl=0x75fe8fc8[0x79f81790,0x79fc3ef8] htb=0x79f81790 ssga=0x79f8102c  user=7d3988d0 session=7d3988d0 count=1 flags=[0000] savepoint=0x146e  LIBRARY OBJECT HANDLE : Handle=62d064dc mtx=0x62d06590 (0) cdp=0  Name=scott. EMPLOYEES----> Note here

  
  From the output of we can see that the Process of (PID) is holding a exclusive lock (MODE=X) on the object we were 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 this session are also listed in the Tracefile (statements and other library caches Objects is preceded by ' name= ').

Second, query x$kgllk form

1.
Gets the session address of the session where the wait event is the library cache lock

Sql> Select Sid,saddr from v$session where event= ' library cache lock ';       SID saddr--------------------------       000000009f783740       198 000000009f06afe0  

2.
The object that is involved in the library cache lock is obtained according to the saddr above.

Sql> Select Kgllkhdl handle,kgllkreq Request, kglnaobj Object from  x$kgllk   2  where kgllkses = ' 000000009f06afe0 ' and  kgllkreq > 0;     ---> Substituting the above saddrhandle              REQUEST OBJECT-------------------------------------------------------- 00000000976ae938          3 DUMMY11GR2                   

Attention:
KGLLKHDL: The P1raw column corresponding to the v$session_wait, indicating the lock address of the library cache lock. Also corresponds to X$kglob's Kglhdadr column.

3.
According to the saddr of the above waiting person, get the information of the blocking person (i.e.: the holder): Saddr,handle,mod,object

Sql> Select Kgllkses saddr,kgllkhdl handle,kgllkmod mod,kglnaobj object  2 from   x$kgllk lock_a  3   where Kgllkmod > 0  4 and   exists (select Lock_b.kgllkhdl from X$kgllk lock_b  5   where kgllkses = ' 0000000 09f06afe0 '/* Blocked session */  ---> Substituting saddr  6 and   lock_a.kgllkhdl = lock_b.kgllkhdl  7   and kgllkreq > 0); Saddr            HANDLE                  MOD OBJECT------------------------------------------------------------------------ 000000009f7773c0 00000000976ae938          1 dummy11gr2000000009f6c3020 00000000976ae938          3 dummy11gr2elapsed: 00:00:04.07  ---> This time can be very long, after all, this is my test environment, but 4 user session, but it took 4 seconds.

4.
Information about the blocked person (i.e.: the holder): Sid,username,terminal,program

Sql> Select Sid,username,terminal,program from v$session where Saddr  in (' 000000009f7773c0 ', ' 000000009f6c3020 ' );       SID USERNAME                       TERMINAL program                       -------------------------------------------------------------------------- --------------------------------------------        LC0029999                      pts/0                          [email protected] (TNS v1-v3)       136 LC0019999                      PTS/1                          [email protected] (TNS v1-v3) elapsed:00:00:00.15sql>---> found 2 session These 2 sessions are the blocker (or the holder) session.

5.
Find out all the information about the waiting person:

Select Sid,username,terminal,program from V$session where saddr in  (select kgllkses from X$kgllk lock_a   where KGL Lkreq > 0 and  exists (select Lock_b.kgllkhdl from X$kgllk lock_b              where kgllkses = ' 000000009f06afe0 '/* Blockin G Session */and              LOCK_A.KGLLKHDL = lock_b.kgllkhdl and              kgllkreq = 0);

Report:
---SID 136

Sql> Set Timing onsql> show Useruser is ' LC0019999 ' sql> select * from V$mystat where rownum<2;        SID statistic#      VALUE------------------------------       136          0          0elapsed:00:00:00.00sql> Begin    2  DUMMY11GR2;    3  dbms_lock.sleep (3600);    4  End;    5  /  

---SID 71

Sql> Set Timing onsql> show Useruser is ' LC0029999 ' sql> select * from V$mystat where rownum<2;        SID statistic#      VALUE------------------------------          0          0elapsed:00:00:00.00sql> Alter Procedure Lc0019999.dummy11gr2 compile;alter procedure LC0019999.DUMMY11GR2 Compile--->hang live  

"Translated from MoS article" Find a way to hold the library cache lock session