Oracle Library Cache Lock/Pin

Library cacheHandle stores the lock and pin information. In addition, both the Library cache handle and child cursor have lock and pin. These are called library cache lock and library cache pin.
 
Library cachelock/pin is used to control concurrent access to librarycache objects. Lock Management concurrency, pin management Consistency, lock is for librarycache handle, and pin is for heap.
 
To access a library cache object, first obtain the handle lock pointing to this object. After obtaining the lock, we need to pin the heap pointing to this object.
 
When we compile packages, stored procedures, functions, and views, Oracle first obtains a library cache lock on the handle of these objects, then obtain the pin on the heap of these objects, so that other processes do not modify the definition of these objects during compilation, or delete the objects.
 
When a session performs hard parsing on SQL statements, this session must obtain librarycache lock, so that other sessions cannot access or change the objects referenced by this SQL statement. If this wait event takes a long time, it usually indicates that the shared pool is too small (because the Shared Pool is too small, you need to search for the free chunk, or you can remove some object pages out, this takes a long time). Of course, another session may be modifying the object (such as the split partition), and the current session needs to reference the table, in this case, we must wait for another session to complete.
 
Library Cache lock has three normal modes:
(1) Share (S): obtained when reading a library cache object
(2) Exclusive (X): obtained when a library cache object is created/modified.
(3) Null (N): Used to ensure object dependency
 
For example, if a process wants to compile a view, a shared lock will be obtained. If we want to create/drop/alter an object, the exclusive lock will be obtained. The Null lock is very special. We have a NULL lock on any executable object (cursor, function). We can break this NULL lock at any time. When this NULL lock is broken, it indicates that this object has been changed and needs to be re-compiled.
 
The primary purpose of the NULL lock is to mark whether an object is valid. For example, an SQL statement obtains the NULL lock during parsing. If the SQL object is always in the shared pool, the NULL lock will always exist, when the table referenced by this SQL statement is modified, the NULL lock is broken because the Exclusive lock is obtained when the SQL statement is modified. Because the NULL lock is broken, the next time you execute this SQL statement, you need to re-compile it.
 
Library Cache pin has two modes:
(1) Share (S): Read object heap
(2) Exclusive (X): Modify object heap
 
When a session wants to read the object heap, it needs to get a pin in the sharing mode. When a session wants to modify the object heap, it needs to get the exclusive pin. Of course, you must get the lock before getting the pin.
In Oracle10gR2, the library cache pin is replaced by library cache mutex.
 
Library cache lock is used to control concurrent access to library cache objects. As mentioned above, you must obtain the librarycache lock before accessing the library cache object. lock is not an atomic operation (an atomic operation is an operation that will not be broken in the operation process, obviously, here the lock can be broken. Oracle introduces the library cache latch mechanism to protect this lock. That is to say, before obtaining the library cache lock, you must first obtain the library cache latch, after obtaining the library cache lock, release the library cache latch.
 
If a library cache object is not in the memory, this lock cannot be obtained. In this case, you need to obtain a library cache load lock latch and then a library cache load lock, after the load lock is obtained, the library cache load lock latch will be released.
 
Library cache latch is controlled by the implicit parameter _ KGL_LATCH_COUNT. The default value is the minimum prime number equal to or greater than the number of CPUs in the system. However, Oracle imposes a hard limit on it. This parameter cannot be greater than 67.
 
Note: Query _ kgl_latch_count is sometimes 0, which is a bug.
 
Oracle uses the following algorithm to determine which sub-latch is used to protect the library cache object handle:
Latch # = mod (bucket #, # latches)
 
That is to say, which sub-latch is used to protect a handle Based on the bucket number where the handle is located, and the total number of sub-latch for hash calculation.
 
In the MOS document [122793.1], there are usually two reasons for librarycache lock:
(1) 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 a table with thousands of records)
In this case, V $ LOCK will show that the session doing the 'alter table' with an exclusive DML enqueue lock on the TABLE object (LMODE = 6, TYPE = TM where 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 will be insufficient to track down the culprit blocking your operation.
 
(2) The compilation of package will hang on Library Cache Lock and Library Cache Pin if any users are executing a procedure/function defined in the same package.
 
For more information, see:
OracleLibrary cache internal mechanism description

OracleLibrary Cache lock and pin description

OracleNamespace description

Troubleshooting of one librarycache pin fault

 
Ii. Process Library cache lock
2.1 use hanganalyze + systemstat for analysis
The Systemstat event contains detailed information about each oracle process. When you operate hang, you can open a new window to use this event to capture relevant information.
 
Systemdump level description:
LEVEL parameter:
10 Dump all processes (IGN state)
5 Level 4 + Dump all processes involved in wait chains (NLEAF state)
4 Level 3 + Dump leaf nodes (blockers) in wait chains (LEAF, LEAF_NW, IGN_DMP state)
3 Level 2 + Dump only processes thought to be in a hang (IN_HANG state)
1-2 Only HANGANALYZE output, no process dump at all
Level 266 = system state (level = 10, with short stacks) = level 10 + short stacks
Level 266 includes the short stacks information of the process on the basis of level 10.
 
Run the following script after Oracle 9.2.0.1:
$ Sqlplus '/as sysdba'
Oradebug setmypid
Oradebug unlimit
Oradebug dump systemstate 266
Oradebug tracefile_name
 
Systemstat 226 is unavailable before 9.2.0.6, so the following command can be used in earlier versions:
Alter session set max_dump_file_size = unlimited;
Alter session set events 'immediate trace name systemstate level 10'
 
Execute hanganalyze first, as shown below:
SQL> oradebug setmypid
SQL> oredebug unlimit
SQL> oradebug setinst all
SQL> oradebug-g def hanganalyze 3;
SQL> oradebug tracefile_name
 
Other sessions in the following file are blocked by 1169:
State of ALL nodes
([Nodenum]/cnode/sid/sess_srno/session/ospid/state/[adjlist]):
[1001]/1/1002/9/c00000063d7aff78/9720/NLEAF/[1169]
[1159]/1/1160/51635/c00000063d8dfc68/19539/NLEAF/[1169]
[1160]/1/1161/15627/c000000631959658/8818/NLEAF/[1169]
[1162]/1/1163/27931/c0000006398d7810/20170/NLEAF/[1169]
[1165]/1/1166/4003/c0000006358f4d58/22069/NLEAF/[1169]
[1166]/1/1167/45511/c0000006398d4868/15674/NLEAF/[1169]
[1167]/1/1168/46253/c00000063d8d9d18/29492/NLEAF/[1169]
[1169]/1/1170/9233/c0000006358f1db0/9434/LEAF_NW/
[1170]/1/1171/43901/c0000006398d18c0/13246/NLEAF/[1169]
[1171]/1/1172/53701/c00000063d8d6d70/13794/NLEAF/[1169]
[1172]/1/1173/23737/c000000631950760/25188/NLEAF/[1169]
[1173]/1/1174/28801/c0000006358eee08/24770/NLEAF/[1169]
[1175]/1/1176/25017/c00000063d8d3dc8/18795/NLEAF/[1169]
[1177]/1/1178/3/c0000006358ebe60/10170/NLEAF/[1169]
 
Here sess_srno is the serial # In v $ session #.
The Ospid is the system process number.
 
After finding sid and serial #, you can view the information of the corresponding session. If the session does not have SQL _id, you can use the corresponding process of oradebug systemdump. To view information.
 
Oradebug setospid 9434
Oradebug unlimit
Oradebug dump systemstate 10
Oradebug TRACEFILE_NAME
Oradebug close_trace
 
Then we use awk to analyze the trace of systemdump:
Oracle uses the ass. awk tool to view system state dump instructions

You can also use systemdump to view information about all processes.
 
2.2 view the X $ KGLLK table
-- The X $ KGLLK table can only be accessed by SYS/INTERNAL users. It contains information about all library object locks (held and requested ).
The X $ KGLLK table (accessibleonly as SYS/INTERNAL) contains all the library object locks (both held & requested) for all sessions and is more complete than the V $ LOCK view althoughthe column names don't always reveal their meaning.
 
-- View the session address (SADDR) of the session waiting for the library cache lock event ):
Select sid, saddr from v $ session where event = 'library cache lock ';
SID SADDR
------------------
16 572ed244
 
-- View the specific lock information from x $ kgllk:
Select kgllkhdl Handle, kgllkreq Request, kglnaobj Object
From x $ kgllk
Where kgllkses = '572ed244'
And kgllkreq> 0;
HANDLE REQUEST OBJECT
-------------------------------------
62d064dc 2 EMPLOYEES
 
KGLLKREQ: This will show you the library cache lock requested by this session (KGLLKREQ> 0)
KGLNAOBJ: contains the first 80 characters of the name of the object.
KGLLKHDL: corresponds with the 'handle address' of the object
 
-- View the session of KGLLKMOD> 0 from X $ KGLLK according to KGLLKHDL. The session is holding the lock:
Select kgllkses saddr, kgllkhdl handle, kgllkmod, kglnaobj object
From x $ kgllk lock_a
Where kgllkmod> 0
Andexists (select lock_ B .kgllkhdl
From x $ kgllk lock_ B
Where kgllkses = '572ed244'/* blocked session */
And lock_a.kgllkhdl = lock_ B .kgllkhdl
And kgllkreq> 0 );
SADDR HANDLE MOD OBJECT
----------------------------------

 
-- View all blocked sessions:
Selectsid, username, terminal, program
From v $ session
Where saddr in
(Select kgllkses
From x $ kgllk lock_a
Where kgllkreq> 0
Andexists (select lock_ B .kgllkhdl
From x $ kgllk lock_ B
Where kgllkses = '572eac94 '/* blocking session */
And lock_a.kgllkhdl = lock_ B .kgllkhdl
And kgllkreq = 0 ));
 
-- View what all the sessions that hold the librarycache pin or lock are doing:
SELECT s. sid, kglpnmod "Mode", kglpnreq "Req", SPID "OS Process"
FROM v $ session_wait w, x $ kglpn p, v $ session s, v $ process o
WHERE p. kglpnuse = s. saddr
AND kglpnhdl = w. p1raw
And w. event like '% library cache %'
And s. paddr = o. addr
 
2.3 handle problems
In general, the 2.1 or 2.2 method can be used to find the root cause of library cache lock and determine which session is caused. For example, in the above hanganalyze, the session is 1169. We only need to kill this session and solve other problems automatically.
 
Kill the session at the DB level first. If the session cannot be killed, kill at the OS level.
 
Alter systemkill session '2017 3 ';
 
Note: Before operating system kill, run the ps command to check the process. If it is a DB process, do not kill it at will; otherwise, the system crash will occur.
 
Ps-ef | grep 9434
Kill-9 9434
 
 
Refer:
How to Find which Session is Holding a special library Cache Lock [ID 122793.1]