Query Blöcke
Ich war bis jetzt der Meinung, dass ein Optimizer Hint nur innerhalb seines Query Blocks wirkt, außer man qualifiziert den Hint mit dem Query Block Namen [1].
Man kann beispielsweise Viewnamen in einem Hint nicht wirksam angeben.
In meiner Untersuchung zur Verbesserung des Statspack habe ich gesehen, dass das nicht immer stimmt und wollte diese genauer untersuchen.
Ausgangslage
Meine Testbeispiele erstelle ich im Scott Schema, so dass jeder meinen Test nachvollziehen kann.
Mittels des Optimizer Hints werde ich einen schlechten Plan erzwingen, damit eindeutig ist, dass der Optimizer den Plan unfreiwillig und durch den Hint gewählt hat.
Für die Tests habe ich Oracle Version 19c verwendet, um gleich die neue Hint Report Funktion nutzen zu können.
Die Pläne habe ich mit dem folgenden Statement angezeigt:
| select * from dbms_xplan.display_cursor(format=>’TYPICAL +hint_report‘) |
| ; |
Hier ist unser Beispiel Statement:
Ohne weitere Angabe erzeugt der Optimizer den folgenden Plan:
Versuchen wir es nun mit einem Hint.
In unserem Hint erzwingen wir, dass der Optimizer mit der Tabelle dept beginnt, was er freiwillig nicht machen würde.
Gegen meine Erwartung wird der Hint befolgt, obwohl er sich auf einen Alias in einem anderen Query Block bezieht.
Diesmal ist auch ein Hint Report dabei, da es einen Hint gibt.
Eigentlich hätte der Hint gar nicht funktionieren dürfen.
Der Hint befindet sich in der Hauptabfrage und referenziert ein Objekt in einem anderen Query Block.
Weshalb geht es wohl doch?
Wie man sieht wurde aber die Unterabfrage aufgelöst und mit der Hauptabfrage verschmolzen. Man spricht hier von einem Simple View Merging.
Ein Simple View Merging ist eine sogenannte Transformation. Der Optimizer schreibt die Abfrage um.
Durch die Transformation gibt es nur noch einen Query Block für die Abfrage.
Das könnte der Grund sein, dass der Leading Hint funktioniert.
Wird der obige Hint noch wirken, wenn man die Transformation verbietet?
In der Tat wird der Leading Hint jetzt nicht mehr befolgt.
Das ist typisch für das nicht funktionieren von Hints. Die Transformation erfolgt vor der Optimierung.
Dadurch werden zwei unterschiedliche Query Blocks erzwungen.
Das direkte Referenzieren eines anderen Query Blocks funktioniert dann nicht mehr.
Der Hint Report zeigt ebenfalls an, dass der Leading Hint nicht befolgt wird.
Im Statspack sah ich aber diese Variante des Hints:
Das Qualifizieren mit dem Alias der Unterabfrage sollte eigentlich nicht funktionieren.
Der Alias der Unterabfrage ist kein Query Block Name.
Jedoch, es geht trotzdem:
Fazit
Die Möglichkeit mit dem Alias einer Subquery zu Hinten erleichtert das Hinten von komplexen Abfragen.
Aufgrund des Hint Reports kann man sehen, dass der Hint korrekt verstanden wird.
Wie so vieles im Kontext von Hints ist auch diese Option nicht dokumentiert.
Ich habe die Abfrage von Version 11.2.0.4. bis 19.0 getestet und sie funktionierte immer.
Statt mit einer Unterabfrage in der From Klausel funktioniert es auch mit einer View.
Jedoch muss auch hier, wenn die View einen Alias bekommt, der Alias im Hint verwendet werden.
Jonathan Lewis hat mich darauf hingewiesen, dass die Art von Hints offiziell erlaubt ist.
Es handelt sich um global table hints [3].
Diese besonders nützliche Art des Hints ist leider noch zu wenig bekannt. Jedenfalls habe ich sie in meiner Praxis noch nicht angetroffen.
Quellen
Colgan, Maria, What are Query Block Names and how to find them, https://sqlmaria.com/2020/03/10/what-are-query-block-names-and-how-to-find-them/
Flatz, Lothar, Statspack verbessern, https://www.dbconcepts.at/oracle-statspack-verbessern-schnellere-snapshots-teil1/
Oracle, Specifying Global Table Hints, https://docs.oracle.com/cd/B13789_01/server.101/b10752/hintsref.htm#27647
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.
