Problemstellung V$ENQUEUE_STAT

Betrifft
Enqueue-Waits identifizieren mit Oracle9i
Autor
Andri Kisseleff ([email protected])
Art der Info
Technische Background Info und Trivadis Scripts (Februar 2003)
Quelle
Aus dem TUN-Kurs der Trivadis
Problemstellung
Habe ich Lock-Waits oder interne Enqueue-Waits auf meinem Datenbank-System? Wie
lange haben diese gedauert?
Diese Fragen versuchen wir in diesem Artikel zu beantworten.
V$ENQUEUE_STAT
Seit Oracle9i kann man dies relativ einfach in V$ENQUEUE_STAT identifizieren. Die
Information, wie lange diese Lock-Waits/Enqueue-Waits gedauert haben kriegt man
selbstverständlich nur, wenn TIMED_STATISTICS auf TRUE gesetzt ist.
Ob der 9.2-Parameter STATISTICS_LEVEL auf BASIC, TYPICAL oder ALL gesetzt ist
spielt für diese Auswertung übrigens keine Rolle.
Verwenden Sie das Freeware-Trivadis-Script lsenqsta.sql ab unserer Download-Sektion
bei www.trivadis.com.
Beispiel:
SQL> connect sys as sysdba
Enter password: *******
Connected.
SQL> @lsenqsta
Wrote file dummy.tmp
Only enqueues with wait time Y/<N> ?
Enqueue requests/waits sorted by wait time and nbr. requests
DB1.TRIVADIS.COM
17.02.2003 18:03 - 20.02.2003 10:09 (64.1 h)
ENQ_NAME
Nbr. Requests Nbr. Waits Nbr. Fail.
Wait Time
------------------------------ ------------- ---------- ---------- -----------TX Transaction
334,836
10
5
4,498.422
UL User-defined Locks
2
1
0
46.016
TM DML Enqueue
691,425
2
0
6.531
TC TC
10
2
0
0.093
CF Controlfile Transaction
89,455
0
2
0.000
JD JD
45,023
0
0
0.000
HW Space management operations
TT Temporary Table
US Undo Segment, Serialization
etc.
etc.
14,692
8,850
8,500
0
0
0
0
0
0
0.000
0.000
0.000
33 rows selected.
lsenqsta.lis has been spooled...
Most important for you are the locks of type TX, TM and UL
Wait time is in seconds (27.123 = 27 seconds and 123 milliseconds)
Please note that statistics are only inserted into v$enqueue_stat
at the moment a locking problem is solved or raises an error.
As long as a user waits for a lock, no insert is done.
Please also note, that a deadlock (ORA-00060) does not appear in "Nbr. Failures"
Wir können hier erkennen:
-
Die Anzahl Locks/Enqueue-Requests (ENQ_NAME, Nbr. Requests) seit dem
letzten Startup
Wie häufig ein User das Lock nicht umgehend erhalten hat (Nbr. Waits)
Wie häufig ein User einen Fehler bei einem Lock-Request erhalten hat (Nbr. Fail.)
(z.B. ein Timeout, oder ein "Resource busy and acquire with nowait specified",
etc.)
Wie lange diese Waits zusammengerechnet gedauert haben (Wait Time). Dies
nur falls TIMED_STATISTICS = TRUE
Auf dieser Instanz/Datenbank hatten wir sehr lange (über 4'000 Sekunden) TX-Waits.
Bei TX-Waits werden die Row-Level-Lock-Waits und ITL-Waits verbucht (Was ist ein
ITL-Wait? Siehe hierzu auch unseren Artikel über ITL-Waits. Interested Transaction
List).
Zweitens hatten wir ca. 46 Sekunden UL-Waits. Dies sind Waits, wo sich User unter
Verwendung von DBMS_LOCK in die Quere gekommen sind, also warten mussten.
(Beruhigend ist hier zu wissen, dass ein DBMS_LOCK.SLEEP(n) nicht hier verbucht
wird. Es handelt sich also um "echte" DBMS_LOCK-Waits)
TM-Lock-Waits (6.5 Sekunden) sind Lock-Waits auf Tabellenebene (LOCK TABLE...).
Auf Oracle internen Locks und Enqueues (Oracle-Prozesse, Background-Prozesse)
hatten wir keine Probleme.
Oracle9i und Oracle8i
V$ENQUEUE_STAT steht erst ab Oracle9i Release 1 (9.0) zur Verfügung. Falls Sie
ähnliche Auswertungen mit früheren Versionen machen möchten, so müssen Sie das
Script anpassen, so dass es auf X$KSQST zugreifft. Die Struktur und die Attribute
dieser X$ unterscheiden sich allerdings ziemlich im Vergleich zu V$ENQUEUE_STAT.
Fazit
Wo es sich lohnt im Bereich Locking-Probleme weitere Abklärungen zu starten ist mit
Oracle relativ einfach zu identifizieren.
Ab Oracle9i Release 2 (9.2) ist es dann recht einfach in V$SEGMENT_STATISTICS zu
identifizieren auf welchen Tabellen wir Row-Lock-Waits und ITL-Waits hatten.
Unter Verwendung der Trivadis-Scripts und unserer Software TVD$TUN und der
Ausbildung z.B. in unserem TUN-Kurs ist es noch einfacher ☺
Viel Erfolg und wenig Lock-Waits wünscht Ihnen...
Andri Kisseleff
Trivadis AG
Andri Kisseleff
Kanalstrasse 5
8152 Glattbrugg
Internet: http://www.trivadis.com
Mail:
[email protected]
Tel:
Fax:
+41 1 808 70 20
+41 1 808 70 21