Oracle Database/Tuning

Optimizer

Rule Based Optimizer / RBO

• Arbeitet bestimmte Regeln ab, nach denen die Abfrage optimiert wird
• Existiert noch in neueren Oracle-Versionen
• Verwendung wird nicht empfohlen, Weiterentwicklung wird von Oracle nicht weitergeführt

Cost Based Optimizer / CBO

• Existiert seit Oracle-Version 7, gut zu gebrauchen aber erst seit Version 8.1.7
• Berücksichtigt Anzahl und Verteilung der Daten
• Ausführungsplan wird anhand von Kosten (Prozessorzeit usw.) errechnet
• Benötigt Statistiken, um zu vernünftigen Ergebnissen zu kommen

Schritte des Optimizers:

• Berechnung von Ausdrücken und Bedingungen (z. B. "where kunde in (1,10)")
• während der "Statement Transformation" werden gegebenenfalls Unterabfragen in Joins (u. a.) umgewandelt
• Wahl des Optimizers (RBO oder CBO)
• Wahl des Zugriffspfades für jede Tabelle
• Wahl der Reihenfolge, in denen die Tabellen mit JOIN verknüpft werden
• Wahl der Verknüpfungsmethode (z. B. Nested Loops, Hash Join usw.)

Konfiguration

Der Oracle Optimizer kann über den Parameter OPTIMIZER_MODE eingestellt werden. Entweder über:

• Pfile/Spfile
• oder mit ALTER SESSION SET...

Einstellungen des Optimizers:

• RULE: damit wird zumeist der RBO verwendet; nicht mehr zu empfehlen, nicht mehr gültig in Oracle 10
• CHOOSE: (Standardwert in Oracle Database 9) damit wird im wesentlichen der CBO verwendet, wenn Statistiken vorhanden sind, ansonsten der RBO
• ALL_ROWS: Maximierung des Durchsatzes, alle Zeilen kommen so am schnellsten
• FIRST_ROWS: liefert die ersten Sätze möglichst schnell zurück, Durchsatz ist dafür in der Regel nicht so gut
• FIRST_ROWS_1, FIRST_ROWS_10, FIRST_ROWS_100, FIRST_ROWS_1000: gibt es seit der Version 9, hiermit kann noch genauer gesteuert werden, wie viele Sätze möglichst schnell geliefert werden sollen

Full Table Scan

Beim Full Table Scan werden alle Blöcke einer Tabelle gelesen. Er wird verwendet wenn:

• grosser Anteil von Zeilen einer Tabelle zurückgeliefert werden muss
• kleine Tabelle
• alte Statistiken (Tabelle hatte mal wenig Zeilen)

ROWID-Scan

Über die ROWID kann eine bestimmte Zeile am schnellsten gefunden werden. Dabei werden zuerst die ROWIDs bestimmt und dann die Zeilen anhand der ROWIDs gelesen.

Index-Scan

Liefert ROWIDs aus einem Index zurück. Falls nur indizierte Spalten ausgewählt werden, so werden die Daten nur aus dem Index beschafft, die Tabelle muss dann nicht gelesen werden.

CBO-Statistik

• Anzahl der Zeilen
• Anzahl der Blöcke
• Die Selektivität beschreibt, wie gut ein Abfragekriterium den Suchraum einschränkt. Viele verschiedene Werte ergeben eine hohe Selektivität. Je höher die Selektivität, desto wahrscheinlicher wird ein Index verwendet.
• Systemstatistiken liefern Informationen zum Prozessor und dem I/O-Subsystem:
• cpuspeed: Geschwindigkeit der CPU in MHz
• sreadtim: Zeit für das Lesen eines Blocks in ms
• mreadtim: Zeit für das Lesen mehrerer Blöcke
  • Festplatte, Arbeitsspeicher (ab Version 9)
  • Netzwerk (ab Version 10)

Befehl ANALYZE

• COMPUTE STATISTICS analysiert eine Tabelle vollständig
• Ergebnisse können im Data-Dictionary über USER_TABLES/ALL_TABLES/DBA_TABLES abgefragt werden
• Beispiel:

ANALYSE TABLE Mitarbeiter COMPUTE STATISTICS;

• ESTIMATE STATISTICS: analysiert nur einen bestimmten Prozentsatz der Tabelle
• Beispiel:

ANALYZE TABLE Mitarbeiter ESTIMATE STATISTICS 10 PERCENT;

Statistiken im Data Dictionary (Tabellen)

• Anzahl Datensätze (NUM_ROWS)
• Anzahl der Blöcke (BLOCKS)
• Leere Blöcke, die zwar angefordert, aber nie benutzt wurden (EMPTY BLOCKS)
• Durchschnittlicher freier Platz im Block (AVG_SPACE)
• Anzahl verketteter Datensätze (CHAIN_COUNT)
• Durchschnittliche Länge eines Datensatzes (AVG_ROW_LEN)
• Tiefe des Index vom Root-Block zu den Leaf-Blöcken (BLEVEL)
• Anzahl der Leaf-Blöcke (LEAF_BLOCKS)
• Anzahl unterschiedlicher Indexwerte (DISTINCT_KEYS)

usw.

Histogramme

• Haben Einfluss auf den verwendeten Ausführungsplan
• Histogramme für alle Spalten einer Tabelle erstellen:

ANALYZE TABLE Dept COMPUTE STATISTICS FOR ALL COLUMNS;

• Standardmässig werden die Spaltenwerte in 75 Bereiche aufgeteilt
• Sinnvoll vor allem für indizierte Spalten
• Machen keinen Sinn für Primärschlüsselspalten oder Spalten, für die ein Unique Constraint definiert wurde
• Die berechneten Daten können von den Views USER/ALL/DBA_HISTOGRAMS abgefragt werden

Bewertung der Performance

Bestimmte Verhältnisse statistischer Kennzahlen geben einen Überblick über die Performanz der Datenbank. Diese Zahlen können aus den folgenden dyamischen Performance-Views abgelesen werden:

• V$SYSSTAT
• V$SESSTAT

Zu den statistischen Grundkennzahlen gehören:

• consistent gets: wie oft wurden Blöcke im Consistent Read-Modus benötigt?
• db block gets: Wie oft wurden Blöcke im CURRENT Modus benötigt?
• logical reads = consistent gets + db block gets
• parse count: Alle Parse-Aufrufe
• physical reads: Anzahl Blöcke, die gelesen wurden
• recursive calls: Zugriffe auf das Data-Dictionary, die von Oracle selbst erzeugt wurden

Die Buffer Cache Hit Ratio zeigt an, wie oft die auf einen bestimmten Block zugreifenden Prozesse diesen Block im Buffer Cache finden. Der Wert sollte möglichst hoch (über 0.8) sein und kann mit folgender Formel berechnet werden:

Hit Ratio = 1 – physical reads / (consistent gets + db block gets)

Die Parse Ratio gibt an, wieviele Anweisungen aller SQL-Anweisungen vor der Ausführung erst noch geparst werden mussten. Sie sollte bei OLTP-Systemen sehr klein sein und kann nach folgender Formel berechnet werden:

Parse Ratio = sum(parse count) / sum(opened cursors cumulative)

Die Recursive Calls sind Abfragen auf das Data Dictionary. Formel:

Recursive Call Ratio = sum(recursive calls) / sum (opened cursors cumulative)

Performance Utilities

• EXPLAIN-PLAN: dieser SQL-Befehl erlaubt das Erzeugen eines Query Execution Plan
• SQL TRACE: dieser ALTER SESSION-Parameter erlaubt das Erzeugen von Trace-Dateien
• TKPROF: Mit diesem Utility werden Trace-Dateien, die mittels SQL_TRACE erzeugt wurden, in lesbare Form gebracht

Outlines sorgen für einen stabilen Ausführungsplan, da sich Ausführungspläne im Laufe der Zeit ändern können (etwa wegen Tabellenwachstum). Beispiel:

CREATE OUTLINE dept_scan FOR SELECT * FROM dept;

Wait Events erlauben es, im laufenden Betrieb zu sehen, worauf und wie lange eine Sitzung wartet. Sie können in verschiedenen V$-Views abgefragt werden:

• V$SESSION_WAIT
• V$SESSION_EVENT
• V$SYSTEM_EVENT

db file scattered read wird üblicherweise durch Full Table Scans verursacht:

• Prozess wartet auf Blöcke, die gelesen werden sollen
  • SQL-Anweisungen überdenken
  • weitere Festplatten hinzufügen
• Prozess wartet auf Blöcke, die für einen Index gelesen werden sollen
  • weitere Festplatten hinzufügen

free buffer waits treten auf, wenn der Server-Prozess keine freien Blöcke findet und signalisiert dann DWWR "dirty" Blöcke zurückzuschreiben.

• I/O-System ist langsam
• Buffer Cache ist zu klein

log buffer space : Server-Prozess wartet auf freien Platz im Redo-Buffer, LGWR schreibt nicht schnell genug

• Redo-Buffer vergrössern
• Archiver schreibt nicht schnell genug
• I/O-System zu langsam

log file sync Nach einem COMMIT müssen alle zugehörigen Redo-Einträge in die Online Redo Log-Dateien geschrieben werden

• I/O-System zu langsam
• Redo Log-Dateien auf andere Festplatten legen
• Raw Devices oder RAID-1 verwenden