25. Oktober 2007

XML Dokumente speichern: Die Wahl der Speicherungsformen ...

Heute wird das Thema XML-Speicherung fortgesetzt. Der letzten Post endete damit, dass die Speicherungsform für das XML doch massiven Einfluß auf die Performance hat. Doch welche Speicherungsformen gibt es überhaupt?
  • Textbasiert (CLOB)
  • Objektrelational
  • Neu in Oracle11g: Binary XML
Die jeweils verwendete Speicherungsform gibt man in der Storage-Clause der Tabelle an. Gibt man nichts an, so nimmt die Datenbank die einfachste Option: die textbasierte Speicherung. Nur ist diese, wenn man in die Dokumente hinein abfragen möchte, die langsamste Variante. Im diesem Post seht Ihr, wie Ihr die anderen Speicherungsformen nutzen und was das (Performance!) ausmachen kann. Für die objektrelationale Speicherungsform ist es wichtig, dass ein XML Schema vorliegt. Objektrelationale Speicherung bedeutet ja, dass die Datenbank die XML-Dokumente tatsächlich als Objekte ablegt - dazu muss sie allerdings wissen, wie die XML-Dokumente aussehen. Mit dem XML Schema machen wir der Datenbank die XML-Strukturen bekannt. Nun bauen wir uns also einen einfachen Testcase:
  1. Der folgende Code registriert das XML Schema, welches für die objektrelationale Speicherung benötigt wird, in der Datenbank. So werden die XML-Strukturen der Datenbank bekannt gemacht. Zum Parameter schemaurl ist noch zu sagen, dass dies ein einfacher Name ist; die Adresse muss nicht gültig sein. Man kann jeden beliebigen Text nehmen; mit Mondfinsternis würde es genauso funktionieren.
    begin
      dbms_xmlschema.deleteschema(
        schemaurl => 'http://sql-plsql-de.blogspot.com/xml-tests/schema.xsd',
        delete_option => dbms_xmlschema.delete_cascade_force
      );
    end;
    /
    
    begin
      dbms_xmlschema.registerschema(
        schemaurl => 'http://sql-plsql-de.blogspot.com/xml-tests/schema.xsd',
        schemadoc => '<?xml version="1.0" encoding="UTF-8"?>
                      <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
                       <xs:element name="test-xml">
                        <xs:complexType>
                         <xs:sequence>
                          <xs:element name="datum" type="xs:date"/>
                          <xs:element name="name" type="xs:string"/>
                          <xs:element name="gekaufte-stuecke">
                           <xs:complexType>
                            <xs:sequence>
                             <xs:element name="stueck" minOccurs="0" maxOccurs="unbounded">
                              <xs:complexType>
                               <xs:sequence>
                                <xs:element name="id" type="xs:string"/>
                                <xs:element name="name" type="xs:string"/>
                               </xs:sequence>
                              </xs:complexType>
                             </xs:element>
                            </xs:sequence>
                           </xs:complexType>
                          </xs:element>
                         </xs:sequence>
                        </xs:complexType>
                       </xs:element>
                      </xs:schema>'
      );
    end;
    /
    
  2. Als nächstes erzeugen wir die Tabellen:
  3. -- Tabelle für die textbasierte Speicherung
    create table xml_text of xmltype
    xmltype store as clob
    /
    
    -- Tabelle für die objektrelationale Speicherung
    create table xml_or of xmltype
    xmltype store as object relational
    xmlschema "http://sql-plsql-de.blogspot.com/xml-tests/schema.xsd"
    element "test-xml"
    /
    
    -- Tabelle für die Speicherung als BINARY XML
    create table xml_binary of xmltype
    xmltype store as binary xml
    allow nonschema
    /
    
  4. Nun geht es daran, die Tabellen zu füllen. Dazu generieren wir die XML-Dokumente mit ein wenig PL/SQL-Code. Nun wird es bereits interessant: Je nach Speicherungsform wird der Vorgang unterschiedliche Zeit benötigen. Man erfährt also schon etwas über die Speicherungsformen. Es macht also Sinn, sich die Ausführungszeiten im SQL*Plus mit set timing on anzeigen zu lassen.
    -- Prozedur erzeugen ...
    create or replace procedure populate_xml_table(
      p_count in number,
      p_table in varchar2
    ) is
      v_xmltext varchar2(32767); 
    begin
      for i in 1..p_count loop
        v_xmltext := '<test-xml>';
        v_xmltext := v_xmltext ||'<datum>'||to_char(sysdate + round(dbms_random.value*2000), 'YYYY-MM-DD')||'</datum>';
        v_xmltext := v_xmltext ||'<name>Kunde #'||to_char(round(dbms_random.value*10000))||'</name>';
        v_xmltext := v_xmltext ||'<gekaufte-stuecke>';
        for s in 1..round(dbms_random.value*20) loop
          v_xmltext := v_xmltext ||'<stueck>';
          v_xmltext := v_xmltext ||'<id>'||to_char(10000 + round(dbms_random.value * 1000))||'</id>';
          v_xmltext := v_xmltext ||'<name>Stueck #'||to_char(10000 + round(dbms_random.value*1000))||'</name>';
          v_xmltext := v_xmltext ||'</stueck>';
        end loop;
        v_xmltext := v_xmltext ||'</gekaufte-stuecke>';
        v_xmltext := v_xmltext ||'</test-xml>';
        execute immediate
           'insert into '||p_table||
           ' values (xmltype(:1).createschemabasedxml(''http://sql-plsql-de.blogspot.com/xml-tests/schema.xsd''))'
        using v_xmltext;
      end loop;
    end;
    /
    sho err
    
    set timing on
    
    -- 1. 5.000 XML-Dokumente für textbasierte Speicherung erzeugen
    
    begin
      populate_xml_table(5000, 'XML_TEXT');
    end;
    /
    
    PL/SQL-Prozedur erfolgreich abgeschlossen.
    
    Abgelaufen: 00:00:13.25
    
    -- 2. 5.000 XML-Dokumente für objektrelationale Speicherung erzeugen
    --    Kopieren der Dokumente aus XML_TEXT in XML_OR
    
    insert into xml_or (select * from xml_text)
    / 5000 Zeilen wurden erstellt. Abgelaufen: 00:00:43.56 -- 3. 5.000 XML-Dokumente für Speicherung als BINARY XML erzeugen -- Kopieren der Dokumente aus XML_TEXT in XML_BINARY insert into xml_binary (select * from xml_text)
    / 5000 Zeilen wurden erstellt. Abgelaufen: 00:00:02.07
  5. Nun haben wir drei Tabellen ... mit jeweils unterschiedlichen Speicherungsformen. Das lässt sich auch anhand der Pseudespalte XMLDATA feststellen ...
    set long 160
    set lines 80
       
    select e.xmldata from xml_text e where rownum = 1;
    
    XMLDATA
    --------------------------------------------------------------------------------
    <test-xml><datum>2008-08-18</datum><name>Kunde #731</name><gekaufte-stuecke><stu
    eck><id>10383</id><name>Stueck #10482</name></stueck><stueck><id>10808</id><name
    
    select e.xmldata from xml_or e where rownum = 1;
    
    XMLDATA(SYS_XDBPD$, datum, name, gekaufte-stuecke(SYS_XDBPD$, stueck(SYS_XDBPD$,
    --------------------------------------------------------------------------------
    test-xml1690_T(XDB$RAW_LIST_T('13070000000102'), '10.01.08', 'Kunde #4172', geka
    ufte-stuecke1691_T(XDB$RAW_LIST_T('1301000080800A'), stueck1693_COLL(stueck1692_
    T(XDB$RAW_LIST_T('130300000001'), '10354', 'Stueck #10579'), stueck1692_T(XDB$RA
    
    select e.xmldata from xml_binary e where rownum = 1;
    
    XMLDATA
    --------------------------------------------------------------------------------
    9F01039E000000C83EC4C0092441323031332D30332D3239C00A2B384B756E6465202331393335C8
    1F59C817C8C004129A3130333036C00C2B3853747565636B20233130303532D9D7D5C004129A3130
    
    Selektiert man die Tabelle "normal", so bekommt man in allen drei Fällen das gleiche XML zurück; es ist eben nur unterschiedlich gespeichert ...
  6. Nun kommen dann endlich die Tests: Wir bereiten die Inhalte nochmals, wie im letzten Post, relational auf. Das SQL dazu sieht dann wie folgt aus (um die Zeit auszublenden, die das SQL*Plus braucht, um alle Ergebnisse darzustellen, wird ein SELECT COUNT(*) außenrum gesetzt (wenn Ihr das Ergebnis selbst sehen wollt, führt einfach nur das innere SELECT aus):
    -- relationales Aufbereiten der textbasiert abgelegten XML-Dokumente
    
    select count(*) from (
    select
      extractvalue(object_value, '/test-xml/name/text()') as kunde_name,
      extractvalue(object_value, '/test-xml/datum/text()') as kauf_datum,
      extractvalue(value(st), '/stueck/id/text()') as stueck_id
    from xml_text,
      table(xmlsequence(extract(object_value, '/test-xml/gekaufte-stuecke/stueck'))) (+) st
    )
    /
    
      COUNT(*)
    ----------
         50027
    
    1 Zeile wurde ausgewählt.
    
    Abgelaufen: 00:00:05.43
    
    -- relationales Aufbereiten der objektrelational abgelegten XML-Dokumente
    
    select count(*) from (
    select
      extractvalue(object_value, '/test-xml/name/text()') as kunde_name,
      extractvalue(object_value, '/test-xml/datum/text()') as kauf_datum,
      extractvalue(value(st), '/stueck/id/text()') as stueck_id
    from xml_or,
      table(xmlsequence(extract(object_value, '/test-xml/gekaufte-stuecke/stueck'))) (+) st
    )
    /
    
      COUNT(*)
    ----------
         50027
    
    1 Zeile wurde ausgewählt.
    
    Abgelaufen: 00:00:00.17
    
    -- relationales Aufbereiten der als BINARY XML abgelegten XML-Dokumente
    
    select count(*) from (
    select
      extractvalue(object_value, '/test-xml/name/text()') as kunde_name,
      extractvalue(object_value, '/test-xml/datum/text()') as kauf_datum,
      extractvalue(value(st), '/stueck/id/text()') as stueck_id
    from xml_binary,
      table(xmlsequence(extract(object_value, '/test-xml/gekaufte-stuecke/stueck'))) (+) st
    )
    /
    
      COUNT(*)
    ----------
         50027
    
    1 Zeile wurde ausgewählt.
    
    Abgelaufen: 00:00:00.67
    
    Am besten lasst Ihr die Abfragen zweimal laufen und nehmt das zweite Ergebnis; dann hat man die Abfragezeiten einer "warmen" Datenbank. Die Unterschiede sind mithin recht eindrucksvoll:
    • Textbasiert: 5,43 Sekunden
    • Objektrelational: 0,2 Sekunden
    • Binary XML: 0,7 Sekunden
    Vergleicht man textbasiert mit objektrelational, so ergibt sich Faktor 25. Die Auswahl der richtigen Speicherungsform ist also ganz entscheidend für den (Projekt-)Erfolg. Diese Unterschiede kann man durch keine Tuning-Maßnahme mehr aufholen. Schauen wir uns nun den Speicherverbrauch der Abfragen an: Dazu öffnet man am besten drei Sessions und führt in jeder Session die Abfrage auf eine der Tabellen aus. Vorher setzt man mit in jeder Session einen Aufruf auf DBMS_APPLICATION_INFO ab, um einen "Marker" in der View V$SESSION zu setzen:
    begin
      dbms_application_info.set_action('TESTBASED[BINARY_XML][OBJECT_RELATIONAL]');
    end;
    /
    Als SYS lässt sich der Speicherverbrauch der Abfragen nun leicht feststellen:
    select
      ss.sid,
      s.action,
      sa.name,
      ss.value
    from v$sesstat ss, v$sysstat sa, v$session s
    where sa.statistic# = ss.statistic# and sa.statistic# = 25 and s.sid=ss.sid
    and s.username = 'SCOTT'
    
           SID ACTION               NAME                                VALUE
    ---------- -------------------- ------------------------------ ----------
           134 BINARY_XML           session pga memory                1177752
           143 TEXTBASED            session pga memory                7469208
           144 OBJECT_RELATIONAL    session pga memory                1439896
    
    Man sieht deutlich, dass die textbasierte Speicherungsform nicht nur am langsamsten ist, sondern auch den meisten Speicherplatz beansprucht: 7,1MB - die anderen beiden Varianten sind mit 1,1 bzw. 1.4 MB doch wesentlich bescheidener. Woher kommen diese Unterschiede ...? Stay tuned!

Kommentare:

Anonym hat gesagt…

Hallo Moritz! Prima Blog! Eine Frage bleibt m.E. noch: Gibt es einen Performanceunterschied in der XML-Abfrage zwischen "reinen" XMLTYPE-Tabellen und Tabellen, die neben einer XMLTYPE-Spalte auch noch Spalten mit anderen Datentypen besitzen? Also z.B. "CREATE TABLE TB_TEST (CO_ID NUMBER, CO_XML XMLTYPE)" vs. "CREATE TABLE TB_TEST2 OF XMLTYPE"? Danke & Gruß, Moritz

Carsten Czarski hat gesagt…

Hallo Moritz,

nein; dies hat keine Auswirkungen auf die Performance. Der Unterschied ist, dass die Zeilen in einer TABLE OF XMLTYPE durch eine Object-Reference (REF-Datentyp) angesprochen werden können - mit "normalen" Tabellen geht das nicht.
Wozu man das braucht? Zeilen in einer TABLE OF XMLTYPE können Einträge im virtuellen Dateisystem der XML DB (XML DB Repository) sein ... aber das führt jetzt zu weit, glaube ich ...

... muss ich vielleicht einen eigenen Post zu machen ...

Viele Grüße

-Carsten

Anonym hat gesagt…

Hallo Moritz,

Ich habe eine Prozedur analog
"populate_xml_table" erstellt und versuche einen Insert mit einem grösseren xml-Dokument (10.000 character) und bekomme nun stets einen "ORA-01461: can bind a LONG value only for insert into a LONG column", obwohl das eigentlich möglich sein müsste (bis 32k). Oracle Version ist 10.1.0.2.0.
Was kann das sein? Ein Bug in dieser Oracle Version?

Carsten Czarski hat gesagt…

Hallo,

nein, das ist kein Bug. Wenn Du das XML-Dokument per String-Literal in Deine Prozedur einbaust, also

xmltype('[xml-text ...]');

wird dieses String-Literal als VARCHAR2 aufgefasst und die 32k-Grenze schlägt zu. Man muss explizit eine Variable vom Typ CLOB definieren, einen temporary LOB erzeugen und damit den XMLTYPE konstruieren, also ...

declare
v_xmldoc clob;
begin
dbms_lob.createtemporary(v_xmldoc, true, DBMS_LOB.CALL);
v_xmldoc := '[xml-text] ....';
-- nun zusammenkonkatenieren ...
v_xmldoc := v_xmldoc ||' noch mehr xmltext...';

insert into ... values (xmltype(v_xmldoc));

end;

Hilft das weiter ...?

Beste Grüße

-Carsten

Beliebte Postings