Fünf wichtige Optimizer Hints

Jonathan Lewis hat in seinem Blog eine Zusammenfassung der wichtigsten Punkte seiner DOAG-Präsentation Five Hints for Optimising SQL veröffentlicht (was ich sehr erfreulich finde, da ich den Vortrag natürlich nicht gehört habe, so wie alle Vorträge, die in den letzten zehn Jahren bei nationalen und internationalen Konferenzen gehalten wurden). Dabei beginnt er mit dem üblichen Hinweis, dass Hints normalerweise im Produktivsystem zu vermeiden und als Ultima Ratio zu betrachten sind. Und dass von den 332 Hints, die in 12.1.0.2 zur Verfügung stehen, nur eine Handvoll tatsächlich eingesetzt werden sollten, wenn es sich nicht vermeiden lässt. Aus dieser Menge nimmt er dann noch einmal fünf Hints heraus, die er als relativ nützliche Hilfsmittel ansieht, um den Optimizer bei seiner Entscheidungsfindung zu unterstützen. Zu diesen fünf Hints liefert er dann jeweils eine längere Erklärung, die im Rahmen eines komplexeren Beispiels erscheint. Ich versuche, hier hier die initiale knappe Definition mit den folgenden Erklärungen zusammenzuführen. Wie übliche werde ich dabei zweifellos zentrale Punkte unterschlagen...

• Merge / no_merge — "Whether to use complex view merging". Der Hint kann aber auch verwendet werden, um einzelne Abschnitte einer komplexen Abfrage voneinander zu isolieren. Dadurch kann man einerseits den Suchraum des Optimizers reduzieren und andererseits manchmal auch verhindern, dass der Optimizer in einem bestimmten Bereich der Query eine ungeeignete Strategie wählt. Im Beispiel wird neben den no_merge Hint noch ein cardinality Hint gestellt, der zwar nicht vollständig dokumentiert ist, aber dazu verwendet werden kann, die cardinality eines Query Blocks anzugeben. Ein Einsatzgebiet des Hint-Paars ist auch der Wechsel zwischen den Möglichkeiten "join then aggregate" und "aggregate then join".
• Push_pred / no_push_pred — "What to do with join predicates to non-merged views". Mit Hilfe dieser Hints kann man festlegen, ob eine Join-Bedingung in eine (nicht im Rahmen der Transformation in einen Join umgewandelte) Subquery propagiert werden soll. Das kann sinnvoll sein, wenn sich dadurch eine effektivere Zugriffsstrategie ergibt (etwa durch einen indizierten Zugriff).
• Unnest / no_unnest — "Whether or not to unnest subqueries". Normalerweise wandelt der Optimizer Subqueries gerne in Joins um (Semi-Join für exists; Anti-Join für not exists). Da eine Subquery in solchen Fällen oft besser zu verstehen ist, kann ein Hint an dieser Stelle dafür sorgen, dass der Optimizer auch dann die Join-Variante wählt, wenn das (fehleranfällige) Costing nicht für diese Strategie sprechen würde. Im Rahmen des Beispiels weist der Autor darauf hin, dass das Costing in seinem Beispiel (ohne Unnest) tatsächlich die Anzahl der Ausführungen einer Subquery berücksichtigt (Anzahl Departments), was eine jüngere Errungenschaft des CBOs ist.
• Push_subq / no_push_subq — "When to handle a subquery that has not been unnested". Über diesen Hint kann man beeinflussen, wann eine Subquery ausgeführt wird - traditionell wurde sie erst gegen Ende der Verarbeitung berücksichtigt. Das Pushing einer Subquery bedeutet, sie im Plan nach unten zu schieben, also früher auszuführen.
• Driving_site — "Where to execute a distributed query". Dieser Hint hat keine NO-Variante, weil er nur angibt, auf welcher Seite einer verteilten Query die Ausführung koordiniert wird und welche Seite als remote fungiert. Der Hint fünktioniert übrigens nicht für CTAS und Insert as Select Queries, was nicht unbedingt nachvollziehbar ist. In solchen Fällen kann die Verwendung einer pipelined table function sinnvoll sein.

Mal wieder eine sehr nützliche Zusammenfassung, die ich in Zukunft vermutlich häufiger als Referenz verwenden werde.