Ausgangslage: Langsamer Statspack Snapshot
Auf verschiedenen Standard Edition Datenbanken sieht man immer wieder den Statspack Snapshot lange laufen.
Dies ist für mich als Consultant sehr unangenehm.
Oft muss ich den Kunden sagen, dass das Statspack auf einer Standard Edition Datenbank unverzichtbar ist.
Und dann sieht mein Kunde, wie der Statspack Snapshot minutenlang einen der kostbaren, limitierten Cores blockiert.
Ich beschloss, etwas dagegen zu unternehmen.
Bei näherer Betrachtung erkennt man zwei Statements, die die Datenbank belasten.
In erster Linie ist dies:
Aber auch dieser Befehl läuft lange:
Grundsätzliches: Hints im Statspack
Man hört oft, dass man Hints so wenig wie möglich machen soll und lieber andere Mittel verwenden soll, um den Optimizer zu steuern.
Gute Statistiken zum Beispiel.
Ich bin auch dieser Meinung.
Allerdings gibt es auch Ausnahmen.
Bei Tools wie Statspack kann man sich nicht darauf verlassen, dass die Statistiken immer aktuell sind.
Es werden auch interne Tabellen X$ verwendet. Nicht jeder DBA macht fixed table stats und selbst wenn, schwanken diese Statistiken oft stark.
Ein Tool wie Statspack muss immer zuverlässig laufen.
Glücklicherweise ist bei den zwei Statspack Queries die wir betrachten werden, ziemlich klar wie der Plan auszusehen hat.
Hints stellen also kein großes Risiko dar. Jedoch habe ich dennoch so wenig wie möglich festgelegt.
Statement : INSERT INTO stats$sql_plan
Das Problem wird in der MOS Note 2182680.1 behandelt.
In dieser Note wird vorgeschlagen eine alternative Implementation des Statspack Package (SCPPKG.SQL) herunter zu laden und zu implementieren.
Diese Implementation enthält einen geänderten Hint, den ich im folgenden Beispiel mit Rot hervorgehoben habe.
Vom ganzen Insert Statement zeige ich nur den SELECT Teil, weil der Eintrag sonst zu lange wird.
Auch vom Select Teil habe ich die Liste der Spalten gekürzt, damit der Code Teil übersichtlich bleibt.
Wie unschwer zu erkennen ist, befinden sie relevanten Suchkriterien auf der Tabelle stats$sql_plan_usage mit dem Alias spu.
Es ist also wichtig, dass im Execution Plan mit dem Lesen dieser Tabelle begonnen wird.
Der Hint Leading(new_plan .. ist also folgerichtig.
Das Problem besteht jedoch darin, dass new_plan keine Tabelle, sondern der Name einer Unterfrage ist.
Obwohl Version 19c den Hint als korrekt meldet, wird er in tieferen Versionen oft nicht akzeptiert.
Der Alias für den Hint wäre eigentlich spu, jedoch ist dieser in der Hauptabfrage nicht zugänglich, weil die Tabelle stats$sql_plan_usage in einer Unterabfrage angesprochen wird. (Achtung: wenn ein Alias vorhanden ist, muss der Alias im Hint angegeben werden und nicht der Tabelle Name.)
Diese Unterabfrage bildet einen eigenen Queryblock und dessen Inhalt ist von der Hauptabfrage nicht direkt referenzierbar.
In der Tat zeigt der Plan des Insert Befehles, dass der Leading Hint ignoriert wird.
Hier ein Beispiel von einem unserer Kunden:
Wie man sieht, benötigt der Insert 5 Minuten und 8 Sekunden.
Queryblöcke
Um eine Queryblock in der Hauptabfrage referenzieren zu können, muss man dem untergeordneten Queryblock mit dem QB_NAME hint einen Namen geben.
Dann kann man die Tabellen des untergeordneten Queryblocks mittels „tabellenalias@queryblock“ ansprechen.
Dies ist ein dokumentiertes Vorgehen und sollte in allen Versionen stabil funktionieren.
In unserem Beispiel sieht das so aus:
Diesmal hält der Optimzer sich an die Hints, das Ergebnis sieht wie folgt aus:
Also 5 hundertstel Sekunden statt 5 Minuten.
Das ist über 6000x schneller und kann sich sehen lassen.
Nachdem ich die Hints gefunden hatte, habe ich eine Sicherheitskopie des Scriptes SCPPKG.SQL angelegt und den neuen Hint in das Package kopiert.
Durch den Aufruf des Scriptes wurde das neue Package dann installiert.
Statement: INSERT INTO stats$seg_stat
Wie sieht nun das zweite Insert aus?
Diesmal gibt es keine MOS Note.
Auch hier zeige ich wieder den sql code, mit den hervorgehobenen Hints:
Wieder sieht man hier ein sehr ungewöhnliches Hint Format.
Wieder meldet aber die Report Funktion in 19c keinen Fehler und in unseren Test hat der Optimizer den Hint befolgt.
Jedoch bin ich diesmal mit der Wirkung des Hints nicht einverstanden.
Sehen wir uns dazu Laufzeitstatitiken an:
Immerhin läuft auch dieser Befehl noch über eine Minute.
Die Zeit fällt fast zu 100% in der Zeile 7 an und zwar deshalb, weil die Zeile 747 Mal wiederholt wird.
Ein Hash join wäre hier wesentlich Laufzeit stabiler.
Der Nested Loop join hat Vorteile bei kleinen Datenmengen.
Allerdings spielt es ohnehin keine Rolle, welchen Join man nimmt, bei kleinen Datenmengen sind alle schnell.
Ich habe also den use_nl hint gegen einen use_hash hint getauscht.
Das ist das Resultat:
Insgesamt läuft der Snapshot jetzt in 6 Sekunden durch.
Mag. Lothar Flatz, Senior Principal Consultant @DBConcepts.
Unser Oracle Mastermind kam 1989 erstmals mit Oracle in Berührung. Mittlerweile verfügt er über enormes Wissen und Erfahrung (15 Jahre als Oracle Mitarbeiter). Er war Mitglied in der Real World Performance Group und ist Mitglied im exklusiven Oaktable* und Oracle ACE*. Als einer der wenigen verfügt er über ein Patent zur Verbesserung des Optimizers. Er arbeitete an der Software für den LHC bei CERN in Genf mit. Sein Wissen und seine Erfahrung teilt er gerne als Autor für diverse Informatik Fachmagazine und als Sprecher bei einschlägigen Veranstaltungen (IT-Tagen, DOUG, AOUG, RMOUG, PGDay, POUG, OUGN uvm.). Aber auch als Dozent auf der TU Wien oder hier im Blog.
*Oaktable Member: 100 Mitglieder weltweit. Ausschließlich auf Einladung.
*Oracle ACE: Weltweit 500. Von Oracle für besondere Leistungen ernannt.
