Cost Based Optimizer - macht er was er will?

Cost Based Optimizer - macht er was er will?
Ing. Christian Pfundtner
GNC Global Network Communication GmbH
Wien
Schlüsselworte
RBO – Rule Based Optimizer, CBO – Cost Based Optimizer, Geschichte, Irrtümer und
Fakten, CBO Neuheiten von Oracle 8 bis 11g, Optimizer auf die Finger sehen, Optimizer
verstehen.
Einleitung
Der Rule Based Optimizer ist ab Oracle 10g offiziell de-supported. Obwohl der Cost Based
Optimizer schon mit Oracle 7 eingeführt wurde, wird er laufend verbessert, erweitert und
modifiziert. Diese Dynamik beim CBO führt dazu, dass viele Entwickler den CBO lange Zeit
ignoriert und missverstanden haben. Mit der Zeit haben sich viele Irrtümer und Vorurteile in
den Köpfen festgesetzt. Einige davon sind sicher auch durch Oracle selbst verschuldet, da der
CBO bei seiner Einführung leider keinen sehr guten Job gemacht hat.
Dieser Vortrag versucht einerseits die Geschichte des Optimizers aufzuzeigen und dabei die
Neuheiten von Version 8 bis 11g näher zu bringen und andererseits anhand eines Beispiels
und vielen Informationen ein, Basiswissen zu vermitteln um das Verständnis für den CBO zu
verbessern.
Die Geschichte des Optimizers
Die Anfänge von Oracle – der RBO
In den Anfängen von Oracle (vor über 30 Jahren) stellten die vorhandenen Ressourcen
(Server mit CPU im kHz Bereich, 64k-128k Hauptspeicher) einen limitierenden Faktor dar,
der dazu zwang, einen Execution Plan für ein Statement mit möglichst geringem
CPU/Memory Bedarf zu ermitteln. Die Anfänge des RBO waren somit geboren. Mit
einfachsten Regeln wurde festgelegt, wie auf die Daten zugegriffen werden soll. Da sich die
Datenmengen im MB Bereich (pro Tabelle) abgespielt haben, war ein nicht optimaler
Zugriffsplan potentiell nicht so problematisch wie heutzutage, wo Tabellen im TB Bereich
existieren.
Im Laufe der Zeit standen immer mehr CPU Leistung und Hauptspeicher zur Verfügung, und
auch die Datenmengen wuchsen (auf GB pro Tabelle). Vor rund 20 Jahren – Oracle 6 war
gerade neu auf dem Markt – haben die Entscheider bei Oracle festgestellt, dass der RBO die
neuen Herausforderungen nicht abdecken kann. Ein neues Konzept musste her, und die Idee
des CBO wurde geboren.
Der CBO erblickt mit Oracle 7 das Licht der Welt
Oracle 7 brachte viele Neuheiten. Unter anderem auch die Einführung des CBO. Zu diesem
Zeitpunkt waren die Entwickler bei Oracle so sehr vom CBO überzeugt, dass im ReadMe der
Datenbank folgender Satz zu finden war „The RBO will be de-supported with a near future
release.“. Inzwischen ist der RBO auch wirklich de-supported seit Oracle 10g, also rund zwölf
Jahre nach Oracle 7.
Was war die Ursache dafür, dass der CBO sich so lange nicht durchsetzen konnte?
In den ersten Jahren war der CBO wirklich nicht besonders gut. Er lag mit seinem Execution
Plan oft genug weit daneben. Ein anderer wichtiger Grund war, dass viele Applikationen für
den RBO optimiert waren (und bis heute so optimiert werden) und diese Optimierungen es
dem CBO teilweise unmöglich machten, einen sinnvollen Execution Plan zu erzeugen. Das
größte Problem ist allerdings bis heute nicht gelöst. Vielen Entwicklern und Datenbank
Administratoren fehlt einfach das Wissen, was den CBO beeinflusst, worauf man achten muss
und wie man bestehende Performance Probleme angehen und neue präventiv vermeiden kann.
Abb. 1: der Optimizer über die Zeit
Irrtümer und Fakten
In den letzten Jahren habe ich sehr viele Schulungen, Consulting Einsätze und auch Vorträge
gehalten, und dabei bin ich mit extrem vielen Irrtümern, Vorurteilen und verdrehten Fakten
konfrontiert worden. Einige der Highlights sind die folgenden:
Wir verwenden RBO mit Hints
Aussage eines Entwicklungsleiters einer größeren Firma mit einer beachtlichen Anzahl von
Entwicklern (zumindest für österreichische Verhältnisse): „Bei uns in der Entwicklung wird
ausschließlich der Rule Based Optimizer mit zusätzlichen Optimizer Hints verwendet.“ Im
ersten Moment war ich sprachlos und es hat mich anschließend einiges an Diskussion und
Beweisen gekostet, bis ich die wahren Fakten vermittelt hatte: Sobald man Optimizer Hints
verwendet (ausgenommen RULE und INDEX_DESC), schaltet Oracle automatisch den Cost
Based Optimizer (Setting: ALL_ROWS) ein. Da natürlich keinerlei Statistiken oder passende
Instance Konfigurationsparameter gesetzt waren, musste man dem CBO mit Hints praktisch
auf einen Execution Plan nötigen. Gemeinsam mit dem Kunden haben wir Statements
gefunden, die bis zu 20 Hints benötigt haben um zu funktionieren.
Wir verwenden CBO ohne Statistiken
Bei einem anderen Kunden wurde ich von der Aussage: „Wir verwenden den CBO, allerdings
ohne Statistiken (diese wurden in einem Job automatisch gelöscht, wenn doch einmal eine
entstanden ist).“ konfrontiert.
Der Grund für diesen Ansatz war: Wenn es keine Statistiken gibt, können sich diese nicht
ändern, so wird immer der gleiche Execution Plan generiert. Notfalls kann man ja mittels
Hints dafür sorgen, dass der CBO macht was man will.
Nun, auch dieser Ansatz ist nicht im Sinne des Erfinders. Wenn man dem CBO wirklich
konsequent alle Informationsquellen abschaltet (und dazu muss man in aktuellen Versionen
wirklich einiges machen), verfällt der CBO auf „Voreinstellungen“, die zwar genau
genommen nichts mit dem RBO zu tun haben, aber in sehr vielen Fällen zum gleichen
Ergebnis wie der RBO gelangen. Manche Kollegen nennen diesen Betriebsmodus „RBO
Mode“.
Warum ist dieser Ansatz nun so schlecht? Man reduziert die Möglichkeiten des CBO auf ein
Niveau, das dem RBO entspricht. Viele Funktionalitäten, die Oracle dem CBO mitgegeben
hat, gehen dadurch verloren (fast alle Neuheiten ab Oracle 8i sind dadurch nicht nutzbar).
Dadurch vergibt man sich die Chance vom CBO zu profitieren.
Man muss in Oracle 9i auf CBO umsteigen bevor man auf 10g umsteigt
Einige Kunden hatten (bzw. haben immer noch) den Stress, dass Ihre Applikationen für RBO
optimiert sind und Sie immer noch auf Oracle 9iR2 Datenbanken, die schon länger nicht mehr
supported sind, festsitzen. Genau genommen haben sie mit dieser Vorgangsweise sogar recht,
denn in Oracle 10g ist der RBO de-supported. Die Frage ist aber, ist es sinnvoll an einer
Version, die de-supported ist, festzuhalten, oder lässt man sich darauf ein, ein de-supported
Feature zu nutzen?
Wir haben einen Kunden bei der Umstellung von RBO auf CBO unterstützt, der zuerst auf
Oracle 9iR2 zum CBO wechseln und erst im zweiten Schritt auf Oracle 10g umsteigen wollte.
Es gelang uns aber, den Kunden zu überzeugen, die Migration gleichzeitig auf einer weiteren
Oracle 10gR2 Datenbank zu verifizieren. Während der ganzen Umstellung gab es keine
Probleme mit der Applikation mit RBO auf Oracle 10gR2. Wir mussten in Oracle 9iR2
jedoch viel mehr Statements manuell tunen, als dies bei der Oracle 10gR2 nötig war. Kurzum,
am Ende des Projekts stand fest: Es wäre problemlos möglich gewesen, zuerst auf Oracle
10gR2 mit RBO umzusteigen und dann auf CBO zu migrieren. Die Umstellung auf CBO in
Oracle 10gR2 ist deutlich einfacher als unter 9iR2, weil Oracle den CBO ja weiterentwickelt
hat.
CBO Neuheiten von Oracle 8 bis 11g
Ab Oracle 8 hat Oracle massiv an der Verbesserung der Ergebnisse des CBO gearbeitet, so
dass man bei vielen Applikationen eine Umstellung von RBO auf CBO in Oracle 9i oder 10g
umsetzen kann.
Oracle 8/8i
Die Oracle 8/8i Erweiterungen des CBO bestehen zum großen Teil aus der Unterstützung der
objekt-relationalen Funktionalitäten, der Partitioning Option und der Einführung von Query
Re-Write für MViews. Allerdings hat Oracle auch die eine oder andere interessante Neuerung
vorgenommen, die einige Probleme mit dem CBO gelöst haben. Die Highlights dabei sind
„Complex View Merging“, „Push-Join Predicates“, sowie „Ordered Nested Loop Costing“.
•
o
•
o
•
o
o
o
o
•
o
o
o
o
•
o
o
•
o
o
o
o
o
•
o
o
o
Allgemeine Optimizer Features ab Oracle 8
Support für User Defined Data Types
Ab Oracle 8.0
Index fast full scan
Ab Oracle 8.0, ausgenommen Oracle 8.1.5
Consideration of bitmap access paths for tables with only B-tree indexes
Complex view merging
Push-join predicate
Ordered nested loop costing
Allgemein ab Oracle 8i
Support für partitionierte Objekte
Support für partitioning pruning bei Joins
Support für Query Re-Write (Oracle 8.1.6)
Support für Function Based Index
Ab Oracle 8i
Improved outer join cardinality calculation
Improved verification of NULLs inclusion in B-tree indexes
Ab Oracle 8.1.6
Random distribution method for left of nested loops
Type-dependent selectivity estimates
Setting of optimizer mode for user recursive SQL
Improved average row length calculation
Partition pruning based on subquery predicates
Ab Oracle 8.1.7
Common sub expression elimination
Use statistics of a column embedded in some selected functions such as
TO_CHAR to compute selectivity
Improved partition statistics aggregation
Oracle 9i
In dieser Version bestechen die neuen Möglichkeiten von „Index Joins“ und das umstrittene
„Bind Variable Peeking“ hervor. Bei „Index Joins“ wird versucht, den Zugriff auf die Tabelle
zu umgehen, indem zwei (oder mehre) Indizes „gemerged“ (über die Row-ID verknüpft)
werden. Oracle wählt diesen Weg, wenn es selektive Bedingungen in der WHERE Clause
gibt und keine weiteren Spalten aus der Tabelle benötigt werden. Beim „Bind Variable
Peeking“ trickst der Optimier, indem er beim Parsen den Inhalt der Bind Variablen „ansieht“
und somit einen Execution Plan ermittelt, der für diesen Inhalt optimal ist. Leider ist das
Ergebnis nicht immer positiv. Wenn das Statement mit atypischen Variableninhalten geparsed
wird, ist der so entstandene Execution Plan für typische Variableninhalte meist nicht sinnvoll.
Erst mit Oracle 11g und dem „Adaptiven Cursor Sharing“ löst Oracle dieses Problem.
•
o
o
o
o
•
o
o
o
Allgemein ab Oracle 9i
Laufende Verbesserung bei MVIEW und Query Re-Write
Neue Partitioning Möglichkeiten
Ab Oracle 9.2 werden CONNECT BY Queries anders aufgelöst
Verbesserung bei Inline Views (Inline Views everywhere)
Ab Oracle 9iR2
Peeking at user-defined bind variables
Index joins
Subquery unnesting
Oracle 10g
Mit Oracle 10g und der DBconsole bringt Oracle auch einige neue Tuning Möglichkeiten
(SQL Tuning Advisor, SQL Access Advisor. SQL Profiles) mit, die vor allem den DBA viel
Arbeit abnehmen können. Leider sind diese neuen Möglichkeiten nur in der Oracle Enterprise
Edition bei Lizenzierung der Tuning und Diagnostic Management Packs nutzbar.
•
o
o
•
o
o
o
o
o
•
o
Allgemeines
Neue Partitioning Möglichkeiten
SQL_PROFILES
Ab Oracle 10gR1
Dynamic sampling
Query rewrite enables
Skip unusable indices
Automatically compute index statistics as part of creation
Cost-based query transformations
Ab Oracle 10gR2
Allow re-writes with multiple MVs and/or base tables
Oracle 11g
Mit dieser Version eliminiert Oracle die potentiell schlechten Execution Pläne durch “Bind
Variable Peeking” mit der Einführung von “Adaptiv Cursor Sharing”. Auch die Möglichkeit,
einen Index für den Optimizer auf INVISIBLE zu stellen, erleichtert das Leben sehr, wenn
man sich nicht sicher ist, ob man einen Index jetzt gefahrlos droppen kann oder nicht.
•
o
o
o
o
o
•
o
o
o
o
o
Allgemeines
Result Caches
Neue Partitioning Möglichkeiten
Index Hidden für Optimizer
Pending Optimizer Statistiken
SQL_PATCHES
Ab Oracle 11g
Enhanced Bind Peeking (Adaptives Cursor Sharing)
Use extended statistics to estimate selectivity
Use native implementation for full outer joins
Partition pruning using join filtering
Group by placement optimization
Die praktischen Erfahrungen mit dem CBO
Hand aufs Herz, wann hat Sie ein Benutzer das letzte Mal angerufen und gesagt: „Vielen
Dank, diese oder jene Verarbeitung ist jetzt viel schneller“? Vermutlich werden Sie eher
solche Anrufe erhalten: „Die Abfrage/der Report/die Verarbeitung ist so langsam! Nein, ich
habe nichts geändert!“.
Wer oder was beeinflusst den CBO?
Der CBO wird von vielen Faktoren beeinflusst (siehe Bild), wobei Oracle ab der Version 10g
einige, durch automatische Jobs, verändert (Objekt Statistiken), andere durch die Benutzer
selbst (unabsichtlich) verursacht werden (beispielsweise NLS Einstellungen) und andere
wirklich durch den Administrator verursacht werden (Instanz Konfigurationseinstellungen,
Oracle Version, etc.).
Abb. 2: Was beeinflusst den CBO?
Wie man sieht, gibt es so viele Einflussfaktoren, dass es an ein Wunder grenzt, dass der CBO
überhaupt sinnvolle und performante Execution Pläne findet. In Wirklichkeit liegt die
Erfolgsrate vom CBO (abhängig von der Oracle Version) bei über 99 Prozent, sofern man
nicht massive Mittel einsetzt um dem CBO das Leben schwer zu machen (siehe Irrtümer und
Fakten).
Leider ist es der letzte Prozentpunkt, das uns das Leben schwer macht. Zumal dieser
Prozentpunkt meist die komplexeren Statements betrifft, bei denen der CBO nicht den besten
Execution Plan findet.
Zu allem Überfluss sorgt der automatische Statistik Job ab Oracle 10g oft genug dafür, dass
plötzlich, über Nacht, der eine oder andere neue Execution Plan besser aber leider auch
schlechter performed. Oracle versucht, diesem Problem mit dem SQL Plan Management
Feature entgegenzutreten, nur muss man sich auch hier mit dem Thema auseinander setzen,
sonst friert man ungewollt alle Execution Pläne auf dem aktuellen Stand ein.
Idee: den Execution Plan „fixieren“
Der Versuch den Execution Plan mit diversen Methoden stabil zu halten (Outlines, SQL
Profiles, SQL Plan Management, etc.), hat auf den ersten Blick natürlich seinen Reiz: Nie
wieder „überraschend“ neue Execution Pläne, die das Potential haben, dass ein Statement
langsamer wird. Leider erreicht man dadurch auch genau das Gegenteil: Wenn sich die
Datenmengen oder Verteilungen wirklich ändern, kann der CBO auch keinen schnelleren
Execution Plan nutzen. Die Performance wird bei wachsenden Datenmengen somit
tendenziell schlechter.
Wie tickt der CBO jetzt eigentlich?
Für den CBO gibt es nur eines: Kosten
Die Kosten werden durch drei Kategorien bestimmt – diese sind:
I/O Kosten
In den meisten Fällen fallen beim I/O die höchsten Kosten an. Wenn man keinerlei
Änderungen an den Instanz-Konfigurationsparametern vorgenommen hat, wird jeder Block
I/O bei einem Index Zugriff als eine Kosteneinheit betrachtet. Bei einem Full Table Scan
(Lesen der gesamten Tabelle) werden die Kosten durch einen Faktor reduziert, den man auf
verschiedene Weisen beeinflussen kann (DB_FILE_MULTIBLOCK_READ_COUNT,
System Statistiken), und den Oracle ab Oracle 10g auch noch „zu schätzen“ versucht.
CPU Kosten
CPU Kosten entstehen durch die Verarbeitung von Funktionen (SQL Funktionen, einfache
Additionen, String Operationen, etc.), als auch für Query Re-Write und Aufschlag bei einigen
Funktionalitäten (Compressed Table, Compressed Index, etc.)
SORT Kosten
Wird sortiert, entstehen dabei auf der einen Seite CPU Kosten und zusätzlich noch SORT
Kosten, sofern der SORT einen größeren Umfang hat und potentiell auf die Disk ausgelagert
werden könnte. Dies spiegelt die darin versteckten I/O Kosten wieder.
Beispiel wie der CBO tickt
Aus Platzgründen führe ich das Beispiel hier nicht an. Wenn Sie Interesse an einem Beispiel
haben, schicken Sie bitte ein eMail an [email protected].
Dem Optimizer auf die Finger sehen
In allen Oracle Editions verfügbar
Oracle bietet viele Möglichkeiten, wie man dem Optimizer auf die Finger sehen kann. Es gibt
dutzende Performance Views, Script Outputs wie Teile vom Perfstat, Tracing Events, etc., mit
denen man jeden Schritt des Optimizers bis ins Kleinste nachvollziehen kann.
Nur in der Oracle Enterprise Edition mit lizenziertem Diagnostic und Tuning Pack
Hier bietet Oracle mit dem ADDM ein Tool, das potentiell auffällige Statements ausfindig
macht, die man anschließend mit dem SQL Tuning Advisor und/oder SQL Access Advisor
analysieren kann. Leider finden diese Tools selten die einfachsten RBO Tuning Tricks und
liefern so auch nicht den Vorschlag, diese zu eliminieren. Selbstverständlich kann man die
verbesserten Möglichkeiten vom AWR Repository im Vergleich zum Perfstat nutzen um
mehr Informationen über Top-Statements zu erhalten. Für aktuell laufende Top-Statements
gibt es dann noch einige Monitoring-Möglichkeiten (Top-Session, Top-Consumer).
Wie war der Execution Plan vorher?
Auf diese Frage bekommen Sie oft genug keine Antwort. Die Performance Views sind
Sichten auf das Memory (Shared Pool SQL Area) und unterliegen LRU Algorithmen: Wird
ein Execution Plan nicht mehr genutzt, verschwindet er.
Mit Perfstat oder dem AWR Repository sieht es meist auch nicht besser aus. Diese sammeln
nur jene Statements, die schon Top sind. War ein Statement mit dem alten Execution Plan
noch nicht unter den Top-Statements, gibt es auch hier keinerlei Anhaltspunkte, was sich am
Execution Plan verändert hätte.
Warum hat sich der Execution Plan geändert?
Auch auf diese Frage, warum der Execution Plan sich von einem vorhergehenden
unterscheidet, kann kein Oracle Tool verraten. Es bleibt somit nichts übrig, als alle relevanten
Daten zu sammeln und zu versuchen, die Ursache zu finden und zu lösen, bzw. das Statement
anders zu tunen. Das „SQL Plan Management“, das Oracle mit der Version 11g (nur für
Oracle EE mit Diagnostic und Tuning Pack Kunden) eingeführt hat, versucht lediglich zu
verhindern, dass ein neuer, potentiell schlechterer Execution Plan verwendet wird.
Verbesserungsvorschläge oder Analysen, warum der Execution Plan sich verändert hat, bleibt
dieses Feature leider auch schuldig.
Zusammenfassung
Der CBO ist bei größeren Datenmengen dem RBO deutlich überlegen, auch wenn sich der
CBO von Zeit zu Zeit Ausrutscher leistet. Oracle stellt alle Informationen zur Verfügung, mit
deren Hilfe man analysieren und suchen kann, warum der CBO bei dem einen oder anderen
Statement daneben liegt. Zusätzlich gibt es (für Oracle EE mit Diagnostic und Tuning Pack)
eine Reihe von Tools, die man als Pflaster verstehen kann. Sie beheben ein Problem,
interessieren sich aber nicht für die Ursache. Mit diesem Pflaster zu leben wäre schon in
Ordnung, wenn es nicht dazu führen würde, dass der Optimizer bei sich ändernden
Datenmengen oder Dateninhalten auf einen Execution Plan festgenagelt wird. Damit wird das
Pflaster (die Outlines oder SQL Profiles) wieder zu einer Ursache statt zu einer Lösung.
Wir haben mit TOM ( www.tom4oracle.com ) ein Tool entwickelt, mit dem man für viele
dieser Probleme eine Lösung bekommt. TOM sammelt im laufenden Betrieb Daten über SQL
Statements und den Optimizer, die anschließend analysiert werden. Stellt TOM dabei fest,
dass neue Execution Pläne zu einer schlechteren Performance führen, wird dies automatisch
gemeldet und dabei die potentielle Ursache angegeben. Damit wird in vielen Fällen die
Analyse vereinfacht. Gleichzeitig liefert TOM alle für die Optimierung relevanten
Informationen in einem Report ab, damit man sich nur noch auf das Tuning konzentrieren
muss, und seine Zeit nicht mit der Suche nach Informationen verschwendet.
Kontaktadresse:
Ing. Christian Pfundtner
GNC Global Network Communication GmbH
Nussdorfer Lände 23
A-1190 Wien
Telefon:
Fax:
E-Mail
Internet:
+43(0)1-370 97 87
+43(0)1-370 97 87 99
[email protected]
www.gnc.at
www.tom4oracle.com