Über unsMediaKontaktImpressum
Lothar Flatz 02. Juni 2015

Oracle Performance Tuning – Warum sollte man gesalzene Bananen essen?

Im Zuge meiner praktischen Tuning-Tätigkeit bin ich auf eine Kategorie Abfragen gestoßen, die sehr schwer zu optimieren sind. Es handelt sich dabei um Abfragen mit unabhängigen und nicht sehr selektiven Suchkriterien über zwei (oder mehr) Tabellen.

Ein typisches Beispiel ist eine Adresssuche über den Namen und den Wohnort einer Person. Diese Kategorie der Abfragen wird durch eine potenziell hohe Zahl von „Throw-away“-Datensätzen charakterisiert. Als „Throw-away“ werden Datensätze bezeichnet, die im Laufe eines Ausführungsplanes zwar gelesen werden, aber zum Endresultat nichts beitragen. „Throw-away“-Datensätze lassen sich nicht immer vermeiden.

In diesem Artikel werde ich eine patentierte Technik beschreiben, wie man den Aufwand auf einem angemessenen Niveau halten kann.

Ein Witz, der ein Prinzip verdeutlicht:

Zwei Männer sitzen zusammen im Zug. Einer von ihnen nimmt eine Banane, schält sie, salzt sie und wirft sie aus dem Fenster. Dann nimmt er die nächste Banane und wiederholt den Vorgang. Nach der dritten Banane kann sein Gegenüber seine Neugier nicht länger unterdrücken und fragt: "Warum werfen Sie alle diese Bananen aus dem Fenster?" – "Nun", antwortet der andere "mögen Sie gesalzene Bananen?"

Was ist an diesem Witz komisch? Nun, wenn Sie keine Banane wollen, welchen Sinn macht es dann, sie erst zu schälen und zu salzen? Ist es vorstellbar, dass wir im wirklichen Leben etwas Ähnliches tun? Beispielsweise in den Ausführungsplänen einer Datenbank?

Das Konzept des „Throw-away“ im Performance-Tuning

Wie schon erwähnt, bezeichnet man mit „Throw-away“ Datensätze, die von der Datenbank gelesen wurden, aber in keiner Weise auf das Ergebnis einer Abfrage Einfluss nehmen. Die Minimierung des „Throw-away“ ist ein Grundprinzip des Performance Tuning [1] (Absatz 12.1.2.). Das Konzept ist intuitiv plausibel, da ein Datensatz, der abgerufen, aber nicht verwendet wird, sicherlich einen überflüssigen Aufwand darstellt. Eine häufige Verwendung ist die Anwendung des „Throw-away“ in der Optimierung von Indexdefinitionen, wie Abb.1 illustriert.

Hier sehen wir den wesentlichen Teil einer SQL-Monitor-Auswertung. Wir sehen, dass über den Index-Zugriff 175k Datensätze gefunden werden. Wenn auf die Tabelle zugegriffen wird, bleibt nur ein Datensatz übrig. Es existiert also auf der Tabelle ein zusätzlicher Filter, durch den die Anzahl der Datensätze dramatisch reduziert wird. Dieser Filter wird durch den Index nicht unterstützt. Alles, was wir tun müssen, ist daher im Ausführungsplan die Spalten des Filters nachzulesen und den Index um diese zu erweitern (Anm.: Natürlich müssen wir auch sicherstellen, dass die zusätzlichen Spalten im Index häufig verwendet werden und nicht nur von dieser einen Abfrage). Auf diese Weise würden die Datensätze schon im Index ausgefiltert werden und nicht erst beim Zugriff auf die Tabelle.

Also: Warum sollte es besser sein, den „Throw-away“ im Index zu haben? Lassen Sie uns einen Blick auf Abb.2 werfen. Hier sehen wir die gleichen Ausführungspläne wie oben, aber dieses Mal auf den Aspekt des
physischen I/O fokussiert.

Wie wir sehen können, haben wir viel mehr I/O-Anforderungen beim Zugriff auf die Tabelle als beim Zugriff auf den Index. Hier müssen wir verstehen, wie der Buffer-Cache arbeitet. Alle Datensätze in unserem Bespiel müssen zunächst im Buffer-Cache gesucht werden. Ist der Datenblock, der den Datensatz beinhaltet, nicht im Buffer-Cache vorhanden, muss er von der Platte gelesen werden. Dazu muss meist ein anderer Datensatz im Buffer-Cache überschrieben werden. Es werden die Datensätze überschrieben, welche den niedrigsten Hitcount haben. Der Hitcount wird immer dann erhöht, wenn ein Datenblock benötigt wird. Die Größe der betroffenen Segmente spielt in unserem Beispiel natürlich eine wichtige Rolle. Der Index hat 3.075 Leaf-Blöcke und die Tabelle hat 78.954 Blöcke. Auf  jedes Segment wird 175.000 Mal (s. Abb.1) zugegriffen. Für den Index bedeutet dies 57 Zugriffe pro Leaf-Block. Für die Tabelle heißt das etwa zwei Treffer pro Block. Deshalb ist die Hitcount für die Indexblöcke natürlich höher. Dies wiederum bedeutet, dass die Indexblöcke mit einer höheren Wahrscheinlichkeit im Hauptspeicher gefunden werden als die Tabellenblöcke.

Da der Index außerdem noch nach den Suchkriterien sortiert ist, ist zusätzlich anzunehmen, dass nicht der ganze Index abgerufen wird, sondern nur der Teil, der für die Suche relevant ist. Daher ist normalerweise zu erwarten, dass die meisten der 175.000 Zugriffe auf den Index über den Buffer-Cache gefunden werden können. Im Vergleich dazu löst der Tabellenzugriff mehr physischen I/O aus. Fügt man eine zusätzliche Filter-Spalte im Index hinzu, reduziert sich so die Gesamtzahl der physischen Lesevorgänge, weil I/O ungünstige Tabellenzugriffe durch I/O günstigere Indexzugriffe vermieden werden.

Eine langsame Abfrage

Vor einigen Jahren sollte ich eine Abfrage optimieren, die mich über Monate beschäftigte. Natürlich habe ich nicht die ganze Zeit daran gearbeitet, aber ab und zu tauchte das Problem wieder in meinen Gedanken auf und ich überlegte erneut, wie es gelöst werden könnte.

Im Grunde war die Anforderung lediglich "finden Sie alle Personen mit dem Namen Müller in der Stadt Bern". Das scheint zunächst eine sehr häufige und einfache Abfrage zu sein. Auf den zweiten Blick ist es nicht so einfach. Müller ist ein gebräuchlicher Name in der Schweiz. Bern hat als Schweizer Hauptstadt über 350.000 Einwohner. Egal mit welchem Suchkriterium Sie die Suche beginnen, die Zwischenresultate werden ziemlich groß sein, und  die Überlappung im Endergebnis ist vergleichsweise gering.

Eigentlich wäre es der beste Weg, die beiden Suchkriterien zu kombinieren. Das stößt auf praktische Schwierigkeiten, da die beiden Suchkriterien auf verschiedenen Tabellen abgefragt werden: eine auf Tabelle Person, die andere auf Tabelle Adresse.

Lassen Sie uns die verschiedenen Möglichkeiten, zwei Suchkriterien in einem Index mit den Möglichkeiten der Oracle-Datenbank zu verbinden, untersuchen: Unsere zugrundeliegende Annahme ist, dass wir uns in einer OLTP-Umgebung befinden, in der Praxis war es eine Call-Center-Anwendung.  Natürlich ist das genau der Kontext, in dem solche Abfragen vorkommen. Die meisten Konzepte, die die Kombination von Suchkriterien über Tabellengrenzen hinweg erlauben, können praktisch nur in einem Data-Warehouse angewendet werden (s. unten).

Materialized View

Eine Lösungsvariante wäre es, Tabelle Adresse und Tabelle Person mittels einer materialisierten View zu kombinieren und dann einen zusammengesetzten Index auf dieser zu erstellen. Das funktioniert im Prinzip, allerdings gibt es einige Voraussetzungen:

  • Grundsätzlich muss man eine materialisierte View erstellen dürfen, nicht immer wird man das Recht dazu bekommen.
  • Da es sich in einem OLTP-Umfeld um eine „Fast refresh on commit“-View handeln muss, sollte sich die Änderungsfrequenz auf den Basistabellen im Rahmen halten. Sonst nimmt der Aktualisierungsaufwand überhand.

Diese Lösung kann in einigen sorgfältig ausgewählten Szenarien funktionieren, aber es ist sicherlich keine allgemein gültige Lösung.

Bitmap Join Index

Grundsätzlich hat diese Lösung die gleichen Nachteile wie die materialisierte View. Hinzu kommt noch ein ernsthafter Nachteil in Form des Bitmap-Segment-Locks, der beim Bitmap-Index auftritt. In einer OLTP-Umgebung ist dies keine Option.

Text-Index

Es ist möglich, einen mehrspaltigen Textindex über mehr als eine Tabelle zu erzeugen. Auch hier liegt die Achillesferse im Aufwand der Aktualisierung, welche über ein Programm vorgenommen werden muss. Außerdem müsste man den Abfragetext ändern, um Textindex-Suchoperatoren nutzen zu können.

Auf dem Weg zur Lösung

Ich stieß in meiner beruflichen Laufbahn gelegentlich auf diesen Typ Abfrage, da es sich um eine typische Call-Center-Abfrage handelt. Jedes Mal, wenn mir jemand sagte: "Unsere Call-Center-Software ist maximal optimiert und reagiert sofort", regte ich an, nach "Müller" in "Bern" oder nach "Meier" in "Zürich" zu suchen. Das Ergebnis waren immer lange Gesichter und eine lange Wartezeit.

Nachdem ich mich immer wieder mit diesem Typ Abfrage beschäftigt hatte, kam ich zu dem Schluß, dass die Abfrage tatsächlich von dem Problem der „Throw-away“-Datensätze beherrscht, also „throw-away“-dominiert ist, wie ich dies im Rest des Textes nennen werde. Lassen Sie uns Abb.3 erneut ansehen: Nur der orangefarbene Teil ist das Ergebnis. Der Rest ist „throw-away“.

Tatsächlich ist der “Throw-away”-Anteil weit größer als das Ergebnis. Das heißt also, die Abfrage ist „throw-away“-dominiert. Ich kam nicht umhin, mich zu fragen: Warum sollte man den zusätzlichen Aufwand des Lesens eines Datensatzes aus der Tabelle betreiben, wenn man schon mit hoher Wahrscheinlichkeit weiß, dass man den Datensatz wegwerfen wird? Die Idee der gesalzenen Banane entstand in meinem Kopf als eine Art Metapher für den Tabellenzugriff.

Ein Testszenario

Um meine Überlegungen im Zusammenhang mit der Müller-in-Bern-Suche zu testen, habe ich ein kleines Beispiel erzeugt und versucht, diesem eine ähnliche Datenverteilung zu geben, wie sie in der Realität vorhanden ist. Ich habe das folgende Schema mit zwei Tabellen erzeugt:

Create table address (city_name varchar2(30) not null,
             person_id number(7) not null,
             data varchar2(200))
/
Create table person (person_id number(7) not null,
             last_name varchar2(15) not null,
             data varchar2(200))
/

Wie man sieht, ist das Schema stark vereinfacht. Es muss nicht realistisch sein, sondern nur im Sinne der  Diskussion ausreichen. Ich habe ausreichende Indexe erzeugt, damit der Optimizer nicht in seiner Wahl eingeschränkt ist.

create Index city_idx1 on address (city_name, person_id) compress;
create Index city_idx2 on address (person_id, city_name) compress;
create Index person_idx1  on person(last_name, person_id) compress;
create Index person_idx2  on person(person_id, last_name) compress;

Unten sehen wir nun die Abfrage, um die es im Beispiel geht. Unter der Annahme, dass sie in einem OLTP-System abläuft, reicht es aus, nur die ersten zehn Datensätze zu lesen.

select * from (
select a.city_name, p.last_name, substr(p.data,1,1) p_data, substr(a.data,1,1) a_data
  from ibj.address a,
       ibj.person p
where p.person_id = a.person_id
  and a.city_name = 'Bern'
  and p.last_name = 'Müller')
where rownum < 11
/

Ausführungspläne

Der klassische Ausführungsplan wäre nested loop join. Wie wir schon wissen, ist die Achillesferse des Planes der teure Zugriff auf die beiden Tabellen. Wie nicht anders zu erwarten, hat Oracle das Problem bereits erkannt und Gegenmassnahmen eingeleitet.

Oak Table Mitglied Randolf Geist schreibt: Oracle introduced in recent releases various enhancements in that area – in particular in 9i the "Table Prefetching" and in 11g the Nested Loop Join Batching using "Vector/Batched I/O". The intention of Prefetching and Batching seems to be the same – they both are targeted towards the usually most expensive part of the Nested Loop Join: The random table lookup as part of the inner row source. By trying to "prefetch" or "batch" physical I/O operations caused by this random block access Oracle attempts to minimize the I/O waits. [3]

Beim “Table prefetch” wird der Zugriff auf die nicht treibende Tabelle außerhalb des Nested Loops gemacht, wie in Abb.4 ersichtlich. Die Rowids der Zieltabelle werden innerhalb des Nested Loops gesammelt und zwischengespeichert. Die Rowids werden sortiert und benachbarte Datenblöcke werden mit einem kleinen Vektor I/O gelesen.

Nested Loop batching in Version 11 geht noch einen Schritt weiter, da der Zugriff auf die äußere Tabelle in einem kompletten separaten Verarbeitungsschritt erfolgt.

Ich zitiere aus [1], Abschnitt 11.6.3.1.2: Oracle Database 11g Release 1 (11.1) introduces a new implementation for nested loop joins to reduce overall latency for physical I/O. When an index or a table block is not in the buffer cache and is needed to process the join, a physical I/O is required. In Oracle Database 11g Release 1 (11.1), Oracle Database can batch multiple physical I/O requests and process them using a vector I/O instead of processing them one at a time. As part of the new implementation for nested loop joins, two NESTED LOOPS join row sources might appear in the execution plan where only one would have appeared in prior releases. In such cases, Oracle Database allocates one NESTED LOOPS join row source to join the values from the table on the outer side of the join with the index on the inner side. A second row source is allocated to join the result of the first join, which includes the rowids stored in the index, with the table on the inner side of the join.

Wie Sie sehen werden, folgt unsere patentierte Lösung (s. u.) – von uns der “Index Backbone Join” genannt – demselben Grundgedanken, aber für sämtliche Tabellen in einer Abfrage. Aber bevor es soweit ist, lassen Sie uns zunächst untersuchen, welchen Plan der Optimizer standardmäßig wählt. Die Güte des Planes können wir anhand der Runtime-Statistiken beurteilen. Der Optimizer wählt Nested Loop batching.

Wie wir klar erkennen können, nimmt der Zugriff auf die Tabelle Adresse die meiste Zeit in Anspruch. Warum also sollten wir die Datensätze von der Tabelle lesen, von denen wir wissen, dass sie wahrscheinlich weggeworfen werden? Alle notwendigen Informationen zur Auswahl der korrekten Datensätze können bereits im Index gefunden werden. Was stört, ist, dass die Datenbank zwingend einen Zugriff auf die innere Tabelle einschaltet, wenn von dieser Tabelle Spalten benötigt werden, die nicht im Index stehen.

Man könnte jetzt alle benötigten Spalten in den Index schreiben, aber alles hat seine Grenzen. Ein Index, der zu groß wird, ist nicht mehr effizient. Und wie soll man wissen, welche Spalten zukünftige Abfragen benötigen werden? Man muss also den intervenierenden Tabellenzugriff ausschalten und die beiden Indexe direkt verknüpfen. Das ist die Idee des “Index Backbone Join”. Der “Index Backbone Join” besteht aus zwei Phasen: In der ersten Phase werden nur die Indexe verknüpft, um die Rowids der Datensätze zu finden, die das Endergebnis bilden werden. Haben wir die Ergebnismenge in Form von Rowids, machen wir einen verzögerten Tabellenzugriff für beide Tabellen. Das ist der Hauptunterschied zu Nested Loop batching. Hier wird der Zugriff auf eine Tabelle verzögert.

Der Optimizer ist nicht in der Lage, einen „Index Backbone Join” von sich aus vorzunehmen, aber wir können ihn mit einem manuellen Rewrite simulieren (s. u.). Beachten Sie bitte, dass der NO_MERGE-Hint im Beispiel zwingend erforderlich ist, um den Optimizer daran zu hindern, mittels eines Rewrite auf seinen Standardplan zu kommen.

select * from (
select a.city_name, p.last_name, substr(p.data,1,1) p_data,
       substr(a.data,1,1) a_data
  from ibj.person p,
       ibj.address a,
       (select /*+ NO_MERGE  */ p.rowid p_rowid, – phase 1
               a.rowid a_rowid
           from ibj.address a,
                ibj.person p
           where p.person_id = a.person_id
            and a.city_name = 'Bern'
            and p.last_name = 'Müller'
         ) i
where p_rowid = p.rowid
  and a_rowid = a.rowid)
where rownum < 11
/

Vor jedem Lauf wurde der Buffer-Cache geleert. Daher sind die Ergebnisse vergleichbar.

Wenn wir den enormen Beschleunigungsfaktor betrachten, erübrigt sich jeder Kommentar.

Schlussfolgerungen

Die Idee des “Index Backbone Join” erlaubt es, Ausführungspläne aus einem anderen Blickwinkel zu betrachten. Es gibt Spalten, die für einen Ausführungsplan essentiell sind. Zum Beispiel solche, die in der where-Klausel oder in Joins verwendet werden.  Im obigen Beispiel werden nur zwei Tabellen verknüpft. Allerdings lässt sich der Ansatz leicht verallgemeinern, so dass in einer ersten Phase so viele Indexe, die für den wesentlichen Teil der Abfrage erforderlich sind, verknüpft werden. Diese Verknüpfung mehrerer Indexe bildet das Rückgrat des Ausführungsplans. Daher der Name „Index Backbone Join“.

Eine Grundvoraussetzung ist eine geeignete Indexierung. Es werden jedoch keine zusätzlichen Indexe benötigt, sondern vorhandene müssen lediglich um zusätzliche Spalten erweitert werden. Im Gegenzug winken schnellere und robustere Ausführungspläne.

Autor

Lothar Flatz

Lothar Flatzwar 15 Jahre Oracle-Mitarbeiter und auch Mitglied in der Real World Performance Group. Er ist Oaktable Member und Oracle ACE und hat ein Patent zur Verbesserung des Optimizers.
>> Weiterlesen
botMessage_toctoc_comments_9210