„Siedler-DBA Edition“ – oder das effiziente Ausnutzen von

Mittwoch, 9. November 2005
15h00, Musensaal
Database
„Siedler-DBA Edition“ – oder das effiziente
Ausnutzen von Ressourcen in der DB
Henrik Rünger
ckc ag, Hannover
Schlüsselworte:
Reorganisation, Partitionierung, Komprimierung, Monitoring, SQL*Plus Features, move, resize, shrink
Einleitung
Dieser Vortrag soll aufzeigen, dass es immer wieder wichtig sein kann, vorhandene Möglichkeiten, die die
DB bietet, zu nutzen. Anhand eines Beispielprojektes in dem die DB von 200GB (durch eine lückenhafte
Planung zu groß geraten) verkleinert werden soll und auch nur eingeschränkte Personalressourcen zur
Verfügung stehen sollen Ideen aufgezeigt werden mit denen dies schnell und effizient erledigt werden
kann. Dabei kommen Techniken wie „transportable Tablespaces“, „Table-Shrink“, „alter table move tablespace“, „Resize“, „Sicherungen“, „Import / Export“ zur Sprache. Praktisch einzusetzen sein soll dieser Vortrag als Kochbuch für DBA Tätigkeiten. Des Weiteren soll gezeigt werden, dass die Datenbank eine Vielzahl von Möglichkeiten bietet, die es zu entdecken gilt. Auf dem Weg zum besseren Verständnis der
Datenbank werden diese und andere Features kurz angerissen, so dass man bei Interesse die für sich wichtigen "Einstiegs-Infos" in den folgenden Wochen weiterverfolgen kann.
Das Problem
Nehmen wir einmal den Fall an, dass eine Datenbank geplant wird die für eine große Menge an Daten
ausgelegt ist (schätzungsweise 200GB) und aus diesem Grund mit den entsprechenden Statements angelegt wird. Aufgrund von Schätzungen wird angenommen, dass diese Datenbank innerhalb von kürzester
Zeit diese Menge von Daten enthalten wird und aufgrund des Haltens von Daten für nur einen bestimmten Zeitraum nicht weiter wachsen wird (siehe Abb. 1). Die Probleme an dieser Stelle sind zahlreich, sollte
sich die Schätzung doch nicht als ganz richtig erweisen:
1. Was passiert, wenn mehr Speicher gebraucht wird?
2. Was passiert, wenn weitaus weniger Speicher benötigt wird?
3. Wie wird mit der Sicherung in den ersten Wochen umgegangen, wenn die Datenbank noch nicht mit
Daten gefüllt ist?
4. wurde die Datafilegröße sehr klein gewählt (2 GB, ehemals Unix Grenze), so ist auch das Dateihandling bei einem Umzug sehr umständlich
Abb. 1: ursprüngliche Erstellung der Tablespaces
Das Ziel
Nachdem ein Export der produktiven Daten und Import in das Testsystem vorgenommen wurde, kam
heraus, dass die exportierten Daten nicht mehr als 11GB betrugen. Obwohl das System schon mehrere
Monate im Einsatz war, stieg der Datenbestand doch nicht auf 200GB an. In der Testdatenbank betrug die
Menge an Daten dann 56GB. Dies ist auf mehrere Gründe zurückzuführen:
Database
DB-Entwicklung
18. Deutsche ORACLE-Anwenderkonferenz
1. es sollten statt 100 Tage doch nur 60 Tage in der Historie gehalten werden
2. Indexe wurden entfernt, da Materialized für regelmäßige Auswertungen performanter waren
3. die Schätzung war inklusive Puffer und spiegelte somit nur eine ungefähre Maximalmenge des benötigten Speicherplatzes wieder
Daraus lassen sich mehrere Ziele ableiten, durchzuführen in der angegebenen Reihenfolge. Für die vorhandene Hardwareumgebung wurden bei der anschließenden Umsetzung nebenstehende Zeiten benötigt:
1. Sicherung der produktiven Datenbank (um im Notfall zurückgehen zu können)
• 3 Stunden
2. Export des entsprechenden Users, über den alle Daten angelegt wurden
• 1,5 Stunden
3. Vergrößern des Undo-Tablespaces, um bei den umfangreichen fachlichen Datenlöschaktionen keinen
Überlauf zu bekommen
• 30 Minuten
4. Löschen des Users inklusive Inhalten
• 1 Stunde
5. betroffene Tablespaces und Datafiles löschen
• 10 Minuten
6. Tablespaces und Datafiles mit neuen Einstellungen wieder anlegen
• 1,5 Stunden
7. Import der Userdaten inkl. Inhalten
• 3 Stunden
8. Löschen von fachlichen Daten, um nur 60 Tage zu historisieren
• 5 Stunden
9. Statistiken aktualisieren
• 7 Stunden
Externe Tools
Auch wenn es in Zeiten von Window – getriebenen Applikationen schwer fällt ein Argument für die Kommandozeile auszusprechen, so hat sie sich für diese Aufgabe doch am besten bewährt. Natürlich gibt es
zahlreiche Tools, inklusive des Oracle Enterprise Managers, die einen bei der Syntax unterstützen ohne
dass man immer wieder in der Dokumentation nachschauen muss. Insofern fällt mein Urteil über externe
Tools im Rahmen dieser Aufgabe auch so aus, dass es keinen Favoriten gibt, es aber von Vorteil ist, Tools
zur Seite zu haben, mit denen man sich noch einmal die grundlegende Syntax einzelner Befehle vor
Augen halten kann.
Aufgrund der langen Laufzeit einzelner Statements ist es sicherer, diese direkt auf dem Datenbankserver
auszuführen, um sich nicht dem Risiko auszusetzen, dass die Client-Verbindung abgebrochen wird. Wie
im weiteren Verlauf deutlich wird, ist die Zahl der einzugebenden Kommandos so gering, dass der Aufwand mit Tools eher höher ist, als wenn man es von Hand vornimmt.
Boardmittel
Da die Anzahl der Möglichkeiten in der Oracle Datenbank mittlerweile sehr zahlreich sind, bietet es sich
an, erst einmal zu schauen, was man davon nutzen kann um diese Aufgabenstellung zu lösen. Dabei lässt
sich die Aufgabe grob in drei Bereiche Teilen:
1. Backup und Recovery
2. DB-Änderungen
3. fachliche Daten performant löschen
Zu Punkt 1 gibt es dabei folgende Möglichkeiten:
Abb. 2: Backup und Recovery Varianten
Zu Punkt 2 bieten sich folgende Möglichkeiten an:
Abb. 3: technische Hilfsmittel, um Grössenänderungen vorzunehmen
Um fachliche Daten möglichst performant zu löschen, muss man natürlich ganz genau analysieren, wie
diese aufgebaut sind. In diesem Fall bezieht es sich auf eine geringe Anzahl von Tabellen (acht Stück),
ohne Constraints, mit einem bis zu 3 Indexes pro Tabelle. Die Inhalte variieren von 20 Millionen bis zu
130 Millionen. Gelöscht werden die Daten, indem eine Datumsspalte als Vergleichskriterium älter als
60 Tage benutzt wird:
Abb. 4: Möglichkeiten zum Löschen von fachlichen Daten
Umsetzungsmöglichkeiten
An dieser Stelle soll eine Auflistung der möglichen Befehle stehen, um sie für zukünftige Projekte benutzen zu können.
Das Anlegen eines Tablespaces mit neuen Datafiles. Um möglichst flexibel für zukünftige Datenmengen zu sein, empfiehlt es sich fast immer Datafiles automatisch wachsen zu lassen. Andererseits sollte es
allerdings einen gewissen Spielraum geben, indem sich dieses Wachstum nur bewegen kann. Da Datenbankadministratoren eigentlich nicht den gesamten Rechner zur Verfügung haben, sondern nur einen
bestimmten Speicherbereich, sollte eben auch der Speicherplatzbedarf schon innerhalb der Datenbank
begrenzt werden.
Database
DB-Entwicklung
18. Deutsche ORACLE-Anwenderkonferenz
Abb. 5: Tablespace anlegen mit Autoexend Möglichkeit
Ein Offline Backup, um im Fehlerfall reibungslos zum Ausgangspunkt zurück zu kommen. Wenn man
genug Speicherplatz zur Verfügung hat und die Datenbank auch nicht im Online Status bleiben muss, so
ist dies eine sehr sichere Variante. Dafür müssen folgende Schritte durchgeführt werden:
1. shutdown
2. Sichern der relevanten Dateien
– alle Datafiles
– Init.ora und spfile.ora (opt)
– alle Online Redo Logs
– alle Control Files
Beim Resize eines Datafiles kann sowohl das Vergrößern als auch das Verkleinern Ziel des Befehls sein.
Beim Verkleinern wird die Datei am Ende gekürzt. Das bedeutet, es dürfen keine Segmente am Ende der
Datei sein. Es gibt diverse Scripte und Möglichkeiten durch Dritthersteller die Datafilebelegung herauszufinden. Somit ist schnell zu erkennen, inwieweit ein Datafile verkleinert werden kann.
Abb. 6: Verkleinern oder Vergrössern eines Datafiles
Mit einem Table Shrink kann gearbeitet werden, wenn bekannt ist, dass es einzelne Tabellen gibt, die
eine Menge Speicherplatz für sich beanspruchen. Mit dem ersten Befehl wird geschaut, wie viel Speicherplatz die Tabelle wirklich braucht und mit dem zweiten Befehl kann sie dann auf die entsprechende
Größe verkleinert werden.
Abb. 7: Erkennen von freiem Platz und anschließender Freigabe
Klippen
Nicht unterschätzt werden sollte der Zeitbedarf, der für das Löschen durch Delete erforderlich ist. Es ist
von allen DML-Statements das Aufwendigste. Dafür sollte ein ausreichend großes Undo-Tablespace vorhanden sein.
Des Weiteren ist es zeitaufwendig die Statistiken neu zu erstellen. Aus dem Grund kann es durchaus sinnvoll sein, vorhandene Statistiken zu exportieren und im System später wieder einzuspielen.
Abb. 8: vorhandene Statistiken nutzen
Ebenfalls ein langwieriger Prozess ist das Neuerstellen von Indexes. Aus dem Grund sollte, sofern der Platz
vorhanden ist, die Möglichkeit des Online Rebuild gewählt werden
Abb. 9: Index online neu erstellen
Zusammenfassung
Abb. 10: Orientierung an diversen Möglichkeiten
Kontaktadresse:
Henrik Rünger
Wedekindstr. 1
D-30161 Hannover
Telefon: +49(0)163-605 7678
E-Mail
[email protected]
Internet: www.ckc.de
Database
DB-Entwicklung
18. Deutsche ORACLE-Anwenderkonferenz