Unterschiede

Hier werden die Unterschiede zwischen zwei Versionen angezeigt.

--- prog:sql_lock_library_cache [2010/10/25 22:48] – gpipperr
+++ prog:sql_lock_library_cache [2021/03/10 11:42] – [Library cache Lock untersuchen] gpipperr
@@ Zeile 1: / Zeile 1: @@
+====== Library cache Lock untersuchen ======
+**10/2010 zum erstenmal das Thema bearbeitet**
+Aus der Doku:
+<code>
+wait: library cache lock
+The library cache lock controls the concurrency between clients of the library cache by acquiring 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 (no other client can change the object).
+This lock is also obtained as part of the operation to locate an object in the library cache (a library cache child latch is obtained to scan a list of handles, then the lock is placed on the handle once the object has been found).
+</code>
+==== Demo Case ====
+Code in Session 1 anlegen, in Session 2 aufrufen und in Session 1 erneut anlegen => Ein Library Cache Lock entsteht
+. Beispiel Code Endlos Schleife
+<code>
+create or replace procedure endless
+is
+begin
+ loop
+  null;
+ end loop;
+end;
+/
+</code>
+. in zweiter Session aufrufen
+<code>
+sqlplus>exec endless
+</code>
+. Code erneut anlegen \\
+. Abfrage wer hat den Lock:
+<code>
+/** Waiter ******************/
+select ss.sid
+     , ss.username
+     , ss.terminal
+     , ss.program
+	 , sw.event
+  from v$session ss , v$session_wait sw
+ where ss.sid=sw.sid
+   and sw.event like 'library cache %'
+</code>
+Session die die Procedure erneutern will => ABER Lock das zweite Session PL/SQL Objekt im Zugriff hat => Event 'library cache pin'
+Session die blockiert:
+--- in Arbeit ----
+über die **x$kgllk** \\
+\\
+This table lists all held and requested library object locks for all sessions.
+\\
+The column kglnaobj displays the first 80 characters of the name of the object.
+\\
+kgllkreq = 0 means, the lock is held, \\
+while kgllkreq > 0 means that the lock is requested.
+<code>
+/**** Suchen über die v$lock und die X$KGLLK mit Username der blockierenden Session ***/
+SELECT * FROM X$KGLLK where user_name = 'GPI' and KGLLKEXC > 0
+/*** Beispiel ***/
+select
+  kglnaobj, kgllkreq
+from
+  x$kgllk x join v$session s on
+    s.saddr = x.kgllkses;
+</code>
+----
+===== Quellen ====
+Support Nodes:
+  * Troubleshooting Library Cache: Lock, Pin and Load Lock (Doc ID 444560.1)
+  * 'library cache lock' Waits: Causes and Solutions (Doc ID 1952395.1)
+  * How to Find which Session is Holding a Particular Library Cache Lock (Doc ID 122793.1)