Heute wollen wir uns dem Thema Hot Blocks widmen bzw. wie identifizieren wir Hot Blocks und vor allem wie lösen wir das Problem der Buffer Busy Waits.
Seit Oracle 10.1 macht es uns der AWR Report ein wenig leichter Buffer Busy Waits zu unterscheiden. Aber das Problem ist das selbe geblieben. So gibt es z.B. in einem AWR Report das Wait Event Read by other Session was in früheren Versionen nicht separat aufgelistet wurde, sondern als Buffer Busy Wait ausgegeben wurde.
Was ist überhaupt ein Hot Block? Ganz einfach. Ein Block der sehr häufig „angefasst“ wird. Sei es um ihn zu ändern oder ihn zu lesen. So wird z.B. ein heißer Block von einer Session gelesen bzw. geändert. Andere Sessions die Informationen abrufen oder ändern möchten müssen in dieser Zeit warten bis die Information in den Buffer Cache geladen wurde oder die Sperre auf den Block nicht mehr vorhanden ist um selbst die im Block enthaltenen Rows ändern zu können.
Zunächst einmal müssen wir überprüfen ob es sich überhaupt um einen Hot Block handelt. Gehen wir davon aus, dass wir in unserem AWR Report bzw. in der Enterprise Manager Console das Wait Event „Read by other Session“ als Problem erkennen. Das deutet auf eine Hot Block Situation hin. Das Wait Event Read by other Session wird immer dann aufgelistet, wenn eine Session einen Block lesen möchte, der bereits von einer anderen Session in den Buffer Cache geladen wird. Die wartende Session muss nun solange ausharren, bis sich der Block im Buffer Cache befindet.
Um herauszufinden welche Session denn nun auf welchen Block wartet können wir die View v$session_wait zurate ziehen. Hier ein Beispiel:
SELECT p1 "fileno", p2 "blockno", p3 "classno" FROM v$session_wait WHERE event = 'read by other session';Die angezeigte Information wird uns die Dateinummer, die Blocknummer und die Wait Klasse ausgeben. (Das abgefragte Event muss nicht „read by other session“ sein, es kann sich um ein anderes Wait Event handeln z.B. „buffer busy wait“)
Sollten die aufgelisteten Sessions häufig oder immer den selben Block betreffen, haben wir definitiv einen Hot Block um den sich mehrere Sessions streiten. Die folgende Abfrage wird uns das dazugehörige Segment und die Art des Segments ausgeben (die entsprechende Datei- und Blocknummer ist aus dem Abfrageergebnis oben zu entnehmen):
SELECT relative_fno, owner, segment_name, segment_type FROM dba_extents WHERE file_id=<fileno> AND <blockno> BETWEEN block_id AND block_id + blocks -1;Nun haben wir also ermittelt um welchen Block und welches Segment es überhaupt geht. Nun gibt es mehrere Lösungsansätze um diese Hot Block Situation zu bereinigen.
1. SQL Tuning: Ineffizientes SQL ist der Hauptgrund für Hot Blocks. Eventuell werden viel zu viele Blöcke in den Buffer Cache geladen. So kann es vorkommen, dass Blöcke die eigentlich gut im Buffer Cache aufgehoben sind weil Sie häufig abgefragt werden, aus Platzgründen wieder aus dem Buffer Cache fallen. Bei vielen verschiedenen Blöcken würde das unser „Read by other session“ Problem erklären. Ein Statement sollte möglichst wenige Blöcke und unterschiedliche Blöcke im Zugriff haben. Aus DBA Sicht ist das natürlich einfacher gesagt als in der Realität umzusetzen.
2. Daten von Hot Blocks neu verteilen: Das Löschen und Einfügen von Daten führt oft dazu, dass die Daten in neue Blöcke verteilt werden. Das führt dazu, dass die Chance einer Contention sinkt da die zugegriffenen Rows nun in unterschiedlichen Blöcken liegen die unabhängig voneinander gelesen werden können.
3. Anpassen von PCTFREE und PCTUSED: Der Wert PCTFREE gibt an, wieviel Platz in einem Block für Datenänderungen vorgehalten wird. Das bedeutet, wenn wir den Wert PCTFREE erhöhen, weniger Rows bei einer Initialen Blockbefüllung eingefügt werden. Sprich, es werden die selben Daten auf mehr Blöcke verteilt was unsere Hot Block Situation entschärft. Der Wert PCTUSED sollte entsprechend angepasst werden. Dieser steuert, wann ein Block wieder auf die Freelist kommt um weitere Daten aufzunehmen.
4. Indizes optimieren: Hier gibt es kein Allheilmittel. Sollte sich ein Hot Block in einem Index Segment befinden, so kann es z.B. ratsam sein über einen Reverse Index nachzudenken. Ein Reverse Index baut seine Baumstruktur mit „ungedrehten“ Spaltenwerten auf. Das führt z.B. bei einer indizierung einer fortlaufenden Nummer dazu, dass in der indizierten Tabelle hintereinander liegende Daten (1,2,3,….70980) auf unterschiedliche Indexblöcke verteilt werden. Ein weiteres Hilfsmittel ist auch bei Indizes die Werte PCTFREE und PCTUSED anzupassen.
5. Blockgröße anpassen: Je kleiner die Blockgröße, desto weniger Daten befinden sich in einem Block. Bedeutet im Umkehrschluss, dass die Chance auf eine Hot Block Situation sinkt. Im Grunde ist das Ergebnis das selbe wie bei einer PCTFREE Anpassung. In der Realität gestaltet es sich oftmals schwer die Blockgröße zu ändern. Daher ist diese Variante mit Vorsicht zu genießen.
In unserem Beispiel sind wir zwar vom Wait Event „Read by other Session“ ausgegangen, jedoch sind die oben beschriebenen Punkte genauso bei anderen Hot Block Situationen anwendbar.
Philip
