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.
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:
-
ORDER BY
-
GROUP BY
-
SELECT DISTINCT
-
CREATE INDEX
-
ANALYZE
-
UNION
-
INTERSECT
-
MINUS
-
Joins zwischen Tabellen mit nicht indexierten Spalten
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
-
Vergrößerung der Sort Area
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.
-
Benutzen Sie, wenn möglich,
UNION ALL
anstelle vonUNION
. -
Stellen Sie sicher, dass Tabellen, die in einer Join-Operation verwendet werden, indexiert sind.
-
Erzeugen Sie Indexe auf Spalten, die in Anweisungen mit
ORDER BY
- undGROUP BY
-Klauseln verwendet werden. -
Benutzen Sie die
COMPUTE
-Option bei der Analyse von Tabellen und Indexen.
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:
-
Automatic Database Diagnostic Monitor (ADDM)
-
SQL Tuning Advisor
-
SQL Access Advisor
-
Memory Advisor
-
MTTR Advisor
-
Segment Advisor
-
Undo-Advisor
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)