UNDO Monitoring

Teil 4: UNDO Monitoring

Der Undo Tablespace, der mit Oracle 9 mit dem automatischen Undo eingeführt wurde, besitzt nur Undo-Segmente. Die verwalten sich selbst und legen daher ein von außen schwer diagnostizierbares Verhalten an den Tag. Eine simple Überprüfung des freien Platzes im Tablespace würde viel zu pessimistisch sein, weil sich innerhalb jedes Undo-Segments üblicherweise freier Platz befindet, der von DBA_FREE_SPACE nicht erfasst wird.

Stattdessen gibt es die wunderbare View V$UNDOSTAT, die nicht nur den benötigten Platz in 10-Minuten Intervallen ausgibt, sondern auch vernünftige Prognosen ermöglicht.

Was steht drin?

	SQL> SELECT TO_CHAR(end_time, 'yyyymmdd hh24:mi:ss') AS ende, undoblks, unxpstealcnt FROM v$undostat;

	ENDE              UNDOBLKS   UNXPSTEALCNT
	----------------- ---------- ------------
	20180520 10:56:05       2510            0
	20180520 10:52:36       5195            0
	20180520 10:42:36       6822            0
	20180520 10:32:36       4994            0
	20180520 10:22:36       5064            0
	20180520 10:12:36       5510            0
	20180520 10:02:36       6123            0
	...

ok, was bedeutet das nun?

Die Intervalle sind deutlich erkennbar 10 Minuten lang (mit Ausnahme des letzten). Die Spalte UNDOBLKS sagt, wieviele Blöcke in diesem Interval geschrieben wurden. Bei einer (angenommenen) durchschnittlichen Anzahl von 5000 Blöcken, kann man auslesen, dass für eine UNDO_RETENTION von 2h folgende Rechnung gilt:

2 Stunden sind 12 10-Minuten Intervalle, also 12*5000 Blöcke = 12*5k*8k = 480M

Mit 500MB wäre der Undo-Tablespace ein wenig knapp, eher 10%-20% mehr, also 600MB.

Woher wissen wir aber, ob 5000 Blöcke/10 Minuten wirklich stimmt?

Man könnte eigentlich jene 12 aufeinander folgende Intervalle suchen, die in Summe die meisten Blöcke verbrauchen, um auf der sicheren Seite zu sein:

SELECT TO_CHAR (end_time, 'yyyymmdd hh24:mi:ss') AS ende,

       AVG(undoblks) OVER (ORDER BY end_time ROWS 11 PRECEDING)

   FROM v$undostat

   ORDER BY 2 DESC FETCH FIRST 1 ROWS ONLY;

Diese Query liefert den größten Mittelwert über 12 Intervalle und auch gleich das Datum mit, wann dieser Spitzenintervall sein Ende hatte.

Was hat es jetzt aber mit der Spalte UNXPSTEALCNT auf sich?

Wenn Undo-Segmente Platz benötigen (also um ein Extent wachsen müssen), holen sie sich das Extent vom Tablespace. Hat der nichts mehr frei, "borgen" sie sich ein Extent, dessen Inhalt die UNDO_RETENTION schon überschritten hat, von einem anderen Segment aus. Gibt es das auch nicht, dann "stehlen" sich die Segmente gegenseitig Extents, die noch nicht abgelaufen sind - jetzt wird der Zähler UNXPSTEALCNT erhöht, denn das ist ein unexpired steal. Diese Situation bedeutet, dass die UNDO_RETENTION nicht eingehalten werden kann und daher mehr Platz (oder eine kleinere Retention) vonnöten wäre.

Für das Monitoring reicht es, diesen Zähler zu überwachen, ob er eh brav auf Null bleibt.

Breadcrumb