Grundlegendes: Ändern des Statspack Code
Natürlich sollte man den Statspack Code nicht leichtfertig ändern. Das wird Niemand wollen. Die hier besprochene Änderung erlaubt es, schnell und ohne großes Nachdenken einen Plan aus der Vergangenheit wieder her zu stellen.
Für manche Applikationen und Datenkonstellationen ist diese Möglichkeit wirklich wichtig.
In diesem Fall sollte man es sich gründlich überlegen, ob man die hier beschriebene Veränderung nicht doch machen will.
Schließlich hat man den Source Code des Statspack. Man sollte die originale Codeversion behalten und kann daher jederzeit den Originalcode schnell wiederherstellen. Die zusätzliche optionale Spalte stört da nicht
Ausgangslage
Bei meinem Webinar „The good plan and the bad Plan” für die DOAG ging es darum, gute Pläne aus der Vergangenheit wieder zu aktivieren.
Kann man die AWR Daten lesen, so kann man beispielsweise aus den DBA_HIST Daten mittels dbms_xplan.display_awr ein Outline erzeugen. Dieses Outline kann man dann in aktuelle Pläne einpflanzen.
Ich wurde damals gefragt, ob eine solche Methode auch auf einer Oracle Standard Edition Datenbank funktioniert.
Mit dem SQL_PATCH [2] gibt es eine legale Möglichkeit, ein Outline in einen SQL Befehl einzupflanzen.
Die Frage ist nur, wie man auf einer Oracle Standard Edition zu einem Outline eines alten Planes kommt.
Gibt es mit Statspack eine Funktion ähnlich dem dbms_xplan.display_awr?
Uwe Küchler, alias Oraculix hat eine sehr kreative Lösung parat [1].
Man verwendet einfach dbms_xplan.display im Zusammenhang mit einer Suchfunktion:
Man muss dazu noch eine Spalte in der Tabelle stats$sql_plan ergänzen, die Einzelheiten finden Sie im Oraculix Blog.
Was passiert nun, wenn man jetzt einfach im Format noch +OUTLINE angibt?
Dann müsste man doch das Outline bekommen. Da könnte man dann über einen SQL_PATCH in ein Statement schreiben und voila, hat man den alten Plan zurück.
Oder etwa nicht?
Nein, die Format Anweisung wird scheinbar einfach ignoriert.
Der Grund hierfür ist, dass in den Statspack Plänen einige Spalten fehlen. Konkret geht es hier um die Spalte OTHER_XML, die nicht mitgespeichert wird.
Die Spalte other_xml
In dieser Spalte findet sich unter anderen das Outline. Wer also die sehr wichtige Funktion der historischen Pläne haben will, darf nicht davor zurückscheuen, den Statspack code zu verändern. Wenn Oracle schon die der MOS Note 2182680.1 vorschlägt das Statspack zu verändern, weshalb sollte wir nicht ein wenig weiter gehen?
Wir beginnen, in dem wir die Spalte hinzufügen.
Danach müssen wir den Code anpassen. Analog zum Vorgehen in MOS Note 2182680.1 habe ich zuerst eine Kopie des aktuellen Codes gemacht.
Wieder ist es das Skript spcpkg, welches angepasst werden muss.
Zu ändern ist das Insert Statement beginnend mit:
Hier muss die Spalte Other_XML ergänzt werden. Dabei gibt es jedoch eine Schwierigkeit.
Im Select Teil des Inserts steht eine Maximum Funktion, z.B. so:
Die Maximum Funktion ist ziemlich sicher nur dafür da, um duplizierte Sätze zu vermeiden.
Also schreiben wir doch einfach: Max(other_xml), oder?
Prompt kommt ORA-00932. Der Grund dafür ist, dass CLOB nicht sortierbar sind, daher kann auch kein Maximum berechnet werden.
ANY_VALUE
Natürlich gäbe es noch andere Möglichkeiten, um die Werteliste eindeutig zu machen.
Beispielsweise analytische Funktionen wie row_number ().
Ich wolle jedoch nicht so viel am Original Code verändern.
Mir fiel eine Idee aus dem Oracle Technologie Network ein, bei der es darum ging, für genau solche Probleme eine neue Art von Gruppenfunktion ein zu führen, genannt ANY_VALUE [3.].
Die Idee wird anscheinend mit Version 20c umgesetzt. Zu schade. Mit Version 19 wäre besser.
Als ehemaliger Oracle Mitarbeiter weiß ich jedoch, dass manchmal die Vorgängerversionen undokumentiert schon Änderungen der Folgeversion enthalten.
Und in der Tat: any_value gibt es schon.
Leider wirft auch diese Implementation völlig unnötig ORA-00932.
Ich habe Chris Saxon darauf aufmerksam gemacht und hoffe das Oracle die Implementierung noch ändert.
Nun, wenn Oracle nicht liefert muss ich das selbst machen.
Ich habe mir über das User-Defined Aggregate Functions Interface die Funktion selbst definiert und any_lob genannt.
Den Code finden sie am Ende dieses Blogs. Hier erst einmal der vollständige Insert Befehl. Die alle vom Standard abweichenden Änderungen sind mit Rot gekennzeichnet. Die Hints habe ich im vorigen Blog zum Thema Statspack beschrieben.
Zusammenfassung
Der Code scheint recht gut zu funktionieren.
Nur eine Sache war etwas ärgerlich: Immer, wenn ich Syntaxfehler im Insert hatte, musste ich auch die Funktion any_lob neu kompilieren.
Hinweis: Man muss den Snap Level auf 6 oder höher schalten, damit das Statspack Pläne sammelt. Z.B.:
Quellen
[ 1 ] Küchler, Uwe, Historical SQL Plan from Statspack using DBMS_XPLAN in 12c, https://oraculix.com/2016/06/21/historical-sql-plan-from-statspack-using-dbms_xplan-in-12c/
[ 2 ] Colgan, Maria, Using SQL Patch to add hints to a packaged application, https://blogs.oracle.com/optimizer/using-sql-patch-to-add-hints-to-a-packaged-application
[ 3 ] Gelling, Kevin, Any_value Set function, https://community.oracle.com/ideas/13039#comment-1069168
[ 4 ] Oracle , User-Defined Aggregate Functions Interface, https://docs.oracle.com/database/121/ADDCI/ext_agg_ref.htm#ADDCI4220
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.
