12. November 2007

XML in der Datenbank: Performance, SQL und EXPLAIN PLAN

Nach dem kurzen Ausflug zum CSV-Parser geht es nun weiter mit der Speicherung von XML-Dokumenten in der Oracle-Datenbank. Teil 1 zeigte, wie man XML-Dokumente grundsätzlich speichert und anschließend relational aufbereitet - Teil 2 stellte die verschiedenen Speicherungsformen vor und Teil 3 zeigte, was bei einer SQL-Abfrage auf die XML-Dokumente tatsächlich in der Datenbank passiert ist.
Heute beschäftigten wir uns mit XML-Abfragen und Ausführungsplänen - insbesondere für die objektrelationale Speicherungsform. Im Gegensatz zu den letzten Posts beschäftigen wir uns nun nicht mehr damit, alle XML-Inhalte relational aufzubereiten, vielmehr werden nun bestimmte XML-Dokumente mit der SQL WHERE-Klausel gesucht. Basis sind wieder die in Teil 2 erzeugten Tabellen. Beginnen wir mit einem einfachen Beispiel auf die objektrelational gespeicherten Dokumente. Das Dokument für Kunde #756 soll gefunden werden:
explain plan for 
select rowid from xml_or
where existsnode(
  object_value,
  '/test-xml[name="Kunde #756"]'
)=1
/

select * from table(dbms_xplan.display())
/

----------------------------------------------------------------------------
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        |    57 |   112K|   136   (0)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| XML_OR |    57 |   112K|   136   (0)| 00:00:02 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("XML_OR"."SYS_NC00009$"='Kunde #756')

Note
-----
   - dynamic sampling used for this statement
Interessant am Ausführungsplan ist die Predicate Information. Sie zeigt, dass mit einem Filter auf die Spalte "XML_OR"."SYS_NC00009$" gearbeitet wurde. Dies ist der Beweis, dass die Datenbank ein Query Rewrite durchgeführt hat: Der XPath-Ausdruck '/test-xml[name="Kunde #756"]' wurde umgeschrieben, so dass tatsächlich eine relationale Abfrage ausgeführt wurde.
Wenn wir das gleiche mit textbasiert gespeicherten XML-Dokumenten versuchen, sieht der Ausführungsplan etwas anders aus:

------------------------------------------------------------------------------
| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          |    51 |   100K|   371   (1)| 00:00:05 |
|*  1 |  TABLE ACCESS FULL| XML_TEXT |    51 |   100K|   371   (1)| 00:00:05 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(EXISTSNODE(SYS_MAKEXML("XML_TEXT"."XMLDATA"),'/test-xml[na
              me="Kunde #756"]')=1)

Note
-----
   - dynamic sampling used for this statement
Der XPath-Ausdruck aus der SQL-Abfrage findet sich genau so im Ausführungsplan wieder; das bedeutet, dass kein Query Rewrite stattgefunden hat - die Datenbank wird die XML-Dokumente zur Laufzeit der Abfrage parsen, was insbesondere bei großen Dokumentmengen eine Zeitlang dauern dürfte. Ein kleines Berechnungsbeispiel hierzu: Angenommen, ein Server schafft es, ein XML-Dokument in einer Millisekunde zu parsen. Liegen nun 1 Million Dokument in der Tabelle, dann ergeben sich (die Datenbank parst ja alle Dokumente zur Laufzeit) 1000 Sekunden, also in etwa 15 Minuten. Allein hierdurch wird deutlich. Greift man "in die XML-Dokumente hinein", so kommt die textbasierte Speicherung nicht mehr in Frage.
Der Vollständigkeit halber nun noch der Ausführungsplan für die Speicherung der XML-Dokumente als Binary XML:

----------------------------------------------------------------------------------
| Id  | Operation           | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |            |    32 | 64448 | 16633   (1)| 00:03:20 |
|*  1 |  FILTER             |            |       |       |            |          |
|   2 |   TABLE ACCESS FULL | XML_BINARY |  4311 |  8478K|   102   (0)| 00:00:02 |
|   3 |   NESTED LOOPS      |            |     2 |    12 |     5   (0)| 00:00:01 |
|   4 |    XPATH EVALUATION |            |       |       |            |          |
|*  5 |    XPATH EVALUATION |            |       |       |            |          |
----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter( EXISTS (SELECT 0 FROM XPATHTABLE('/test-xml' PASSING :B1 COLUMNS 
              "C_00$" XMLTYPE PATH '.')  "P",XPATHTABLE('/test-xml/name'
              PASSING SYS_XQSEQ2CON("P"."C_00$",1) COLUMNS "C_00$" XMLTYPE PATH '.',
              "C_01$" XMLTYPE PATH '/name')  "P1" WHERE
              SYS_XQ_UPKXML2SQL(SYS_XQEXVAL("P1"."C_01$",1,50),50,1,0)='Kunde #756'))

   5 - filter(SYS_XQ_UPKXML2SQL(SYS_XQEXVAL("P1"."C_01$",1,50),50,1,0)='Kunde #756')

Note
-----
   - dynamic sampling used for this statement
Auch hier sieht man, dass die Abfrage intern umgeschrieben wurde. Ausgeführt wird eine Abfrage, die auf die Strukturen der Speicherungsform Binary XML optimiert ist. Sie ist (wie bereits in Teil 2 dargestellt wurde) schon wesentlich schneller als die textbasierte Speicherung - an die objektrelationale Speicherung mit annährend relationaler Performance kommt sie jedoch nicht heran.
Im folgenden gehen wir noch ein wenig tiefer in die objektrelationale Speicherung hinein: Betrachten wir eine Suche in der XML-Hierarchie; es wird also nicht mehr nach einem bestimmten Kunden, sondern nach einem gekauften Stück gesucht ...
explain plan for 
select rowid from xml_or
where existsnode(
  object_value,
  '/test-xml/gekaufte-stuecke/stueck[name="Stueck #1782"]'
)=1
/

select * from table(dbms_xplan.display())
/

---------------------------------------------------------------------------------------------
| Id  | Operation                          | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |        |    32 | 60480 |  9305   (1)| 00:01:52 |
|*  1 |  FILTER                            |        |       |       |            |          |
|   2 |   TABLE ACCESS FULL                | XML_OR |  5653 |    10M|   136   (0)| 00:00:02 |
|*  3 |   COLLECTION ITERATOR PICKLER FETCH|        |       |       |            |          |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter( EXISTS (SELECT 0 FROM TABLE() "KOKBF$" WHERE
              SYS_OP_ATG(VALUE(KOKBF$),3,4,2)='Stueck #1782'))
   3 - filter(SYS_OP_ATG(VALUE(KOKBF$),3,4,2)='Stueck #1782')

Note
-----
   - dynamic sampling used for this statement
Auch die Suche in der XML-Hierarchie wurde komplett durch ein Query Rewrite umgesetzt - der XPath-Ausdruck findet sich im Ausfürhungsplan nicht mehr wieder.
Nun zur Indizierung von XML-Dokumenten: Bei großen Dokumentmengen wollen wir nicht unbedingt jedesmal einen Full Table Scan machen. Zunächst ein Beispiel für die Indizierung des Kundennamens (die erste Abfrage):
drop index idx_or_kundename
/

create index idx_or_kundename 
on xml_or (extractvalue(object_value, '/test-xml/name'))
/

explain plan for 
select rowid from xml_or
where existsnode(
  object_value,
  '/test-xml[name="Stueck #1782"]'
)=1
/

select * from table(dbms_xplan.display())
/

-------------------------------------------------------------------------------------
| Id  | Operation        | Name             | Rows  | Bytes | Cost (%CPU)| Time     | 
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |                  |    57 |   112K|     1   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| IDX_OR_KUNDENAME |    57 |   112K|     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("XML_OR"."SYS_NC00009$"='Stueck #1782')

Note
-----
   - dynamic sampling used for this statement
Man sieht am Ausführungsplan sofort, dass zur Ausführung nun ein Index genutzt wird; das gute an der objektrelationalen Speicherung ist, dass man normale, relationale Hilfsmittel zum Datenbanktuning nutzen kann. Versuchen wir als nächstes, die zweite Abfrage nach den gekauften Stücken mit einem Index zu unterstützen ...
... aber auf Anhieb wird das nicht funktionieren: Die "gekauften Stücke" sind eine 1:n-Beziehung - es gibt also mehrere gekaufte Stücke pro XML-Dokument. Beim Erstellen der Tabelle in Teil 2 wurden keine besonderen Angaben gemacht, also werden alle gekauften Stücke als Array zusammen mit dem Rest des XML-Dokumentes in eine Tabellenzeile gepackt. Nun gilt für einen relationalen Index "Indexblatt = Tabellenzeile": Wir wären also in der Situation, viele Indexblätter (gekaufte Stücke) auf ein- und dieselbe Tabellenzeile zeigen zu lassen ... das geht nicht.
Die Lösung des Problems: Wir legen die Tabelle etwas anders an und sorgen dafür, dass die "gekauften Stücke" in eine eigene Tabelle ausgelagert werden:
drop table xml_or
/

create table xml_or of xmltype
xmlschema "http://sql-plsql-de.blogspot.com/xml-tests/schema.xsd"
element "test-xml"
varray xmldata."gekaufte-stuecke"."stueck" store as table STUECKE_OCT ((
  primary key (nested_table_id, array_index)) organization index)
/

insert into xml_or (select * from xml_text)
/
In der Storage Clause geben wir an, dass das Array der gekauften Stücke ('varray XMLDATA."gekaufte-stuecke"."stueck"' in die index-organisierte Tabelle STUECKE_OCT ausgelagert werden soll. Nun kann darin das Element "name" indiziert werden.
desc STUECKE_OCT

 Name                                      Null?    Typ
 ----------------------------------------- -------- ---------------------------

 SYS_XDBPD$                                         XDB.XDB$RAW_LIST_T
 id                                                 VARCHAR2(4000 CHAR)
 name                                               VARCHAR2(4000 CHAR)
Probieren wir es: Der Index wird nun auf die Tabelle STUCKE_OCT erzeugt.

drop index idx_or_stueckname
/

create index idx_or_stueckname 
on STUECKE_OCT ("name")
/

explain plan for 
select rowid from xml_or
where existsnode(
  object_value,
  '/test-xml/gekaufte-stuecke/stueck[name="Stueck #1782"]'
)=1
/

select * from table(dbms_xplan.display())
/

----------------------------------------------------------------------------------------
| Id  | Operation          | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                   |     1 |  2034 |     3  (34)| 00:00:01 |
|   1 |  NESTED LOOPS      |                   |     1 |  2034 |     3  (34)| 00:00:01 |
|   2 |   SORT UNIQUE      |                   |     1 |  2012 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN| IDX_OR_STUECKNAME |     1 |  2012 |     2   (0)| 00:00:01 |
|*  4 |   INDEX UNIQUE SCAN| SYS_C009741       |     1 |    22 |     0   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("SYS_ALIAS_1"."name"='Stueck #1782')
   4 - access("NESTED_TABLE_ID"="XML_OR"."SYS_NC0001100012$")

Note
-----
   - dynamic sampling used for this statement
Wie man sieht, hat die Speicherungsform und u.U. das Tabellenlayout doch massiven Einfluß auf den Ausführungsplan einer SQL-Abfrage auf XML-Dokumente. Insbesondere bei großen Datenmengen oder performancekritischen Abfragen lohnt sich eine genauere Betrachtung des Themas.
Im nächsten Post werden wir uns ansehen, was man tun kann, wenn man zwar scheinbar alles beachtet hat ... es aber trotzdem nicht performant läuft: also Troubleshooting. Bis dahin ... Stay tuned!

Keine Kommentare:

Beliebte Postings