Library load locks
                                                 Last update (2012-01-24 10:05:54)
                                                                                                                   Date added (2008-07-28 14:01:38)

Summary
Basically this happens when the session tries to find the load lock for the database object so that it can load the object. The load lock is always obtained in Exclusive mode, so that no other process can load the same object. If the load lock is busy the session will wait on this event until the lock becomes available.

If an object is not in memory, then a library cache lock cannot be acquired on it. So the object has to be loaded into the memory to acquire the lock. Then the session tries to find the load lock for the database object so that it can load the object. In order to prevent multiple processes to request the load of the same object simultaneously, the other requesting sessions have to wait for the library cache load lock as the lock is busy with loading the object into the memory.

The waits on the library cache load lock is due to the objects not available in the memory. The unavailability of the library cache object in the library cache is due to the undersized shared pool causing reloads often, too many hard parse because of unshared sqls.

Use the following script to identify Library load locks
select /*+ ordered */ w1.sid  waiting_session,
	h1.sid  holding_session,
	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
 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;
Reviews
Categories
Filters
Search