Grundlagen des kostenbasierten Optimizers

Jonathan Lewis hat für AllThingsOracle eine neue Serie Basics of the Cost Based Optimizer gestartet und liefert damit vermutlich eine neue Standardreferenz zum Thema. Allerdings weist der Autor darauf hin, dass die Serie eher einführenden Charakter besitzt und nicht allzu sehr auf die technischen Details eingeht, weshalb ich meine Zusammenfassungen eher knapp zu halten gedenke:

• Basics of the Cost Based Optimizer – Part 1: beschreibt die Grundlagen der Arbeit des Optimizers: um einen möglichst effektiven Zugriff zu ermöglichen erstellt er ein numerisches Modell basierend auf Informationen über den Daten (= Objektstatistiken), die Leistungsfähigkeit des Rechners (= Systemstatistiken) und die verfügbareb Ressourcen auf dem Rechner (= Konfiguration der Datenbank/Instanz). Dieses Modell ist natürlich nicht immer eine exakte Abbildung der realen Gegebenheiten und aus diesen Differenzen ergeben sich dann die Fehlentscheidungen des Optimizers. Dazu gibt es dann ein umfangreiches Beispiel eines Joins zweier Tabellen, die unter Verwendung unterschiedlicher Strategien miteinander gejoint werden können.
• Basics of the Cost Based Optimizer – Part 2: liefert Daten zu den in Teil 1 vorgestellten Objektdefinitionen (DDL) und zeigt, dass das Standardmodell relativ leicht zu Fehleinschätzungen führt, und wie man diese durch Konfigurationsänderungen korrigieren kann. Dabei enthalten die Daten eine Ungleichverteilung der Werte (skew). Im Beispiel werden die Optimizer-Kosten der Zugriffe sehr detailliert mit den zugehörigen I/O-Operationen auf Indizes und Tabellen verglichen. Zudem wird der durch ASM verzerrte Clustering Factor eines Index erläutert. Nach Korrektur des CF (durch Verwendung eines angemessenen table_cached_blocks-Parameters) verzichtet der Optimizer auf einen zunächst vorgesehenen Index Hash Join und verwendet stattdessen einen einfachen Index Range Scan mit folgendem Tabellenzugriff.
• Basics of the Cost Based Optimizer – Part 3: erläutert die Arbeitsweise und das Costing von Hash Joins und Nested Loops Joins. Im NL-Beispiel treten ausschließlich single block I/O Operationen auf, so dass die Reads im Plan mit rowsource statistics theoretisch exakt der cost Angabe entsprechen sollten: und das tun sie auch bei einer der beiden möglichen Reihenfolgen beim Tabellenzugriff, aber nicht bei der anderen, was mit dem Clustering der Daten zu tun hat (und der Effizienz des Cachings). Auch für den Hash Join ergibt sich eine sehr akkurate Cost-Angabe: in zwei der drei Test-Fälle ist das Modell also recht nah an der Realität. Aber im Fall von Nested Loops Joins kann der Optimizer die Caching Effekte nicht vorhersehen und neigt deshalb dazu, die Kosten für physical I/O zu überschätzen (was sich übrigens nicht solide durch die überholten Parameter optimizer_index_caching und optimizer_index_cost_adj korrigieren lässt).
• Basics of the Cost Based Optimizer – Part 4: erläutert die Rolle von Query Blocks und führt vor, welche Transformationen für ein Beispiel einer korrelierten Subquery möglich sind. Insbesondere weist er darauf hin, dass der Optimizer nicht immer die geeignetste Transformation auswählt, weshalb es wichtig ist, Einfluss auf die Umwandlungen nehmen zu können.
• Basics of the Cost Based Optimizer – Part 5: liefert zunächst eine knappe Zusammenfassung der älteren Artikel, da zwischen Teil 4 und Teil 5 der Serie ein gewisser Abstand lag. Im Anschluss werden die Begriffe cardinality (Anzahl Datensätze) und Selectivity (Anteil der Datensätze) eingeführt. Eine Hauptursache für ineffiziente Pläne ist das der Optimizer bei der Bestimmung der Selectivity oft keine präzise Informationsgrundlage hat - unter bestimmten Umständen kann er nur raten. Entsprechende Fälle sind:
• function(column) = constant: geschätzte Selectivity 1%. Korrigierbar mit virtual column.
• function(column) > constant: geschätzte Selectivity 5%. Beim range mit between wird daraus 5% * 5% = 0,25%. Korrigierbar mit virtual column.
• column = column: berücksichtigt die Anzahl distinkter Werte in beiden Spalten (1/greatest(num_distinct(col1), num_distinct(col2)). Korrigierbar mit dynamic sampling.
• combining columns: multipliziert die Selektivitäten, da der Optimizer unabhängige Werte erwartet. Korrigierbar mit extended statistics (column groups).
• Basics of the Cost Based Optimizer – part 6: beschäftigt sich mit vier Fällen, die in Teil 5 erwähnt, aber noch nicht erklärt worden waren. Ein erster Fall sind 5% Schätzungen für die Cardinality von Subqueries (mit einem "größer als" Prädikat), die man unter Umständen durch cardinality/opt_estimate Hints korrigieren kann. Ein zweiter Fall betrifft die Verwendung veralteter Statistiken, die einen unzutreffenden high_value etwa für Zeitangaben liefern, was durch häufiges Aktualisierung der Statistiken oder durch Setzen der high_values korrigiert werden kann. Die beiden letzten Fälle betreffen die Verwendung von Bind Variablen im Zusammenhang mit data skew, wobei einerseits die Begrenzung der maximalen Bucket-Anzahl auf 254 (in 11g) und die Verwendung von bind peeking eine Rolle spielen. Grundsätzlich gilt immer noch, dass Bind Variablen und Histogramme nicht gut miteinander harmonieren (weshalb der Einsatz von Literalen manchmal gar keine schlechte Idee ist).

Ich gehe davon aus, dass in der Serie noch weitere Artikel folgen werden, und diese werde ich dann voraussichtlich hier ergänzen.