Problemstellung Der INITRANS-Parameter

Werbung
Betrifft
ITL-Waits identifizieren mit Oracle9i 9.2.x
Autor
Andri Kisseleff ([email protected])
Art der Info
Technische Background Info und Trivadis Scripts
Quelle
Aus dem NF9i-DBA Kurs und TUN-Kurs der Trivadis
Problemstellung
Ihre User beklagen sich, dass sie ab und zu bei einer schreibenden Transaktion sehr
lange warten müssen bis sie diese ausführen können?
Vielleicht ist es ein Lock-Wait? Vielleicht wurde auf einer Tabelle der INITRANSParameter zu klein gesetzt, was zu einem ITL-Wait führt?
Wie kann ich nun herausfinden, ob es sich um ein Lock-Wait oder ein ITL-Wait handelt?
Wie kann ich herausfinden auf welchen Tabellen ich ITL-Waits hatte und deshalb die
Tabelle reorganisieren und mit einem grösseren Wert für INITRANS neu erstellen sollte?
Diese Fragen versuchen wir in diesem Artikel zu beantworten.
Der INITRANS-Parameter
Beim CREATE TABLE und CREATE INDEX können wir einen Wert für INITRANS
spezifizieren.
Was sagt die Doku zu INITRANS:
Specify the initial number of concurrent transaction entries allocated within each
data block allocated to the database object. This value can range from 1 to 255 and
defaults to 1. The default value for an index is 2.
Was bedeutet "concurrent transaction entries"?
Jede Transaktion, welche in einem Block einen oder mehrere Records modifiziert
(INSERT, UPDATE, DELETE) muss (etwas vereinfacht gesagt) ihre
"Transaktionsnummer" in den Header des Blocks schreiben. Dies ist die so genannte
ITL-Liste im Header eines jeden Blocks (ITL = Interested Transaction List). Diese ITLListe ist in jedem Oracle (Daten- und Index-) Block, und nicht etwa nur einmal pro
Segment.
Wenn wir in einem Oracle-Block 100 Records haben (record1, record2, record3, etc.)
und user1 modifiziert record1, dann belegt er den ersten ITL-Eintrag im Header dieses
Blocks. Dies nicht nur kurzfristig, sondern bis zum COMMIT oder ROLLBACK.
Nun kommt user2 und will record2 updaten (user1 hat noch kein COMMIT gemacht).
Falls die Tabelle mit INITRANS 2 erstellt wurde, so kann user2 garantiert die
Transaktion ausführen und belegt den zweiten ITL-Eintrag (wir nennen das in unseren
Kursen z.T. auch "Transaction Entry Slot"). Falls die Tabelle aber mit INITRANS 1
erstellt wurde, so hat user2 ggf. ein kleines Problem, da bereits alle ITL-Slots belegt
sind. Hat es noch freien Platz im PCTFREE des Blocks, so hat user2 Glück gehabt.
Oracle schreibt den ITL-Eintrag ins PCTFREE des Blocks und user2 kann seine
Transaktion ausführen. Wenn er aber keinen freien ITL-Slot mehr findet und das
PCTFREE des Blocks ebenfalls bereits gefüllt ist oder sehr klein gewählt wurde, so
muss user2 warten bis user1 commited hat um seine Transaktion auszuführen. Er hat
ein ITL-Wait.
Für den User wirkt sich dies aus wie ein "Lock Wait". Eigentlich ist es aber kein Locking
Problem, da user2 ja nicht record1, sondern record2 updaten will. Aber der Effekt ist der
Selbe.
Bei transaktionsorientierten Tabellen, wo die Chance gross ist, dass 2-n User
gleichzeitig auf denselben Blöcken Änderungen vornehmen, sollte also INITRANS
entsprechend höher gesetzt werden.
Wenn Sie eine Tabelle häufig mit parallelen DML-Befehlen bearbeiten, so kann dies
auch ein Grund sein, INITRANS zu erhöhen, da jeder PDML-Slave Prozess intern als
einzelne Transaktion gehandhabt wird und somit eigene ITL-Slots benötigt.
Auf der anderen Seite sollte man aber INITRANS auch nicht zu gross spezifizieren, da
so ein ITL-Slot 24 Bytes pro Block (pro Block, nicht pro Tabelle) belegt. Bei früheren
Oracle-Versionen waren dies noch 23 Bytes pro Slot pro Block.
Wie viel es bei Ihrer Version ist können Sie in v$type_size identifizieren:
SQL> select * from v$type_size
2 where type = 'KTBIT';
COMPONEN TYPE
DESCRIPTION
TYPE_SIZE
-------- -------- -------------------------------- ---------KTB
KTBIT
TRANSACTION VARIABLE HEADER
24
Die Kunst des INITRANS-Tunings ist, INITRANS so gross wie notwendig, aber so klein
wie möglich zu spezifizieren.
Was wird in diese ITL-Slots geschrieben?
Eingangs hatte ich gesagt "die Transaktionsnummer". In Wirklichkeit stehen in diesen 24
Bytes diverse Informationen, welche fürs Locking, für Lesekonsistenz für
konkurrenzierende User und für Rollback notwendig sind.
So wird z.B. die SCN (System Change Number) der schreibenden Transaktion in diese
ITL-Slots geschrieben, andererseits aber auch Informationen zu:
-
welche UNDO-Segment-Nr. wird von dieser TX (Transaktion) verwendet
welcher Slot in diesem Undo-Segment
die UBA (undo block address) des letzten von dieser TX verwendeten UNDOBlocks
der LOCK-Status der TX (aktiv, commited, commited and cleand out, etc.)
etc., etc.
Der MAXTRANS-Parameter
Was sagt die Doku dazu?
Specify the maximum number of concurrent transactions that can update a data
block allocated to the database object. This limit does not apply to queries. This value
can range from 1 to 255 and the default is a function of the data block size. You should
not change the MAXTRANS value from its default.
Ja, das meinen wir auch. Lassen Sie MAXTRANS immer auf dem Default-Wert. Solange
eine Transaktion den ITL-Eintrag in einen ITL-Slot oder ins PCTFREE schreiben kann,
so wollen wir, dass die Transaktion ausgeführt wird. Wir wollen dies nie limitieren.
INITRANS und MAXTRANS mit Oracle9i
In der Doku steht zwar INITRANS und MAXTRANS könne von 1-255 (Default 1)
spezifiziert werden. Seit Oracle9i stimmt dies aber nicht mehr.
Die 9i-Limiten für INITRANS und MAXTRANS liegen zwischen 2 und 255.
Auch wenn Sie eine Tabelle explizit mit INITRANS 1 erstellen und im Data-Dictionary
INITRANS 1 steht, hat die Tabelle in Wirklichkeit zwei (2) ITL-Slots.
Dies kann man mit kleinen Tests sehr einfach beweisen.
ITL-Waits identifizieren
Nun stellt sich die Frage: Auf welchen Tabellen oder Indexes habe ich wohl INITRANS
zu klein spezifiziert, so dass dies zu ITL-Waits geführt hat? (oder ein Döfling (sorry) hat
MAXTRANS auf 1 oder 2 gesetzt)?
Seit Oracle9i Release 2 (9.2.x) kann man dies in V$SEGMENT_STATISTICS relativ
einfach identifizieren, falls der Instance-Parameter STATISTICS_LEVEL auf TYPICAL
oder ALL gesetzt ist (Default: TYPICAL).
Verwenden Sie dann das Trivadis-Freeware-Script lsitlsta.sql von der Download Sektion
auf www.trivadis.com.
SQL> connect sys as sysdba
Enter password: *******
Connected.
SQL> @lsitlsta
Wrote file dummy.tmp
ITL-Waits per table (interested transaction list) (INITRANS to small)
DB1.TRIVADIS.COM
17.02.2003 18:03 - 20.02.2003 08:18 (62.2 h)
OWNER
--------------SCOTT
SCOTT
OBJECT_NAME
NBR. ITL WAITS
------------------------------ -------------ITL_TEST
1
ITL_TEST2_MAXTRANS
1
lsitlsta.lis has been spooled...
Please note that v$segment_statistics is only updated at the
moment the ITL-wait *is solved* (and not while the
user is waiting for a free ITL-entry)
Tabellen- (oder Index-) Besitzer, Name des Objektes (Tabelle oder Index) und ggf. SubObjektes (Partitionsname) und die Anzahl ITL-Waits seit dem letzten Startup werden
aufgelistet.
Das Trivadis-Utility TVD$TUN weist solche Tabellen und Indexes selbstverständlich
ebenfalls aus. Dort wird so ein Problem dann z.B. so dargestellt:
1.15. Transaction Entry Slot (ITL) Waits
Benutzername Object
Subobject Tablespace Anzahl
SCOTT
TEST_ITL
SYSTEM
2
TVD$TUN ist *das* Utility für den "Health-Check" Ihrer Datenbank und Instance. Dies
was Tuning, Struktur und Security betrifft. Wir analysieren und dokumentieren Ihre
Umgebung, vor allem auch die vorhandenen Probleme, wie z.B. auch die ITL-Waits.
Solche Tabellen sollte man dann neu erstellen mit einem grösseren INITRANS (oder
ggf. grösseren PCTFREE). Für diese Reorganisation empfehlen wir online oder offline
Reorganisationen mit TabReorg/TabEXP der Trivadis ☺
V$SEGMENT_STATISTICS
Falls Sie V$SEGMENT_STATISTICS nicht kennen sollten (Was? Sie haben unseren
NF9i-R2 Techno-Circle oder unseren NF9i-DBA Kurs nicht besucht???!!!???), so
schauen Sie sich diese V$ mal genauer an. Dort finden Sie auch I/O-Statistiken auf
Segment-Ebene, Lock-Wait Statistiken pro Tabelle, etc., etc.
SQL> select distinct statistic_name from v$segment_statistics;
STATISTIC_NAME
---------------------------------------------------------------ITL waits
buffer busy waits
db block changes
global cache cr blocks served
global cache current blocks served
logical reads
physical reads
physical reads direct
physical writes
physical writes direct
row lock waits
ITL-Waits online identifizieren
Früher, wenn ein End-User ein Locking Problem hatte, hat er der Hot-Line oder dem
DBA telefoniert. Heute booten die End-User den PC neu (der PC hängt wohl mal
wieder...) und wenn dann alles wieder aufgestartet ist, so stellen sie erleichtert fest, dass
das Problem (dadurch!) gelöst wurde (und erzählen es dann stolz in der Cafeteria...).
Falls Sie trotzdem mal ein diesbezügliches Telefon erhalten sollten, so möchten Sie
sicher gerne feststellen ob es sich um ein LOCK-Wait oder ein ITL-Wait handelt.
Dies kann im Lock-Monitor des Oracle Enterprise Managers relativ einfach identifiziert
werden (oder natürlich auch mit den Trivadis Scripts sslckwai.sql und sslckwa1.sql).
User IKISS wartet hier auf einen ITL-Slot, welchen er nicht kriegt, da dieser von SCOTT
gehalten wird.
Der Verursacher hat ein TX X (eXclusive) und der Waiter ein TX S (Share). Dies ist die
eindeutige Identifikation.
(Wäre es TM X und TM S oder TX X und TM S, so würde der Waiter auf ein SHARELock warten oder wäre es TX X und TX X, so würde der Waiter auf ein Row-level Lock
warten)
TX X und TX S = ITL-Wait.
Mit View/Edit-Detail (oder sslckwa1.sql) kann man dann den SQL-Befehl des Waiters
einsehen und somit ist klar, auf welcher Tabelle man ein INITRANS-Problem hat.
Fazit
Mit Oracle9i Release 2 stehen uns mit STATISTICS_LEVEL = TYPICAL | ALL weitere
interessante Informationen fürs Tuning unserer Datenbanksysteme zur Verfügung.
Früher war es quasi unmöglich zu identifizieren, auf welchen Tabellen man INITRANS
zu klein spezifiziert hatte. Heute ist dies ein Kinderspiel, wenn man weiss wie.
Wer unsere NF9i-DBA oder AI9-B-DBA Kurse besucht hat, der weiss wie. Wer dann
noch den TUN-Kurs besucht, der weiss *wirklich* wie!
ITL = Ich Tuning Lernen. Bei Trivadis!
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
Herunterladen