Oracle-Datenbank automatisch optimieren

08.03.2007 von Lutz Fröhlich, Carsten Czarski und Klaus Maier
In diesem Artikel setzen wir die Optimierung der Oracle 10g Datenbank fort. Neben der Abhängigkeit zwischen Blockgröße und Datensatzgröße beschreiben wir noch die Möglichkeiten der automatischen Tuning-Features.

Datenbanken sind das Herz vieler Business-Applikationen oder Websites. Der Erfolg steht und fällt mit der Geschwindigkeit, in die Datenbank Anfragen bearbeitet. Kommt die Antwort auf eine Web-Anfrage zu spät, könnte der Besucher schon wieder verschwunden sein und anderswo bestellen.

In den ersten vier Teilen haben wir uns mit Verfahren beschäftigt, die Sie direkt über die Oracle-Datenbank steuern können, um die Leistungsfähigkeit zu verbessern. Der fünfte Teil drehte sich um den darunter liegenden Datenträger und wie Sie ihn optimal auf den Einsatz als Datenbank-Storage vorbereiten. In diesem letzten Teil vertiefen wir die Informationen über den Datenträger und zeigen Möglichkeiten auf, das Tuning von Datenbanken zu automatisieren.

Unsere neue Serie zu Oracle 10g basiert auf Kapitel 18 des Standardwerks „Oracle 10g“ von Fröhlich, Czarski und Maier aus dem Verlag Markt + Technik. Sie können dieses über 870 Seiten starke Buch auch in unserem Buchshop bestellen oder als eBook herunterladen.
Serie: Oracle Datenbank-Tuning

Teil 1: Grundlagen und grundsätzliche Überlegungen

Teil 2: Shared Pool konfigurieren

Teil 3: Buffer Cache optimieren

Teil 4: Der Redo Log Buffer

Teil 5: I/O optimieren

Teil 6: Tuning automatisieren

Row Chaining und Row Migration

Die Blockgröße hat auch Einfluss auf Row Chaining und Row Migration. Wenn ein Datensatz eingefügt wird und die Größe eines Datenblocks übersteigt, dann wird er in mehreren Blöcken untergebracht. Dieser Vorgang wird als Row Chaining bezeichnet.

Row Chaining ist schlecht für die Performance, da mehrere Blöcke für einen Satz gelesen werden müssen.

Vermeiden Sie Row Chaining, indem Sie eine hinreichend große Blockgröße wählen. In Oracle 10g können Tablespaces mit verschiedenen Blockgrößen in einer Datenbank verwaltet werden.

Row Migration tritt auf, wenn ein Datensatz geändert wird und der geänderte Satz so groß wird, dass er nicht mehr in den Block passt. Da die ROWID identisch bleiben muss, verschiebt Oracle in diesem Fall den kompletten Datensatz in einen neuen Block.

Row Migration ist schlecht für die Performance, da eine Menge Overhead mit der Verschiebung des Datensatzes in einen neuen Block ausgelöst wird. Sie können Row Migration vermeiden, indem Sie den Parameter PCTFREE erhöhen.

Mit der Abfrage in folgendem Listing können Sie herausfinden, ob Row Chaining oder Row Migration in Ihrer Datenbank ein Problem ist.

SQL> SELECT table_name, chain_cnt
2 FROM dba_tables
3 WHERE chain_cnt > 0;
TABLE_NAME CHAIN_CNT
-------------------- ----------
HOUSEHOLD_FACTS 134

Die Spalte CHAIN_CNT im View DBA_TABLES wird nur gefüllt, wenn die Tabellen analysiert sind. Tabellen können einzeln mit dem ANALYZE-Befehl analysiert werden. Sie können alternativ ein komplettes Schema mit der Prozedur ANALYZE_SCHEMA analysieren.

SQL> ANALYZE TABLE dwh.household_facts COMPUTE STATISTICS;
Tabelle wurde analysiert.
SQL> EXEC dbms_utility.analyze_schema('DWH','COMPUTE');
PL/SQL-Prozedur wurde erfolgreich abgeschlossen.

Oracle liefert das Skript utlchain.sql aus, mit dessen Hilfe Sie Row Chaining identifizieren und beseitigen können.

Row Chaining identifizieren und beseitigen

1. Führen Sie das Skript utlchain.sql aus. Es erstellt die Tabelle CHAINED_ROWS.

$ cd $ORACLE_HOME/rdbms/admin
$ sqlplus /nolog
SQL> connect / as sysdba
Connect durchgeführt.
SQL> @utlchain
Tabelle wurde angelegt.

2. Analysieren Sie die Tabelle mit der folgenden Anweisung.

SQL> ANALYZE TABLE system.accounts LIST CHAINED ROWS;
Tabelle wurde analysiert.

3. Jetzt können Sie sich die identifizierten Sätze in der Tabelle CHAINED_ROWS anschauen.

SQL> SELECT owner_name, table_name, head_rowid
2 FROM chained_rows;
OWNER_NAME TABLE_NAME HEAD_ROWID
------------------- ------------ ------------------
DWH ACCOUNTS AAAHpfAABAAASSSAAB
DWH ACCOUNTS AAAHpfAABAAASSSAAD
DWH ACCOUNTS AAAHpfAABAAASSSAAG
DWH ACCOUNTS AAAHpfAABAAASSSAAI
DWH ACCOUNTS AAAHpfAABAAASSSAAL
DWH ACCOUNTS AAAHpfAABAAASSSAAN
DWH ACCOUNTS AAAHpfAABAAASSSAAQ
DWH ACCOUNTS AAAHpfAABAAASSSAAS
DWH ACCOUNTS AAAHpfAABAAASSSAAV

Row Chaining beseitigen

4. Kopieren Sie sie im nächsten Schritt in eine temporäre Tabelle.

SQL> CREATE TABLE accounts_temp
2 AS SELECT * FROM accounts
3 WHERE rowid IN
4 SELECT head_rowid FROM chained_rows);
Tabelle wurde angelegt.

5. Jetzt können Sie die Sätze aus der Tabelle löschen und aus der temporären Tabelle wieder einfügen.

SQL> DELETE FROM accounts
2 WHERE rowid IN
3 (SELECT head_rowid FROM chained_rows);
9 Zeilen wurden gelöscht.
SQL> INSERT INTO accounts
2 SELECT * FROM accounts_temp;
9 Zeilen wurden erstellt.
SQL> COMMIT;
Transaktion mit COMMIT abgeschlossen.

6. Zum Schluss können Sie überprüfen, ob der Vorgang erfolgreich war.

SQL> ANALYZE TABLE accounts COMPUTE STATISTICS;
Tabelle wurde analysiert.
SQL> SELECT table_name, chain_cnt
2 FROM dba_tables
3 WHERE table_name = 'ACCOUNTS';
TABLE_NAME CHAIN_CNT
------------ ----------
ACCOUNTS 0

Das High-Watermark bei Full Table Scans

Ein Segment benutzt Datenblöcke, um Extents zu erzeugen. Oracle speichert die größte Blocknummer, die jemals von dem Segment benutzt wurde. Die Block-ID wird High Watermark genannt.

Das High Watermark wird nicht verändert, wenn Sätze gelöscht werden und damit leere Blöcke entstehen. Wenn ein Full Table Scan durchgeführt wird, dann liest Oracle alle Blöcke bis zum High Watermark, auch die leeren.

Das folgende Beispiel zeigt, wie ein High Watermark die Performance beeinflussen kann. Obwohl sich nur ein Datensatz in der Tabelle befindet, braucht die Abfrage sehr lange.

SQL> SELECT count(*) FROM household_facts3;
COUNT(*)
----------
1
Abgelaufen: 00:00:23.01

Oracle liest in diesem Beispiel eine große Anzahl leerer Blöcke. Wenn Sie sich in folgendem Listing die Blockzahlen anschauen, ist die Laufzeit nicht mehr verwunderlich.

SQL> SELECT table_name, empty_blocks, blocks
2 FROM dba_tables
3 WHERE table_name = 'HOUSEHOLD_FACTS';
TABLE_NAME EMPTY_BLOCKS BLOCKS
------------------------------ ------------ ----------
HOUSEHOLD_FACTS3 0 42736

Die Spalte EMPTY_BLOCKS enthält die Anzahl von Blöcken über dem High-Watermark, wogegen BLOCKS die Blöcke unter High-Watermark zählt. Da die Tabelle fast leer ist, existieren viele leere Blöcke.

Verwenden Sie für lokal verwaltete Tabellen mit automatischer Segmentverwaltung das Paket DBMS_SPACE für Informationen über unbenutzten Platz und zum Herabsetzen des High-Watermarks.

Optimierung von Sortieraktivitäten

Sortieroperationen finden im Hauptspeicher und auf der Festpatte statt. Operationen auf der Festplatte sind schlecht für die Performance. Deshalb lautet das Optimierungsziel, Sortieroperationen möglichst zu vermeiden. Sind sie unvermeidbar, dann sollte der Prozess möglichst im Hauptspeicher und nicht auf der Festplatte ablaufen.

Die folgenden SQL-Anweisungen bzw. -Klauseln verursachen Sortieroperationen:

Die Menge an Hauptspeicher, die für Sortieroperationen zur Verfügung steht, wird durch die Initialisierungsparameter SORT_AREA_SIZE und SORT_AREA_RETAINED_SIZE festgelegt.

Der Parameter SORT_AREA_SIZE bestimmt, wie viel Hauptspeicher für jeden Benutzerprozess für Sortieroperationen reserviert wird. Ist die Sortieroperation kleiner, wird sie komplett im Hauptspeicher ausgeführt.

Ist sie größer, so wird sie in Stücke zerteilt. Diese Stücke werden in die temporäre Tablespace geschrieben. Sind alle Einzelstücke sortiert, dann werden die Ergebnisse in einem abschließenden Lauf zusammengeführt.

Beim Shared Server wird die Sort Area eines jeden Benutzers in der User Global Area (UGA) angelegt.

Wenn der Sortiervorgang abgeschlossen ist, reduziert der Benutzerprozess die Größe der Sort Area auf die im Initialisierungsparameter SORT_AREA_RETAIND_SIZE festgelegte Größe.

Sortieraktivitäten können mit den Views V$SYSSTAT und V$SORT_SEGMENT überwacht werden. Außerdem können Sie, wie fast immer, auch den Performance Manager oder STATSPACK verwenden.

Das View V$SYSSTAT kennt die Statistiken sorts (memory) und sorts (disk). Das Verhältnis dieser Werte ist die In-Memory Sort Ratio.

SQL> SELECT a.value/(b.value + a.value) "Sort Ratio"
2 FROM v$sysstat a, v$sysstat b
3 WHERE a.name = 'sorts (memory)'
4 AND b.name = 'sorts (disk)';
Sort Ratio
----------
,999921036

In einer gut optimierten Datenbank ist die In-Memory Sort Ratio größer als 0,95. Das heißt, mehr als 95 % aller Sortieraktivitäten finden im Hauptspeicher statt.

Eine Verbesserung der Performance können Sie mit den folgenden Maßnahmen erreichen:

Sortiervorgänge vermeiden

Sortiervorgänge laufen lange und binden viele Ressourcen. Das Vermeiden von Sortier-Prozessen ist die erste und beste Maßnahme. Die folgenden Punkte geben einige Hinweise, wie Sie Sortiervorgänge vermeiden können.

Vergrößerung der Sort Area

Mit einer Vergrößerung der Sort Area werden mehr Sortier-Prozesse im Hauptspeicher durchgeführt.

Vergeben Sie die Größe der Sort Area vorsichtig. Beachten Sie dabei, dass eine Sort Area pro Benutzer angelegt wird. Bei 1.000 Benutzern und einer Sort Area von einem Mbyte wird schon mal ein Gigabyte Hauptspeicher benötigt, wenn alle Benutzer gleichzeitig sortieren. Stellen Sie deshalb sicher, dass eine Vergrößerung der Sort Area auch die gewünschten Effekte erzeugt.

Seit Oracle9i gibt es den Initialisierungsparameter PGA_AGGREGATE_TARGET. Er spezifiziert, wie viel Hauptspeicher in der PGA allen Server-Prozessen der Instanz zur Verfügung gestellt wird. Oracle versucht, die Größe des Private Memorys nicht über diese Größe hinaus anwachsen zu lassen.

Bestimmen Sie zum Setzen dieses Parameters, wie viel Hauptspeicher der Oracle-Instanz zur Verfügung steht, und subtrahieren Sie die Größe der SGA. Den Rest können Sie im Parameter PGA_AGGREGATE_TARGET spezifizieren.

Einrichtung automatischer Tuning-Features

Mit Oracle 10g wird eine Reihe von automatischen Tuning-Features eingeführt. Das Ziel ist eine dynamische Anpassung der Datenbankkonfiguration abhängig von den Bedürfnissen des aktuellen Workloads.

Das setzt natürlich voraus, dass den Tuning-Features Statistiken und Metriken bekannt sind. Die Sammlung der Statistiken übernimmt das Automatic Workload Repository (AWR). Statistiken über Sitzungen werden in der Active Session History (ASH) gespeichert.

Das Aktivieren von automatischen Tuning-Features führt nicht zwangsläufig zur Lösung aller Performance-Probleme in der Datenbank. Tritt z.B. während einer Peak-Zeit ein Engpass im Buffer Cache auf, dann versucht zwar das Automatic Memory Management (AMM), dem Buffer Cache mehr Shared Memory zuzuweisen, kann jedoch nicht über die Maximalgrenze der SGA hinausgehen, die durch den Parameter SGA_TARGET festgelegt wird.

Wollen Sie den Performance-Problemen auf den Grund gehen, dann müssen Sie die Datenbankkonfiguration kennen, so wie sie zu diesem Zeitpunkt bestanden hat. Die Informationen erhalten Sie ebenfalls aus dem AWR.

In diesem Fall würden Sie auch nicht generell den Buffer Cache vergrößern, da möglicherweise der Shared Memory außerhalb der Peak-Zeit von anderen Komponenten stärker benötigt wird. Sie würden den Parameter SGA_TARGET hoch setzen, um dem AMM zu ermöglichen, innerhalb der Peak-Zeit dem Buffer Cache mehr Shared Memory zuzuweisen.

Halbautomatische Tuning-Features

Viele automatischen Tuning-Features muss man bei genauerem Hinsehen als halbautomatisch bezeichnen. Das sind die so genannten Advisories.

Advisories verwenden ebenfalls das AWR als Basis für die Entscheidungsfindung, führen jedoch nicht automatisch eine Änderung der Datenbankkonfiguration durch. Der Datenbankadministrator trifft die Entscheidung, welche Empfehlungen umgesetzt werden sollen.

Zu den Advisories gehören:

Advisories erleichtern die Umsetzung der Empfehlung. Die Benutzung der Advisories kann mit PL/SQL-Paketen oder dem Enterprise Manager erfolgen. Zur Seite ZENTRALES ADVISORY im Enterprise Manager gelangen Sie über die Startseite der Datenbank. Den Link finden Sie am unteren Ende der Seite.

End to End Application Tracing vereinfacht den Prozess der Diagnose von Performance-Problemen in einer n-tier-Umgebung. Anfragen des Clients werden über eine Middle-Tier an die Datenbank weitergeleitet. Dabei ist es oft schwierig, die Session des Clients bis zur Datenbank zu verfolgen. End to End Application Tracing verwendet die Client-ID zur Verfolgung der Sitzung bis zur Datenbank. (mha)