Warum ändert Oracle den Abfrageausführungsplan?
16/5/2024
Tomasz Chwasewicz
Statistiken sind erforderlich, damit der Oracle Optimizer den effektivsten Plan zur Abfrageausführung ermitteln kann. Sie sind eine Informationsquelle über die Verteilung und Eigenschaften von Daten in Tabellen und Indizes. Wenn sich Statistiken ändern — als Ergebnis von Aktionen wie dem Hinzufügen neuer Daten, dem Löschen vorhandener Datensätze oder der Durchführung geplanter Aktualisierungen — ändert sich auch die Sicht des Optimierers auf die Daten. Dies kann dazu führen, dass Oracle den aktuellen Ausführungsplan neu bewertet, was möglicherweise dazu führen kann, dass Oracle den Ausführungsplan ändert.
Warum ist das wichtig? In der Praxis können häufige Änderungen der Statistiken zu erheblichen Schwankungen der Abfrageleistung führen. Das bedeutet, dass Datenbankadministratoren und Entwickler genau darauf achten müssen, wann und wie Statistiken aktualisiert werden. Es muss jedoch ein durchdachter Prozess sein, um eine stabile und vorhersehbare Abfrageleistung aufrechtzuerhalten. Eine regelmäßige Überwachung und Anpassung kann erforderlich sein, insbesondere wenn Daten stark belastet oder gelöscht werden. Vor allem, wenn Sie einen Leistungsabfall verhindern und sicherstellen möchten, dass die Ausführungspläne effizient bleiben.
Selbst wenn die Statistiken unverändert bleiben, kann sich die tatsächliche Verteilung der Daten in den Tabellen während dieser Zeit geringfügig ändern. Dies kann auf verschiedene Faktoren zurückzuführen sein, z. B. auf die Aktualisierung vorhandener Datensätze oder das Hinzufügen neuer Datentypen, die noch nicht enthalten sind. Dies wirkt sich auf Kardinalitätsschätzungen aus — Vorhersagen über die Anzahl der Datenzeilen, die wahrscheinlich von der Abfrage betroffen sein werden. Diese Vorhersagen sind für den Oracle-Optimierer von entscheidender Bedeutung, wenn er den effektivsten Ausführungsplan auswählt.
Warum ist es relevant? Kardinalitätsschätzungen beeinflussen Entscheidungen darüber, welche Indizes verwendet werden sollen, ob ein vollständiger Tabellenscan durchgeführt werden soll und wie Tabellen in Abfragen mit mehreren Tabellen verknüpft werden. Wenn die Annahmen des Optimierers zur Datenverteilung falsch sind, wählt er möglicherweise einen Plan, der nicht mehr optimal ist.
Durch die regelmäßige Überprüfung und Anpassung der Annahmen des Optimierers kann damit umgegangen werden. Dies kann häufigere Statistikaktualisierungen oder die Verwendung von Funktionen wie dynamischer Stichprobenerhebung beinhalten. Dadurch kann Oracle die tatsächliche Verteilung der Daten zur Laufzeit besser beurteilen. Wenn der Optimierer über den tatsächlichen Stand der Datenverteilung informiert wird, kann sichergestellt werden, dass er Entscheidungen auf der Grundlage der genauesten und aktuellsten Informationen trifft.
Parameter wie optimizer_mode bestimmen, wie der Optimierer an Ausführungspläne herangeht. Dieser Modus kann auf Werte wie folgt gesetzt werden ALLE_ZEILEN um die Abfrageleistung für alle zurückgegebenen Zeilen zu optimieren, oder ERSTE_ZEILEN um die Rücklaufgeschwindigkeit der ersten Linien zu priorisieren. Durch das Ändern dieser Parameter kann sich die Art und Weise ändern, wie Abfragen verarbeitet werden. Dies wirkt sich wiederum auf die ausgewählten Ausführungspläne aus.
Änderung anderer Parameter, wie optimizer_index_cost_adjdas die geschätzten Kosten für den Zugriff auf den Index anpasst, oder optimizer_index_caching, was sich auf den angenommenen Prozentsatz der Indexblöcke im Cache auswirkt, kann auch den Ausführungsplan drastisch ändern. Durch diese Änderungen wird die Art und Weise, wie der Optimierer verschiedene Ausführungsstrategien abwägt, neu kalibriert. In der Tat ändert Oracle den Ausführungsplan.
Variables Peeking binden ist eine Funktion, in der Oracle Optimizer die tatsächlichen Werte von Bind-Variablen überprüft, wenn die SQL-Anweisung zum ersten Mal ausgeführt wird. Ziel ist es, den effizientesten Ausführungsplan zu ermitteln und es dem Optimierer zu ermöglichen, einen auf Ihre Bedürfnisse zugeschnittenen Plan zu erstellen. Ein Plan, der die Besonderheiten der Daten berücksichtigt, auf deren Grundlage er operieren wird.
In der Regel fördert die Verwendung von Bindungsvariablen in SQL-Abfragen die Stabilität und Wiederverwendbarkeit des Plans, wodurch eine schwierige Analyse bei jeder Ausführung vermieden wird. Bei der ersten Ausführung kann der Optimierer jedoch die tatsächlichen Werte der Einschränkung „untersuchen“, was sich auf den ausgewählten Plan auswirken kann. Beispielsweise kann eine Abfrage, die eine Spalte mit einer stark verzerrten Datenverteilung filtert, zu unterschiedlichen Ausführungspfaden führen. Es hängt alles von dem Wert ab, der zum Zeitpunkt der Analyse verwendet wurde.
Das Ersetzen von Bindungsvariablen durch Literalwerte ändert diese Dynamik. Der Optimierer generiert einen Plan für jeden eindeutigen Satz von Literalwerten, was zu mehreren Plänen für im Wesentlichen dieselbe Abfrage führen kann. Dadurch kann zwar in bestimmten Fällen die Leistung optimiert werden, es kann aber auch den Analyseaufwand erhöhen und die Gesamtleistung verringern, da keine Pläne gemeinsam genutzt werden.
Wenn eine Abfrage zum ersten Mal ausgeführt wird, erstellt Oracle einen Standardausführungsplan, der auf den aktuellen Statistiken und den Anfangswerten der Bindungsvariablen basiert. Wenn eine Abfrage wiederholt mit unterschiedlichen Bindungsvariablenwerten ausgeführt wird, erfasst der Optimierer Leistungsdaten. Gleichzeitig wird bewertet, ob der ursprüngliche Plan noch wirksam ist oder ob Anpassungen erforderlich sind. Wenn signifikante Leistungsunterschiede festgestellt werden, kann der Optimierer mehrere Ausführungspläne für dieselbe Abfrage generieren, und jeder einzelne wird für einen bestimmten Bereich von Bindungsvariablenwerten optimiert.
Das passiert selten, aber es passiert - der Oracle-Optimierer ist nicht vor gelegentlichen Fehlern gefeit. Natürlich kann einer von ihnen die Ursache für eine unerwartete Ineffizienz der Ausführungspläne sein. Wenn sie auftreten, sei es aufgrund von Fehlern in der Optimierer-Codebasis oder aufgrund unvorhergesehener Interaktionen zwischen verschiedenen Teilen des Datenbanksystems, können sie suboptimale Pläne generieren. Solche Pläne nutzen verfügbare Indizes nicht effizient und führen keine kostspieligeren Operationen durch als nötig.
In diesen Fällen gibt es keine andere Lösung, als den Fehler zu identifizieren. Dies erfordert sorgfältige Tests und Beobachtungen, insbesondere wenn eine Abfrage, die zuvor effizient funktionierte, plötzlich ohne ersichtlichen Grund einen Leistungsabfall zeigt.
Die Eingabeaufforderungen in Oracle SQL-Abfragen dienen als Anweisungen für die Entscheidungen des Optimierers und setzen häufig seinen Standardentscheidungsprozess außer Kraft. Durch die direkte Einbettung von Hinweisen in eine SQL-Anweisung können Entwickler den vom Optimierer eingeschlagenen Weg beeinflussen, indem sie beispielsweise bestimmen, welche Indizes verwendet werden sollen, Join-Methoden diktieren oder die Reihenfolge der Tabellen-Joins festlegen.
Änderungen der Aufforderungen selbst können auch zu Änderungen der Umsetzungspläne führen. Beispielsweise kann das Entfernen eines zuvor enthaltenen Hinweises dazu führen, dass der Optimierer zu seinem ursprünglichen Plan zurückkehrt, der aufgrund der aktuellen Datenumgebung möglicherweise weniger effizient ist. Ebenso kann das Ändern von Hinweisen zur Berücksichtigung neuer Indexierungsstrategien oder anderer Verlinkungsansätze die Art und Weise, wie eine Abfrage ausgeführt wird, drastisch verändern.
Ohne Ausreichende Statistiken Der Optimierer ist möglicherweise nicht in der Lage, fundierte Entscheidungen über die besten Abfrageausführungspläne zu treffen. Wenn der Stichprobenumfang zu klein oder nicht repräsentativ für den gesamten Datensatz ist, geben die resultierenden Statistiken möglicherweise nicht genau die wahre Natur der Daten wieder.
Dies kann zu ungenauen Schätzungen der Kardinalität führen. Eine falsch geschätzte Kardinalität kann wiederum den Optimierer in die Irre führen, woraufhin Oracle den Ausführungsplan durch einen potenziell weniger effizienten Plan ändert. Ein Optimierer könnte sich beispielsweise dafür entscheiden, eine Tabelle vollständig zu scannen, wenn das Indexscannen effizienter wäre, oder er könnte Verknüpfungen aufgrund falscher Annahmen über die Größe und Verteilung der Daten falsch anordnen.
In Oracle-Datenbanken sind variable Datenwerte solche, die temporär sind oder sich schnell ändern können.
Wenn die Datenwerte in der Spalte variabel sind, geben die Optimizer-Statistiken möglicherweise nicht genau den aktuellen Status der Daten zum Zeitpunkt der Abfrageausführung wieder. Diese Diskrepanz kann zu einer Diskrepanz zwischen der erwarteten und der tatsächlichen Datenverteilung führen, sodass der Optimierer suboptimale Ausführungspläne auswählt. Ein für einen bestimmten Wertebereich optimierter Plan kann schlecht funktionieren, wenn sich diese Werte schnell und unvorhersehbar ändern.
Der Optimierer steht vor zusätzlichen Herausforderungen bei kurzfristigen Daten, bei denen Werte nur für kurze Zeit existieren können, bevor sie aktualisiert oder gelöscht werden. In solchen Fällen kann der Entscheidungsprozess des Optimierers, der auf Statistiken basiert, die in regelmäßigen Abständen erfasst werden, hinter dem tatsächlichen Datenszenario zurückbleiben, was zu häufigen Planänderungen führt. Der Grund dafür ist, dass der Optimierer versucht, seine Strategie neu zu kalibrieren, um sie an die neuesten Datenmerkmale anzupassen.
In Oracle-Datenbanken ist die asynchrone Statistikerfassung eine Methode, mit der Statistiken zur Datenverteilung erfasst werden, ohne den normalen Betrieb der Datenbank zu unterbrechen. Dieser Ansatz ist besonders in Umgebungen mit hoher Verfügbarkeit nützlich, in denen die Minimierung von Ausfallzeiten das Hauptziel ist. Aber es gibt immer ein „Aber“.
Die Verzögerung bei Statistikaktualisierungen aufgrund der asynchronen Erfassung bedeutet, dass der Optimierer bei der Bestimmung des besten Ausführungsplans möglicherweise nicht über die aktuellsten Daten verfügt. Dies kann dazu führen, dass Pläne ausgewählt werden, die nicht für die aktuelle Datenverteilung optimiert sind. Auf diese Weise erzielen wir eine ineffiziente Abfrageleistung.
