Performance Tuning

In der ORDIX News 1/2007 haben wir den Datentyp XMLType vorgestellt. In diesem Artikel beleuchten wir die Möglichkeiten der Optimierung von Abfragen. Dabei soll im Detail vor allem auf das Query Rewrite und die Indizierung von XMLType eingegangen werden.

Werden XML-Dokumente in der Datenbank als XMLType abgelegt, so kann ihr Inhalt in Select-Statements einbezogen werden. An dieser Stelle werden die Möglichkeiten der Optimierung solcher Abfragen durch Query Rewrite und Indizierung vorgestellt.

Alle Beispiele beziehen sich auf die Daten der Tabelle, deren Insert-Statement in Abbildung 1 dargestellt ist. Das zugehörige, registrierte XML-Schema zeigt Abbildung 2. Die Beispiele wurden unter Oracle 10g erstellt.

Insert into auto values
  (1,'Peugeot',XMLTYPE('<?xml version="1.0" encoding="UTF-8"?>
  <Auto xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
    xsi:noNamespaceSchemaLocation=" 
    http://www.ordix.de/herstellerliste.xsd">
    <Herstellerliste>
    <Hersteller id="1">
    <Name>Motorenwerke R&#252;desheim</Name>
    </Hersteller>
    <Hersteller id="2">
    <Name>Best-Blech</Name>
  </Hersteller>
  </Herstellerliste>
  </Auto>'));

<?xml version="1.0" encoding="UTF-8"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" element
FormDefault="qualified" attributeFormDefault="unqualified">
  <xs:element name="Auto">
    <xs:complexType>
	<xs:sequence>
        <xs:element name="Herstellerliste">
	   <xs:complexType>
	     <xs:sequence>
	       <xs:element name="Hersteller" maxOccurs="unbounded">
		  <xs:complexType>
		    <xs:sequence>
		      <xs:element name="Name"/>
    		    </xs:sequence>
		    <xs:attribute name="id"/>
		  </xs:complexType>
		</xs:element>
	     </xs:sequence>
	   </xs:complexType>
	 </xs:element>
     </xs:sequence>
   </xs:complexType>
 </xs:element>
</xs:schema>

Objektrelational oder CLOB?

Schon die Speicherungsart des XML-Dokuments beeinflusst die Performance der Abfragen. In der letzten Ausgabe der ORDIX News wurden die beiden Speicherungsarten eingehend vorgestellt. Aber wann sollte welche Speicherungsart verwendet werden, um möglichst performant zu arbeiten?

Um diese Frage zu beantworten, sollte zunächst überlegt werden, welche XML-Daten abgespeichert werden und was nach dem Speichern mit den Daten passieren soll.

1. Unstrukturierte Speicherung als CLOB

Die Speicherung in einem CLOB ist dann vorteilhaft, wenn die Struktur der XML-Dokumente stark variiert oder wenn große Datenmengen zu speichern sind. Veränderungen in der Struktur sind für eine strukturierte Speicherung nicht hinnehmbar: Jedes gespeicherte Dokument muss zum registrierten XML-Schema passen.

Soll immer nur auf das gesamte XML-Dokument zugegriffen werden, so ist in jedem Fall die Speicherung in einem CLOB zu empfehlen. Damit lässt sich der Aufwand beim Einfügen und bei Abfragen verringern. Bei der strukturierten Speicherung muss das Dokument beim Einfügen analysiert und in mehrere Tabellen gesplittet werden. Bei Abfragen werden Dokumente wieder aus den einzelnen Tabellen zusammengesetzt. Diese Vorgänge können gerade bei sehr großen XML-Dokumenten viel Zeit in Anspruch nehmen.

2. Strukturierte, objektrelationale Speicherung

Muss dagegen (über XPath) auf einzelne Elemente des XML-Dokuments zugegriffen werden, so ist es vorteilhaft das XML-Dokument objektrelational abzuspeichern. Durch das Splitten des Dokuments auf Tabellen muss bei Abfragen oder Updates nicht mehr das gesamte XML-Dokument gelesen bzw. geschrieben werden. Ein gezielter Zugriff auf einzelne Elemente ist durch die strukturierte Speicherung möglich. Nur bei dieser Art der Speicherung ist ein Query Rewrite möglich.

Query Rewrite

Um per SQL auf einzelne Knoten des XML-Dokuments zuzugreifen, werden verschiedene XMLType-Funktionen verwendet. Die Lokalisierung der Knoten erfolgt bei den Funktionen durch XPath. Beispiele sind u. a.

• extract: Extrahiert Knoten aus dem Dokument.
• extractValue: Extrahiert genau einen Knoten.
• existsNode: Prüft, ob der Knoten existiert.

Bei der objektrelationalen Speicherung werden bei der Registrierung eines XML-Schemas diverse Objekte in der Oracle Datenbank angelegt. Wie zuvor beschrieben, werden die XML-Dokumente beim Insert gesplittet und in mehreren Datenbankobjekten gespeichert. Die Oracle Datenbank verwendet, sofern es möglich ist, automatisch den direkten Objektzugriff. D. h. SQL-Anweisungen werden so umgeschrieben, dass nicht auf das gesamte XML-Dokument zugegriffen wird, sondern auf die einzelnen Objekte, in denen Teile des XML-Dokuments gespeichert wurden.

Diese Art des Umschreibens von SQL-Anweisungen mit XMLType-Funktionen wird Query Rewrite genannt. In Abbildung 3 ist eine SQL-Anweisung zu sehen, die den Namen des Herstellers mit der ID 1 ausgibt. In Abbildung 4 ist sinngemäß dargestellt, wie diese Anweisung von Oracle intern umgeschrieben wird.

Select extractvalue(
  herstellerliste,
  'Auto/Herstellerliste/Hersteller[@id="1"]/Name/text()') "Name" 
  from auto;

select SYS_NC000013$ from auto where....;

Voraussetzungen für Query Rewrite

Ein Query Rewrite kann natürlich nur bei der objektrelationalen Speicherung stattfinden, denn bei der dokumentenbasierten Speicherung als CLOB sind die Strukturen der XML-Dokumente der Datenbank nicht bekannt. Außerdem kann ein Rewrite nicht bei allen SQL-Funktionen durchgeführt werden. Im Folgenden sind einige SQL-Funktionen dargestellt, bei denen u. a. ein Query Rewrite möglich ist:

• extract
• existsNode
• extractValue
• updateXML
• insertChildXML
• deleteXML
• XMLSequence

Nicht alle XPath-Ausdrücke können in ein SQL-Statement umgewandelt werden. Die we­sentlichen Einschränkungen sind:

• Auf Funktionen innerhalb des XPath-Ausdrucks muss (bis auf wenige Ausnahmen) verzichtet werden.
• Wildcards, die zu mehr als einem XML-Knoten führen, sollten nicht verwendet werden.
• Außer den XPath-Achsen "child" und "attribute" sollten keine XPath-Achsen verwendet werden.
• XPath-Variablen sollten nicht verwendet werden.
• In der Hierarchie darf immer nur zu den Kind-Elementen navigiert werden.

Kein Query Rewrite?

Werden die zuvor genannten Anforderungen nicht erfüllt, so kann kein Query Rewrite stattfinden. Die SQL-Anweisungen werden aber natürlich trotzdem ausgeführt. In diesem Fall wird zunächst das gesamte XML-Dokument gelesen. Anschließend wird im Hauptspeicher ein DOM-Baum aufgebaut und anhand des DOM-Baums die gewünschte Operation durchgeführt.

Die Erstellung eines DOM-Baums ist sehr ressourcenintensiv. Der DOM-Baum ist im Hauptspeicher etwa 5 bis 10 Mal so groß, wie das XML-Dokument auf der Festplatte. Ist das XML-Dokument auf der Festplatte also 500 KB groß, so benötigt es im Hauptspeicher schon 2,5 bis 5 MB. Wenn nun davon ausgegangen wird, dass in jedem Datensatz der durchsuchten Tabelle ein XML-Dokument analysiert werden muss, so führt dies leicht zu einem enormen Aufwand.

Die Erstellung von DOM-Bäumen sollte auf jeden Fall durch ein fehlerfrei laufendes Query Rewrite verhindert werden, wenn die SQL-Anweisung performant durchlaufen soll.

Tracing

Dauert eine SQL-Abfrage sehr lange, sollte zunächst untersucht werden, ob die Datenbank für diese Abfrage Query Rewrite anwenden kann. Dazu kann man, wie in Abbildung 5 beschrieben, dafür sorgen, dass eine Fehlermeldung ausgegeben wird, wenn ein Query Rewrite nicht möglich ist.

alter session set events ='19021 trace name context forever, level 0x1';

Wird jetzt eine SQL-Abfrage abgesetzt, bei der kein Query Rewrite möglich ist (z. B. eine Abfrage mit einer XPath-Wildcard), so wird, wie in Abbildung 6 dargestellt, eine Fehlermeldung ausgegeben. Allerdings lässt sich anhand der Fehlermeldung nicht identifizieren, warum das Query Rewrite bei der Abfrage nicht möglich war. Dafür muss ein erweitertes Tracing, wie in Abbildung 7 beschrieben, eingeschaltet werden.

select extractvalue(
  herstellerliste,
  'Auto/Herstellerliste/Hersteller[@id="1"]/Name/text()') "Name" 
  from auto;

from auto
      *
ERROR at line 2:
ORA-19022: XML XPath functions are disabled

alter session set events='19027 trace name context forever, level 0x2000';

War bei der Abfrage, wie in Abbildung 6, kein Query Rewrite möglich, so kann man anhand der Trace Files erkennen, welcher Teil der Abfrage zu dem Problem geführt hat. Damit die Trace Files geschrieben werden, muss man dafür sorgen, dass das Tracing von Oracle eingeschaltet ist. Im Oracle SQL Developer kann man das Statement z. B. über den Button "Autotrace" ausführen und so sicherstellen, dass Trace Files geschrieben werden.

Die Trace-Dateien findet man, sofern man die Standardeinstellungen von Oracle beibehalten hat, im Verzeichnis <Installationsverzeichnis>/admin/<Name der DB>/udump. In Abbildung 8 ist ein Auszug aus einem solchen Trace File dargestellt.

...
		  <xs:complexType>
		    <xs:sequence>
		      <xs:element name="Name" type="xs:string"/>
  </xs:sequence>
  <xs:attribute name="id" type="xs:int"/>
</xs:complexType>
...

In unserem Beispiel sehen wir im Trace File die Meldung "anytype with child axis". Dies deutet darauf hin, dass im XML-Schema bei einem Element vergessen wurde, einen Datentyp anzugeben und trotzdem der Kindknoten angesprochen wurde. In einem solchen Fall ist kein Query Rewrite möglich.

Auch welcher Knoten betroffen ist, wird im Trace File mitgeteilt. Es handelt sich um den Knoten "Name". In Abbildung 2 ist zu sehen, dass dem Element "Name" tatsächlich kein Datentyp zugewiesen ist.

Wurde anhand des Tracings identifiziert, was ein Query Rewrite verhindert, kann die SQL-Anweisung so umformuliert werden, dass ein Query Rewrite möglich wird. In unserem Beispiel muss also das XML-Schema angepasst werden. Für jedes Element und Attribut sollte der Datentyp eingetragen werden. In Abbildung 9 ist ein Auszug aus dem angepassten XML-Schema zu sehen.

...
		  <xs:complexType>
		    <xs:sequence>
		      <xs:element name="Name" type="xs:string"/>	
  </xs:sequence>
  <xs:attribute name="id" type="xs:int"/>
</xs:complexType>
...

Nach dem Umformulieren der SQL-Anweisung oder in unserem Fall des XML-Schemas kann bzw. sollte über das Tracing kontrolliert werden, ob die Umformulierung zu einem Query Rewrite führt. Wird beim Ausführen der Abfrage keine Fehlermeldung mehr ausgegeben, so kann davon ausgegangen werden, dass ein Query Rewrite erfolgt ist.

In Abbildung 10 ist die Abfrage dargestellt, für die das Query Rewrite durch die Oracle Datenbank durchgeführt werden konnte.

select extractvalue(
  herstellerliste,
  'Auto/Herstellerliste/Hersteller[@id="1"]/Name/text()') "Name" 
  from auto;
Name
--------------------------------------------------------------
Motorenwerke Rüdesheim

Indizierung

Query Rewrite ist nicht nur für sich allein gesehen für die Performance von SQL-Anweisungen auf XML-Dokumente in der Datenbank wichtig. Auch auf die Indizierbarkeit von Elementen der XML-Daten wirkt sich das Query Rewrite aus. Ohne ein Query Rewrite an dieser Stelle ist ein Index immer wirkungslos.

Es sollte also unbedingt darauf geachtet werden, dass ein Query Rewrite erfolgt. Abbildung 11 zeigt, wie ein Index auf XML-Daten angelegt werden kann. In unserem Beispiel ist es sinnvoll, einen Index auf das Attribut "ID" anzulegen. Auch beim Anlegen von Indexen wendet die Datenbank das Query Rewrite an.

CREATE TABLE auto(
   autoid NUMBER,
   autotyp VARCHAR2(100),
   herstellerliste XMLTYPE
)
XMLTYPE COLUMN herstellerliste
XMLSCHEMA "http://www.ordix.de/herstellerliste.xsd"
ELEMENT "Auto"
varray herstellerliste.xmldata."Herstellerliste"."Hersteller"
store as table hersteller_oct ((primary key (nested_table_id, 
array_index)) organization index)
/

Im Beispiel gibt es, wie in Abbildung 2 zu sehen ist, eine 1:n-Beziehung. In der Herstellerliste können sich mehrere Hersteller befinden. Die 1:n-Beziehung wird objektrelational als Array abgelegt. Standardmäßig wird ein Array als Byte-Strom in einem Attribut als CLOB abgespeichert. Um hier indizieren zu können, muss die 1:n-Beziehung in eine eigene Tabelle ausgelagert werden. Dies ist schon beim Anlegen der Tabelle zu beachten. In Abbildung 11 ist zu sehen, wie eine solche 1:n-Beziehung in eine eigene Tabelle ausgelagert werden kann.

Beim Anlegen der Tabelle wird zusätzlich die "Nested Table" hersteller_oct angelegt. Hier werden die Elemente der 1:n-Beziehung abgelegt. Für jedes Element ist eine Zeile in der Tabelle vorhanden. Auf diese Tabelle wird nun, wie in Abbildung 12 beschrieben, der Index angelegt, der bei Abfragen auf die Tabelle "auto", die als Suchkriterium Elemente der 1:n-Beziehung verwendet, genutzt wird.

create index idx_herstellerid on hersteller_oct("id");

Für die Tabelle sollten Statistiken erzeugt und vor allem auch aktuell gehalten werden, damit der Optimizer den Index auch tatsächlich richtig nutzt.

Vorgehensweise beim Tuning von Abfragen

Natürlich gibt es keine allgemeingültige Vorgehensweise, mit der Abfragen in jedem Fall beschleunigt werden können. Dazu bedarf es immer einer genauen Analyse im konkreten Projekt. Allerdings lässt sich aus den oben dargestellten Möglichkeiten des Tunings eine allgemeine Vorgehensweise ableiten:

1. Speicherungsform prüfen.
2. Query Rewrite sicherstellen.
3. Indexe erstellen, Statistiken aktualisieren, Optimizer Hints setzen, usw.

Fazit

Wie der Artikel zeigt, gibt es in der Oracle XMLDB einige Besonderheiten, die bei der Optimierung der Performance zu beachten sind. Konkrete Maßnahmen sind naturgemäß stark vom Projekt abhängig. Die dargestellten Einflussgrößen sind bei Abfragen mit XML-Dokumenten unbedingt zu berücksichtigen. Anhand von Testreihen sollte immer geprüft werden, ob die durchgeführten Maßnahmen tatsächlich zum Erfolg führten.

Kathrin Hammerschmidt (info@ordix.de).

Seitenanfang

Seite drucken

Seite weiterempfehlen