Ein Artikel von Lothar Flatz, Senior Principal Consultant, Diso AG

Im ersten Teil seines Artikels geht Lothar Flatz auf die Problemstellung ein. Der zweite Teil, in der Dezemberausgabe, wird die Lösung aufzeigen. Lothar Flatz ist Mitglied des Oak Tables und Oracle ACE sowie Inhaber des Oracle ACE Ehrentitels der Oracle Corp.


Einführung

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 werden wir eine patentierte Technik beschreiben, wie wir den Aufwand auf einem angemessenen Niveau zu halten können.

Ein Witz, der ein Prinzip verdeutlicht

Der Titel bezieht sich auf einen Scherz, der im Prinzip aussagt, dass man keinen zusätzlichen Aufwand für etwas betreiben soll, was man wegzuwerfen wünscht:
Zwei Männer sitzen zusammen in einem Zug. Einer von ihnen nimmt eine Banane, schält sie, salzt sie und wirft sie  aus dem Fenster. Dann nimmt er eine andere Banane und wiederholt den Vorgang.

Nach der dritten Banane kann sein Gegenüber seine Neugier nicht länger bezähmen und fragt: „Warum werfen Sie alle diese Bananen aus dem Fenster?“ – „Nun“, antwortet der andere, „mögen Sie gesalzene Bananen?“

Bevor wir uns ernsteren Themen zuwenden, lassen Sie uns einen Augenblick inne halten. 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, siehe [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äufigste Verwendung ist die Anwendung des „Throw-away“ in der Optimierung von Indexdefinitionen, wie die folgende Darstellung illustriert.

Abb. 1: Suboptimaler Index-Zugriff aufgezeigt mittels „Throw-away“.

Hier sehen Sie 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.

(Anmerkung: 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 die nächste Abbildung werfen.

Hier sehen wir die gleichen Ausführungspläne wie die oben, aber dieses Mal auf den Aspekt des
physischen I/O fokussiert.

Abb. 2: I/O Wartezeit im Vergleich Index-Zugriff gegen Tabellen-Zugriff .

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 nicht beinhaltet, 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össe 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 (siehe Abbildung 1) zugegriffen. Für den Index bedeutet dies 57 Zugriffe pro leaf Block. Für die Tabelle heisst 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 sind. 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.

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.
Auf den ersten Blick erscheint das Problem sehr einfach. 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 gross sein, und  die Überlappung im Endergebnis ist vergleichsweise gering.

Abb. 3: Mengendiagramm für die Müller-in-Bern-Suche.

Eigentlich wäre es der beste Weg, die beiden Suchkriterien zu kombinieren. Das stösst 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 (siehe 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.

In der Dezemberausgabe der News@Diso 2016 wird Lothar Flatz den Weg zur Lösung aufzeigen. Fragen? Wenn Sie Lothar Flatz kontaktieren möchten, senden Sie eine E-Mail an lflatz@diso.ch

Diso AG – Der Daten- und Cloud-Experte

Die Diso AG ist ein renommierter IT-Dienstleister und langjähriger Oracle-Vertriebspartner in der Schweiz mit Schwerpunkten in den Bereichen Datenbanken und Cloud-Lösungen. Diso bietet ihren Kunden beispielsweise die Oracle Plattform as a Service-Lösung und die dazugehörige Datenmigration an. Kunden profitieren des Weiteren vom Komplettlösungsangebot im Sinne von Planung, Integration, Support inklusive Betrieb und Überwachung von IT-Infrastrukturen und Datenbanksystemen.
Im Bereich Software-Engineering entwickelt Diso massgeschneiderte IT und Software-Lösungen für unternehmensspezifische Anwendungen, wann immer sinnvoll mit einem mobile-first Ansatz. Zudem ist Diso Spezialist wenn es um die Software-basierte Optimierung von Performance geht. Auf die Kompetenz des traditionsreichen IT-Dienstleisters und Mittelständlers vertrauen bereits namhafte Kunden aus den Schwerpunktbranchen Banken, Versicherungen Detailhandel und öffentliche Verwaltung.
Die Diso designt wandlungsfähige IT-Systeme, entwickelt massgeschneiderte Software und ermöglicht die performante Verwendung und Auswertung von Informationen.