Abstrakt – 

Viele spannende Aufgaben durfte ich während 10 Jahren bei Oracle Consulting übernehmen. Die komplette Neuindexierung einer grossen Onlinedatenbank war, neben der Mitarbeit an einer Software zur Auswertung der Messdaten am CERN, die eindrücklichste.

Bei Oracle Consulting liess sich keiner finden, der sich eine solche Aufgabe zugetraut hätte. Das Resultat war ein grosser Erfolg und die Ziele meines Auftraggebers wurden vollumfänglich erreicht. Durch die ehrenvolle Einberufung in die Real Performance Group von Oracle – als einer von 5 Europäern – bekam ich weitere Aufgaben und konnte so selbst bis heute keine weitere Gesamtindexierung vornehmen.

Der folgende Text behandelt die Indexierung einer Onlinedatenbank, und es ist, wenn wir im Folgenden von Index sprechen, der konventionelle B*-Index gemeint. Für ein Data Warehouse (kurz DWH) gelten selbstverständlich andere Indexierungsregeln, die allerdings den Umfang dieses Artikels sprengen würden.

Der Autor Lothar Flatz begleitet und unterstützt seit 15 Jahren Kunden bei schwierigen Oracle Problemen, speziell im Bereich Datenbankperformance. Während seiner Zeit bei Oracle war er als einer von 25 Oracle Experten teil der Real World Performance Group, und ist Mitglied des Oracle Oak Table Netzwerks.

1. Einleitung

Die Idee der grundlegenden faktenbasierten Indexierung wurde in einem lockeren Gespräch bei einer Tasse Tee geboren. Ich war gerade als Oracle-Berater mit einem Tuning-Auftrag bei einem Key Account. Mein Kontaktmann, ein erfahrener DBA, war wieder einmal bei seinem Lieblingsthema angelangt:

Mindestens 30 Prozent unserer Indexe sind überflüssig, darauf würde ich wetten. Deshalb sind unsere Ausführungspläne auch so instabil. Zudem braucht es Platz. Die Indexierung einmal komplett zu überprüfen, wäre sicherlich das Richtige.

Im Grunde musste ich ihm Recht geben. In der Tat werden bei eingekauften Applikationen die Indexe in der Regel von den Entwicklern erstellt. Die Entwickler erleben das System aber selten unter Volllast, und zudem ist die Indexierung nie an die Bedürfnisse eines bestimmten Kunden angepasst. Eine nachträgliche Indexierung ist darum die Regel.

Die Grundlage, die im Vorfeld geschaffen wurde, wird aber nicht mehr infrage gestellt. Es kommen schlichtweg neue Indexe dazu. Das Resultat ist dann meist ein Wirrwarr von Indexen. Wieso also die Sache nicht von Grund auf neu und richtig machen? Die Fakten liegen vor. Die Oracle-Datenbank ist eine unendliche Quelle von Informationen. Über die Informationen, die im Shared Pool und in AWRs gespeichert sind, müsste es sicherlich eine Möglichkeit geben, eine geeignete Indexierung zu erstellen.

Einige Zeit später war ich nun in der Lage, eine solche grosse Herausforderung anzugehen. Einer meiner Kunden hatte genug von halbherzigen Lösungen und wollte die Indexstruktur seiner Kernapplikation in Ordnung gebracht haben.

Den Kunden musste ich aber zuerst überzeugen, dass wir die Indexierung von Grund auf neu machen müssten. Meine Meinung war, dass wir uns bei Verbesserungen auf Basis der bestehenden Indexierung nur verzetteln könnten. Die Entscheidung, ob wir einen Index jetzt löschen sollten oder eben doch nicht, würde uns nur hemmen. Solche fruchtlosen Diskussionen hatte ich bereits erlebt.

Endlich fiel sein Entscheid. Wir löschten auf der Testdatenbank sämtliche Indexe und fingen ganz von vorn an.

Freilich hätten wir nun einfach ein grosses SQL Tuning Set erstellen und es durch SQL Tuning Adviser abarbeiten lassen können. Der Haken an diesem Vorgehen war, dass wir uns blind auf die Entscheide der SQL Tuning Adviser hätten verlassen müssen. Zudem würden wir das Know-how und die Erfahrungen der Entwickler sowie der DBAs nicht verwenden können. Ganz klar werden die Argumente hier noch einmal zusammengefasst:

First, while the automated tools reduce the complexity of the physical design process, it is still nontrivial to identify a representative workload that can be used to drive the physical design in its entirety. Second, automated tools do not consider all factors that impact physical design (e.g., the impact of replication architectures) [3].

Tatsächlich unterstreichen neue Schweizer Forschungen, dass die automatische Lösung mittels Tools für grosse Gesamtaufgaben problematisch ist [1], [2].

Wir wählten stattdessen ein halbautomatisches Vorgehen, bei dem jeder Schritt erklärt und verstanden werden kann. Das gesamte Team konnte seine Gedanken einbringen. Am Schluss hatten wir eine klar begründete Lösung, hinter der das gesamte Team stehen konnte.

2. Überlegungen während der Vorbereitung

2.1 Verlangsamung von DML-Operationen

Folgender Satz findet sich in technischen Dokumentationen: Ein Index verlangsamt eine Insert Operation um 100 Prozent. Das stimmt aber höchstens, wenn nur ein Satz eingefügt wird. Sind es viele Sätze in einer Insert … Select Operation, so ist die gemessene Verlangsamung um einiges höher. Ich konnte in Tests mit 1 Million Datensätzen einen Verlangsamungsfaktor von 200–300 Prozent messen. Diese Verlangsamung entsteht zusätzlich dadurch, dass der B*-Baum neu organisiert werden muss.

Weit gravierender ist ein Update auf Indexspalten, das in seinen Auswirkungen einem Delete mit nachfolgendem Insert entspricht. Dies erzeugt also den doppelten Aufwand im Vergleich zu einem Insert, aber nur für die Indexe mit den von der Änderung betroffenen Spalten.

Bei einem Row Movement zwischen 2 Partitionen tritt die ultimative Verlangsamung durch Index-Wartung auf. Dies entspricht einem Update auf sämtlichen lokalen Indexen.

Das Fazit und eine der wichtigsten Regeln für eine solide Indexierung ist also: Man sollte so wenige Indexe wie möglich anlegen. Bei Onlineapplikationen mit einem Schwerpunkt auf einem Einzelsatz-Insert kann man etwas großzügiger sein, bei einem Data Warehouse sollte man zugunsten der ETL-Verarbeitung mit Indexen eher sparsam umgehen.

2.2 Indexe mit einer oder mehreren Spalten

Manchmal kommen in Applikationen nur einspaltige Indexe vor. Das ist ein gravierender Fehler. Falls ein Index bereits existiert und an den Overhead im die DML auf sich genommen hat, dann sollte man ihn auch nützen und möglichst alle sinnvollen Suchspalten kombinieren. Auch eine wenig selektive Spalte mit nur 2 unterschiedlichen Werten kann als zusätzliche Spalte in einem Index immerhin eine Verbesserung um Faktor 2 erwirken.
Ein Beispiel.

Der dreispaltige Index {Nachname, Vorname, Alter} kann 3 unterschiedliche Suchen unterstützen:

{Nachname, Vorname, Alter}
{Nachname, Vorname}
{Nachname}

Folgende Suchen können nicht unterstützt werden:

{Vorname, Alter}
{Alter}
{Vorname}

Die klassischen B*-Indexe haben hier leider Einschränkungen.

Wer die vollständige Flexibilität und Performance will, kann den mehrdimensionalen Index der Firma Dimesio verwenden, der beliebige Kombinationen von Suchkriterien parallel auswerten und verlinken kann.

Fazit: Es ist besser, einen existierenden Index um eine Spalte zu erweitern, als einen zusätzlichen Index zu erstellen.

2.3 Die Reihenfolge der Spalten im Index

Ein gängiger Mythos der Indexierung ist, dass man selektive Spalten an den Anfang des Index stellen sollte. Dies würde es erlauben, im Indexbaum schneller die relevanten Blöcke ausfindig zu machen. Meistens sind die Verbesserungen marginal. (Für eine ausführliche Diskussion durch den Indexspezialisten Richard Foote klicken Sie hier).

Eine wenig selektive Spalte zu Beginn des Indexes hat sogar einen kleinen Vorteil gegenüber einer stark selektiven Spalte: Der Index komprimiert besser. Einen wichtigeren Einfluss auf die Reihenfolge der Spalten im Index hat die Frage: Wie oft wird nach bestimmten Spalten gesucht?
Habe ich also einen Index über den Vor- und Nachnamen einer Person, so ist {Nachname, Vorname} einem Index mit der Reihenfolge
{Vorname, Nachname} vorzuziehen, wenn häufiger nach dem Nachnamen allein gesucht wird.

Eine Ausnahme kann hier eine sehr selektive Spalte am Anfang sein. Der Index I1 {Nachname, Geschlecht} ist auch dann besser als der
Index I2 {Geschlecht, Nachname}, wenn seltener nach Nachname als nach Geschlecht gesucht wird, solange nur auch relativ häufig nach Nachnamen allein gesucht wird. In diesem Falle kann der Index I1 verwendet werden. Der Index I2 wird für eine Suche nach Geschlecht allein nicht verwendet werden, da das Geschlecht in der Regel zu wenig selektiv ist.

Fazit: Die Wahl der optimalen Reihenfolge der Spalten ist der schwierigste Teil im Designprozess des B*-Indexes. Wenn man dies richtig macht, erlaubt dies, mit einem Index mehrere Suchen zu unterstützen.

2.4 Foreign Keys indexieren?

Ich denke, es ist unbestritten, dass der Primary Key in aller Regel indexiert werden wird. Im Fall der Foreign Keys ist das nicht vollends klar, die Foreign Keys zu indexieren, würde ich aber trotzdem empfehlen.

Dies aus 2 Gründen:

> Da es beim Löschen eines Satzes zu ungünstigen Sperren der abhängigen Sätze kommen kann, sofern ein Foreign Key Constraint existiert.

> Damit ein Nested Loop Join in alle bestimmte Richtungen möglich ist und somit der Optimizer in der Wahl des besten Zugriffspfades nicht zusätzlich eingeschränkt wird.

Schlussfolgerung: Es ist vorteilhaft, einen Foreign Key zu indexieren. Zumindest sollte man, wenn man mit einer Komplettindexierung produktiv geht, das Risiko eines nicht indexierten Foreign Key scheuen. Danach kann man bei flüssigem Betrieb vereinzelte nicht benötigte Foreign Keys herauslöschen.

2.5 Zieldefinition

Die obigen Überlegungen erlauben uns die Definition von Qualitätskriterien, die für die Lösungs- und Vorgehensevaluation herangezogen werden:

1) Vollständigkeit: Alle wesentlichen Abfragen sollen, sofern die Abfragecharakteristik einen Index sinnvoll macht, durch einen Index unterstützt werden.

2) Minimalität: Die komplette Unterstützung der Abfragen soll mit der kleinst- möglichen Anzahl von Indexen geschaffen werden.

3) Effizienz: Sämtliche Filterkriterien, die die Indexwirkung verbessern, sollen im Index enthalten sein.

Die Ausprägungen der Zielkriterien können und sollen in jedem Projekt neu definiert werden. Es ist schwer, sich hier auf globale Richtlinien festzulegen, da die einzelnen Applikationen unter Umständen stark abweichende Bedürfnisse haben können. So könnte man beispielsweise festlegen, dass eine Abfrage als wesentlich gilt, wenn sie pro Woche mindestens 5-mal im Onlinebetrieb vorkommt oder aber in einem wiederkehrend ablaufenden Batchjob enthalten ist.

3. Chronik einer Gesamtindexierung

3.1 Vorbereitung

Zunächst wurde ein Team aus allen Teilnehmern gebildet. Da war einmal ich selbst als externer Berater für den DB-Hersteller. Zusätzlich 2 Vertreter aus dem Entwicklungsteam des Applikationsherstellers und 2 DBAs als Vertreter des Softwarebetreibers. In 2 Sitzungen wurden die wichtigsten Schritte bestimmt und die Grundregeln der Indexierung festgelegt:

> Es wurde entschieden, dass Primär – und Fremdschlüssel grundsätzlich automatisch einen Index bekommen sollten.

> Es wurden Namenskonventionen definiert.

> Es wurde dem Design der Applikation Rechnung getragen.

> Die Art der physischen Speicherung (z. B. Tablespace) wurde festgelegt.

3.2 Datensammlung

In dieser Phase versuchte man, so viele Informationen wie möglich über die Abfragebedingungen in der Datenbank zu speichern. Möglichst alle wichtigen Verarbeitungen sollten unbedingt in die Auswertung mit einbezogen werden. Das heisst, nicht nur tägliche Verarbeitungen, sondern auch wöchentliche und monatliche Aktivitäten mussten berücksichtigt werden. Dadurch zog sich diese Phase über mehrere Monate hin. Die Arbeit wurde uns jedoch von automatischen Sammeltools abgenommen, auf Seiten des Teams war relativ wenig zu tun.

Im Wesentlichen werden untenstehende Informationen gesammelt:

> Erstens die Häufigkeit und die Vergleichsoperatoren, mit denen nach einer bestimmten Spalte gesucht wird
> Zweitens die Kombination von Spalten, nach denen parallel gesucht wird, und die Häufigkeit, mit der dies geschieht

Dies sind im Wesentlichen dieselben Informationen, die die Datenbank zur Unterstützung der automatischen Statistikgenerierung in der sys.col_usage$ sammelt. Ab der Version 11.2.0.2 werden auch Spaltenkombinationen unterstützt.

Das relevante Verfahren beschreibt Maria Colgan – eine Kollegin aus dem Oak-Table-Netz und bis vor kurzem die Produktmanagerin des Optimizers – in ihrem Weblog [4]. In früheren Datenbankversionen werden die Spaltenkombinationen nicht unterstützt und müssen daher aus den anderen Quellen hochgerechnet werden.

Als Quellen bieten sich an:

> Die aktuellen Abfragen Shared Pool. Hier nimmt man die Suchkriterien einfach aus den Filter- und Access-Predicates.

> Die Top-Statements aus dem AWR. Hier muss man leider einen Reparse durchführen, da die Filter- und die Access-Predicates in der Tabelle DBA_HIST_SQL_PLAN leer sind.

Diese Möglichkeiten sind auch deshalb wichtig, weil man sich auf den Inhalt der col_usage$ nicht 100%ig verlassen kann. Ich habe bereits Datenbanken gesehen, bei denen der Inhalt der col_usage$ nicht brauchbar war. Dies vermutlich aufgrund von Memorymangel. Es ist klar, dass dann natürlich auch die Statistikgenerierung in Mitleidenschaft gezogen wird.

Ausserdem werden diese Informationen aus dem Dictionary verwendet:

> Die Selektivität der einzelnen Spalten sowie die der verwendeten Kombinationen
> Primary Key und Foreign Key Constraints

3.3 Auswertung

Jetzt werden die gesammelten Informationen zu einem fertigen Index-Design verdichtet. Dies geschieht über einige Schritte, das Tool unterstützt diese aber halbautomatisch. Bei schwierigen Designentscheidungen wird das menschliche Wissen aber als letzte Instanz hinzugezogen. Das Vorgehen folgt ungefähr dem Prinzip des als Merge-and-Reduction bekannten Algorithmus.[3]

Als Basis für die Arbeit dienen uns die in der Vorphase gefundenen Spaltenkombinationen. Grundsätzlich könnte man aus jeder Spaltenkombination, die in der Suche auftaucht, einen Index machen. Das würde aber zu einem Überangebot an Indexen führen. Da jeder Index die DML-Operationen verlangsamen kann [z. B. 6], sollen nur so viele Indexe wie nötig und so wenige wie möglich erstellt werden.

In den folgenden Schritten wird also versucht, die Indexstruktur bezüglich Preis/Leistung zu verbessern.

3.3.1 Grundindexierung

Es kann meist von einer Basisindexierung ausgegangen werden. Primary Keys bekommen in der Regel wie schon erwähnt ohne große Überlegung ebenfalls einen Index.

3.3.2 Elimination

In dieser Phase nimmt man alle Suchkombinationen aus der Betrachtung, die man ohne große Leistungseinbussen weglassen kann. Das sind vor allem:

1. alle Foreign- und Primary-Keys, die bereits im vorigen Schritt indexiert worden sind
2. alle Suchkombinationen, die so wenig selektiv sind, dass sich ein Index nicht lohnt
3. alle Suchkombinationen, die schon in anderen Suchkombinationen gleichwertig enthalten sind

Betrachten wir ein paar Beispiele aus dem gängigen Bereich der Adress- und Personendaten.

Zu 2.: ein Index {Geschlecht} lohnt sich auf der Tabelle Person nicht, da die Suchspalte nicht selektiv genug ist. (Eine Ausnahme wäre hier eine Anwendung für Frauenfragen beim Militär, da hier häufig nach einem seltenen Wert gesucht wird. Dies zeigt, dass selbst scheinbar einfache Fälle nicht ohne Überlegung entschieden werden können. Wieder einmal zeigt sich, vor welchen Problemen eine vollautomatische Indexierung steht.)

Zu 3.: der Indexkandidat {Ort} auf der Tabelle Adresse wird eliminiert, wenn es eine andere häufige Suchkombination gibt, zum Beispiel {Ort, Strasse}, in der {Ort} bereits vorkommt.

Dies kann selbstverständlich weitergeführt werden. Der dreispaltige Index {Nachname, Vorname, Alter} auf der Tabelle Person kann 3 verschiedene Suchen unterstützen:

{Nachname, Vorname, Alter}
{Nachname, Vorname}
{Nachname}

Die beiden letzten Suchkombinationen können also eliminiert werden.

An diesen Beispielen sieht man, dass die Wahl der richtigen Reihenfolge der Spalten entscheidend für die Qualität eines flexiblen Indexdesigns ist. Eine ideale Indexierung unabhängig von der Spaltenreihenfolge ist mit dem konventionellen B*-Index unmöglich.

3.3.3 Synthese

In diesem Schritt versucht man die verbleibenden Suchkombinationen zusammenzulegen und dadurch weitere Indexkandidaten zu eliminieren. So kann man beispielsweise einen Foreign Key um weitere Suchkriterien erweitern. Wenn zum Beispiel in der Tabelle Umsatz oft nach {Artikelnummer, Verkaufsdatum} gesucht wird, ist es sinnvoll, einen existierenden Foreign Key Index auf der Artikelnummer um das Feld Verkaufsdatum zu erweitern.
Ab und zu kann man auch durch eine leichte Verschlechterung des Indexdesigns einen zusätzlichen Indexkandidaten eliminieren.

Als Beispiel seien diese Suchkombinationen gegeben:

{Ort, Nachname, Vorname} und
{Ort, Nachname, Geburtsdatum}

Der Indexkandidat {Ort, Nachname, Vorname, Geburtsdatum} kann beide oben erwähnten Kombinationen ersetzen. Die Suche nach {Ort, Nachname, Geburtsdatum} ist zwar nicht mehr so optimal wie bei einem eigenen Index, jedoch kann die Verschlechterung wahrscheinlich in Kauf genommen werden, da {Ort, Nachname} für sich gesehen bereits gute Suchkriterien sind.

Man sieht, dass in diesem Schritt die rationale Entscheidung des menschlichen Designers besonders wichtig ist.

3.4 Umsetzung und Tests

Wenn diese Schritte durchlaufen sind, ist es relativ einfach, aus dem Ergebnis ein Index-Create-Skript zu erstellen. Natürlich muss man das Resultat nun ausführlich testen. RAT bietet sich dabei als Mittel der Wahl an. Wenn man in die Produktion geht, sollte man auch Überwachungsmechanismen einsetzen, die schnell in der Lage sind, fehlende Indexe zu identifizieren. Wir hatten ein eigenes Überwachungsskript, das Ausführungspläne findet, die durch eine suboptimale Indexierung entstehen, und entsprechende Verbesserungsvorschläge macht. Selbstverständlich kann man auch den Oracle-Index-Advisor verwenden, der hingegen für meinen Geschmack für diesen speziellen Zweck etwas umständlicher in der Handhabung ist.

Die neue Indexstruktur zeigte sich in der Produktion erstaunlich stabil. In den ersten Monaten musste nur eine einstellige Anzahl Indexe nacherzeugt werden.

3.5 Resultate

In dem hier geschilderten Fall handelt es sich um eine Applikation, die spezialisierte Consultants der Firma Oracle schon seit Monaten optimiert hatten. Umso erfreulicher war es, dass die Gesamtindexierung eine zusätzliche Platzersparnis von 30 Prozent und eine Performanceverbesserung von ebenfalls 30 Prozent erbrachte. Die Platzersparnis konnte im Folgenden weiter verbessert werden, da man nicht benutzte Foreign-Key-Indexe löschen konnte.

Ein sehr erfreulicher Nebeneffekt, der etwas überraschend kam, war eine höhere Stabilität der Execution-Pläne. Da für einen bestimmten Zweck nur noch ein Index zur Verfügung steht und nicht mehrere, bleiben die Pläne stabiler und verändern sich nicht so leicht in Abhängigkeit von den Werten der Bindevariablen.

Des Weiteren erleichterte die neue Indexstruktur mit ihren klaren Regeln und ihrer einheitlichen Namensgebung die tägliche Arbeit der DBAs.
Ein Leiter einer DBA-Gruppe, die dieselbe Applikation betrieb, überzeugte sich selbst bei einem Besuch von dem Resultat der Komplettindexierung. Er fasste seinen Eindruck wie folgt zusammen:

„Die DBAs hier wirken so entspannt. Das will ich auch.“

Literatur

[1] Borovica, Alagiannis, Ailamaki. „Automated Physical Designers: What You See is (Not) What You Get“, DBTest’12, May 21, 2012 Scottsdale, AZ, U.S.A.
[2] Weikum, Moenkeberg, Hasse, Zabback. „Self-tuning Database Technology and Information Services: from Wishful Thinking to Viable Engineering“, Proceedings of the 28th VLDB Conference, Hong Kong, China, 2002.
[3] Bruno, N., and Chaudhuri, S. 2007. Physical design refinement: The ,merge-reduce‘ approach. ACM Trans. Database Syst. 32, 4 (Nov. 2007), 28.
[4] Colgan M. How do I know what extended statistics are needed for a given workload?, Blogentry: https://blogs.oracle.com/optimizer/entry/how_do_i_know_what_extended_statistics_are_needed_for_a_given_workload.
[5] Oracle® Database Concepts 12c Release 1 (12.1), Kapitel 9, Locks and Foreign Keys und Kapitel 6, Indexes and Foreign Keys.[6] Oracle® Database 2 Day DBA 12c Release 1 (12.1) , Kapitel 8, Managing Indexes.

Rückfragen und Diso AG Unterstützung

Die Diso AG ist Teil der BigData Experten Gruppe micData AG und Spezialistin in der Verwaltung und Analyse von Daten (Business Analytics, BigData Analytics). Neben der Lizenzberatung und dem Aufbau von Datenbanken und Cloud Systemen, verfügt die Diso über vertieftes Know-How, wenn es darum geht die Performance von Datenbanken zu analysieren und substanziell zu verbessern. Gegenüber „vereinfachten“ Massnahmen im Hardwarebereich lassen sich durch Code Optimierungen kostengünstig und effizient Performanceprobleme beheben.

Für Fragen rund um unsere Dienstleistungen wenden Sie sich bitte direkt an Daniel Meienberg: dmeienberg@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.