prog:sql_lock_library_cache
Unterschiede
Hier werden die Unterschiede zwischen zwei Versionen angezeigt.
Beide Seiten der vorigen RevisionVorhergehende ÜberarbeitungNächste Überarbeitung | Vorhergehende Überarbeitung | ||
prog:sql_lock_library_cache [2010/10/25 22:48] – gpipperr | prog:sql_lock_library_cache [2021/03/11 11:44] (aktuell) – [Quellen] gpipperr | ||
---|---|---|---|
Zeile 1: | Zeile 1: | ||
+ | ====== Library cache Lock untersuchen ====== | ||
+ | **10/2010 zum erstenmal das Thema bearbeitet** | ||
+ | |||
+ | |||
+ | Aus der Doku: | ||
+ | < | ||
+ | |||
+ | 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). | ||
+ | </ | ||
+ | |||
+ | ==== Demo Case ==== | ||
+ | |||
+ | Code in Session 1 anlegen, in Session 2 aufrufen und in Session 1 erneut anlegen => Ein Library Cache Lock entsteht | ||
+ | 1. Beispiel Code Endlos Schleife | ||
+ | < | ||
+ | create or replace procedure endless | ||
+ | is | ||
+ | begin | ||
+ | loop | ||
+ | null; | ||
+ | end loop; | ||
+ | end; | ||
+ | / | ||
+ | </ | ||
+ | 2. in zweiter Session aufrufen | ||
+ | < | ||
+ | sqlplus> | ||
+ | </ | ||
+ | |||
+ | 3. Code erneut anlegen \\ | ||
+ | |||
+ | 4. Abfrage wer hat den Lock: | ||
+ | < | ||
+ | /** 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 ' | ||
+ | </ | ||
+ | Session die die Procedure erneutern will => ABER Lock das zweite Session PL/SQL Objekt im Zugriff hat => Event ' | ||
+ | |||
+ | 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. | ||
+ | < | ||
+ | /**** Suchen über die v$lock und die X$KGLLK mit Username der blockierenden Session ***/ | ||
+ | SELECT * FROM X$KGLLK where user_name = ' | ||
+ | /*** Beispiel ***/ | ||
+ | select | ||
+ | kglnaobj, kgllkreq | ||
+ | from | ||
+ | x$kgllk x join v$session s on | ||
+ | s.saddr = x.kgllkses; | ||
+ | </ | ||
+ | |||
+ | ---- | ||
+ | |||
+ | ===== Quellen ==== | ||
+ | |||
+ | Support Nodes: | ||
+ | |||
+ | * Troubleshooting Library Cache: Lock, Pin and Load Lock (Doc ID 444560.1) | ||
+ | * ' | ||
+ | * How to Find which Session is Holding a Particular Library Cache Lock (Doc ID 122793.1) | ||
+ | |||
+ | Sehr gute Präsentation zu dem Thema: | ||
+ | |||
+ | * https:// |
prog/sql_lock_library_cache.txt · Zuletzt geändert: 2021/03/11 11:44 von gpipperr