Home 
ORDIX News 1/2004
ORDIX News 1/2004
ORDIX News 1/2004
Das IT-Magazin der ORDIX AG mit Fachbeiträgen zu Datenbanken, Unix und Java/XML.
Im ersten Teil wurden I/O-intensive Systeme klassifiziert und typische Beispiele für I/O-Systeme aufgeführt. In diesem zweiten Teil erfahren Sie nun mehr über Werkzeuge zur Analyse von I/O-Problemen und Techniken zu ihrer Behebung.
Zur Analyse des Ist-Zustands bietet Oracle eine große Anzahl von Werkzeugen. Dieses Kapitel zeigt kurz auf, welches Werkzeug welche Stärken besitzt und für welche Einsatzgebiete es sich empfiehlt.
Statspack: Dieses Paket ist hervorragend geeignet, um einen Gesamteindruck eines Datenbanksystems zu bekommen. Im Bereich Wait Events lässt sich unmittelbar feststellen, wie viel Zeit für welche Form von I/O verbraucht wird (siehe hierzu auch weiter unten Wait Events). Im Bereich Top SQL, sortiert nach physical Reads, sind die SQL Befehle aufgeführt, die für die lesende Last verantwortlich sind. Schließlich findet sich im I/O-Teil die Anzahl und Aufteilung des I/O auf die verschiedenen Tablespaces und Data Files.
Trace Analyzer (Alternativ tkprof): Wurde eine einzelne, langsame Session identifiziert (z. B. ein Batch Job), so ist für diese der Trace einzuschalten. Zur Analyse des Ergebnisses dient dann das Werkzeug Trace Analyzer oder tkprof. Der I/O-Anteil am Job kann in der Zusammenfassung ermittelt werden. Multipliziert mit den Antwortzeiten aus Statspack lässt sich somit der Anteil für I/O-Operationen an der gesamten Zeit ermitteln. Darüber hinaus lassen sich mit der Auswertung leicht die I/O-intensivsten SQL Befehle identifizieren.
Handwerkszeug: Falls weitere Details für schwierige Fälle benötigt werden, so bietet Oracle hierfür zahlreiche Views an. In v$sql sind die historischen SQL Befehle inklusive der ausgeführten I/O gelistet, in den Views v$filestat und v$tempstat der I/O aus Sicht der Datenbankdateien. Welche Session momentan auf I/O wartet, lässt sich der View v$session_wait entnehmen.
Nach der Analysephase müssen nun Antworten auf die Frage gefunden werden, wie sich der I/O verringern lässt. Hier bietet Oracle zahlreiche Möglichkeiten.
SQL Tuning: Nach wie vor bietet das SQL Tuning die meisten Potenziale. Fehlen Indizes? Können zusammengesetzte Indizes einzelne ersetzen? Ermittelt der Optimizer den optimalen Ausführungsplan? Helfen Hints? Können SQL Befehle umformatiert werden? Diese Fragen müssen für die aufwendigsten SQL Befehle beantwortet werden. Ein sehr gutes Grundlagenwerk ist "Oracle SQL High-Performance Tuning (2nd Edition)" von Guy Harrison.
Memory Tuning I: Die Größe des DB Block Buffers bestimmt im Wesentlichen die Buffer Hit Ratio. Prinzipiell gilt, je mehr desto besser. Eine sehr gute Unterstützung bietet der DB Cache Advisor. Dieser zeigt an, wieviel I/O bei einer Vergrößerung des Buffers um n Prozent vermieden werden kann. Ansonsten sollte der Buffer so lange erhöht werden – physikalischen Speicher vorausgesetzt – wie I/O im gleichen Maße reduziert wird.
Memory Tuning II: Die Aufteilung auf verschiedene Block Buffer kann die Buffer Hit Ratio für bestimmte Segmente einer Applikation erhöhen oder verringern. Der Gesamteffekt ist in der Regel nur extrem schwer zu berechnen. Wir empfehlen diese Technologie nur dann, wenn viel Zeit für Tests und Messungen zur Verfügung steht.
EventsWartet eine Session auf ein Ereignis, so wird diese Wartezeit zusammen mit dem Ereignis (Event) protokolliert. Die folgende Liste zeigt die wichtigsten Events auf, an denen I/O-Probleme erkannt werden. Die Events können entweder über die View v$system_event oder mit utlbstat/utlestat bzw. mit dem Paket statspack ermittelt werden. Zur exakten Analyse ist der Initialisierungsparameter timed_statistics auf den Wert true zu setzen. db file parallel write: Der Database Writer schreibt Blöcke zurück in die Datendateien. In der Regel zeigt dieser Event kein I/O-Problem an, da das Schreiben von Blöcken asynchron erfolgt und die Oracle-Sessions nicht davon betroffen sind. Falls eine Session einen Block bearbeiten möchte, der gerade geschrieben oder gelesen wird, ist dies durch die Events buffer busy waits und free buffer waits zu erkennen. db file sequentiell reads: Dieser Event zeigt an, wie viel Zeit für das Lesen einzelner Blöcke verwendet wurde. Der Event tritt auf, wenn Zeilen über Indexzugriff gelesen werden. Bei hohen Wartezuständen ist die Buffer Hit Ratio – unabhängig davon, wie gut diese bereits ist – zu erhöhen. Dieser Event ist für nicht optimierte OLTP Systeme sehr typisch. db file scattered reads: Dieser Event zeigt an, wie viel Zeit für Full Table Scans bzw. Fast Full Index Scans verwendet wurde. Bei hohen Werten müssen Full Table Scans identifiziert und vermieden werden. Über den Parameter db_file_multiblock_read_count können verbleibende Full Table Scans optimiert werden. Dieser Event ist für nicht optimierte Data Warehouse Systeme relativ typisch. direct path read/direct path write: Dieser Event zeigt an, dass eine bestimmte Form von I/O gegenüber dem Betriebssystem abgesetzt wurde. Hierzu gehören Direct load Operationen (z. B. create table as select), Schreibvorgänge für LOB Daten, parallele DML Operationen oder Sortiervorgänge. Dieser Event sollte nur selten signifikante Werte annehmen. Falls dennoch hohe Wartezustände ermittelt werden, sollte das System auf die genannten Operationen hin untersucht werden. |
Kompression I: Indizes lassen sich ab der Version 8 komprimieren. Eine Komprimierung ist um so erfolgreicher, je mehr Wiederholungen es gibt. Auch hier gilt: Testen und Messen sind unbedingt notwendig. Dies lässt sich jedoch gut automatisieren.
Beispiel: Bei 100 Bundesbürgern kommt der Nachname im Schnitt 2 mal vor, der Vorname 5 mal. Ein Index auf Nachname, Vorname lässt sich nicht sinnvoll komprimieren, ein Index auf Vorname, Nachname bringt dagegen schon 20 % Platzersparnis und damit auch eine bessere Buffer Hit Ratio.
Kompression II: Mit Oracle 9 lassen sich nun auch Tabellen komprimieren. Obwohl diese Eigenschaft in erster Linie für Data Warehouse Datenbanken geplant war, lässt sie sich auch für OLTP Systeme hervorragend einsetzen.
Je mehr Zellen einer Tabelle innerhalb eines Oracle Blocks identisch sind und je mehr Byte diese belegen, desto eher lohnt sich ein Einsatz.
Indizes I: Überflüssige Indizes sollten entfernt werden, wenn viele Einfügeoperationen auf der zugehörigen Tabelle stattfinden. Mit dem Index Monitoring Feature lässt sich die Benutzung vorab prüfen. ACHTUNG: Das Löschen von dringend benötigten Indizes kann allerdings fatale Auswirkungen haben!
Indizes II: Tabellen können als Index Organized Table verwaltet werden. Dies spart den zusätzlichen Index für den Primary Key. Insbesondere bei Tabellen mit wenigen Spalten sollte ein Einsatz in Betracht gezogen werden.
Index Organized Tables können bei vielen Änderungen stark fragmentieren. Eine Online Reorganisation ist nur mit dem Paket dbms_redefinition möglich.
Indizes III: Manchmal hilft eine zusätzliche Spalte im Index, um ein Index Read Only zu ermöglichen. In einigen Fällen lässt sich die Performance damit drastisch verbessern.
Indizes IV: Indizes müssen regelmäßig reorganisiert werden.
Full Table Scans I: Falls der Full Table Scan die beste Zugriffsstrategie ist, lässt sich mit dem Parameter db_file_multiblock_read_count die Anzahl I/Os reduzieren und die Performance verbessern. Achtung: Der Optimizer neigt bei hohen Werten zu Full Table Scans. Mit dem Parameter optimizer_index_cost_adj kann gegengesteuert werden.
Full Table Scans II: Wird ein Full Table Scan parallel durchgeführt, so liest Oracle die Blöcke direkt in die PGA unter Umgehung des Block Buffers (physical reads direct), der Block Buffer wird dadurch nicht beeinträchtigt.
Partitionierung: Können die Daten nach logischen Kriterien partitioniert werden, so hilft diese Technologie häufig, um das relevante Datenvolumen zu reduzieren. Mit Oracle 9 steht mit der List Partitionierung auch ein Kriterium zur freien Einteilung zur Verfügung.
Sortierungen: Sortierungen auf der Platte sorgen für zusätzlichen I/O. Sortierungen sollten so weit wie möglich durch Indizes vermieden werden.
Durch den Parameter pga_aggregate_target lässt sich ab Oracle 9 ein gemeinsamer Pool für Sortierungen im Hauptspeicher einrichten.
Dieses neue Feature ersetzt die bisherigen Parameter sort_area_size und hash_area_size.
Materialized Views: Falls häufig ein Aggregat oder eine Teilmenge aus einem großen Gesamtbestand selektiert wird und der Datenbestand relativ wenigen Änderungen unterliegt, bietet sich die Verwendung von Materialized Views an.
Oracle stellt ein umfangreiches Paket zur Analyse und Reduzierung von I/O zur Verfügung. Mit Oracle 9 ist dieses Paket noch einmal mit wertvollen Neuerungen erweitert worden. Ist das Problem erkannt, können die geeigneten Maßnahmen ergriffen werden. Wir helfen Ihnen gerne dabei.
Martin Hoermann (info@ordix.de).