19. November 2007

XML-Dokumente speichern: Was tun, wenn "Query Rewrite" mal nicht geht ...

Nun folgt der fünfte und letzte Teil der Posting-Reihe zum Thema XML in der Oracle-Datenbank und Performance. In den letzten Posts wurden die verschiedenen Speicherungsformen, das Query Rewrite und die Auswirkungen auf den Ausführungsplan betrachtet. Insbesondere die objektrelationale Speicherung hängt ganz massiv vom Query Rewrite ab: Wenn die XPath-Abfrage erfolgreich auf die objektrelationalen Strukturen umgeschrieben werden kann, wird sie mit annährend relationaler Performance ausgeführt. Wie gesehen, kann die Performance dann durch Tuning-Maßnahmen wie Indizes, Partitionierung oder andere nun noch weiter verbessert werden. Basis ist jedoch ein funktionierendes Query Rewrite!.
Manchmal stellt man jedoch fest, dass man zwar die objektrelationale Speicherung verwendet (man hat ein XML Schema registriert), die Abfrageperformance jedoch trotzdem nicht richtig stimmt ... Hierzu ein Beispiel: Auf die Tabelle XML_OR wird die folgende XPath-Abfrage abgesetzt (mit der XPath-Funktion contains machen wir eine Teilstring-Suche) - Trotz objektrelationaler Speicherungsform wird die Performance eher schlecht sein. Zur Diagnose fangen wir mit einem Ausführungsplan an ...
explain plan for
select * from xml_or e
where existsnode(value(e), '//name[contains(text(), "Kunde #2285")]')=1
/

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

| Id  | Operation          | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                   |  5000 |   258K|    32   (7)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN  | SYS_IOT_TOP_72422 |    10 |   400 |     3   (0)| 00:00:01 |
|*  2 |  FILTER            |                   |       |       |            |          |
|   3 |   TABLE ACCESS FULL| XML_OR            |  5000 |   258K|    29   (7)| 00:00:01 |

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("NESTED_TABLE_ID"=:B1)
   2 - filter(
         EXISTSNODE(
           SYS_MAKEXML('3EB815A238894F4EE040A8C08C021394',4753,"E"."XMLEXTRA","E"."XMLDATA"),
           '//name[text()="Kunde #2285"]'
         )=1
       )
Der XPath-Ausdruck findet sich im Ausführungsplan wieder - im letzten Post trat dies nur bei der textbasierten Speicherung auf. Dieser XPath-Ausdruck konnte jedoch offensichtlich auf die zweifellos vorhandenen objektrelationalen Strukturen umgeschrieben werden.
Doch warum ...?
Um diese Frage zu beantworten, müssen wir ein wenig tiefer in die Datenbank schauen. Das folgende Kommando aktiviert das Tracing für Query Rewrite:
alter session set events='19027 trace name context forever, level 0x2000'
Anschließend führen wir das EXPLAIN PLAN nochmals durch. Wichtig ist jedoch, dass das SQL für die Datenbank neu sein muss - wir fügen also einen Kommentar mit irgendeinem wirren Text dazu.
explain plan for
select /* asdkljkjf */ * from xml_or e
where existsnode(value(e), '//name[contains(text(), "Kunde #2285")]')=1
/

EXPLAIN PLAN ausgeführt.
Die Informationen zum Query Rewrite befinden sich nun in der Tracedatei. Man benötigt also spätestens jetzt Zugriff auf das Dateisystem des Datenbankservers: Bis Oracle10g befinden sich diese im udump-Verzeichnis, ab Oracle11g unter $ORACLE_BASE/diag/.../trace. Schaut man in die generierte Tracedatei hinein, so sollte sie in etwa so aussehen ...
NO REWRITE
        XPath ==> /child::test-xml/child::name[contains(text(), "Kunde #2285")]
        Reason ==> predicate not rewritable
        Current Location Path ==>
                axis:child
                nodetest:wildcard name
                strval:name
                slash:slash
Das Predicate ist der Teil innerhalb der eckigen Klammern. Schaut man nun in der XML DB Dokumentation nach, welche XPath-Konstrukte durch Query Rewrite auf objektrelationale Strukturen umgeschrieben werden können, so fällt auf, dass nur bestimmte XPath-Funktionen unterstützt werden: not, floor, ceiling, substring, string-length und translate. contains gehört nicht dazu. Die XML-Abfrage wird durchaus ausgeführt, allerdings wird sie nicht auf objektrelationale Strukturen umgeschrieben - die Performance entspricht also eher der textbasierten Speicherungsform.
Doch wie lösen wir das Problem?
Es gibt zwei Varianten:
  1. Verwenden der Funktion ora:contains
  2. Nutzen der SQL-Funktion LIKE
explain plan for
select /* asdkljdasjhklkjf */ * from xml_or e
where existsnode(
  value(e), 
  '//name[ora:contains(text(), "Kunde #2285") > 0]',
  'xmlns:ora="http://xmlns.oracle.com/xdb"'
)=1
/

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

----------------------------------------------------------------------------------------
| Id  | Operation          | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                   |   262 | 13886 |    23   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN  | SYS_IOT_TOP_72422 |    10 |   400 |     3   (0)| 00:00:01 |
|*  2 |  FILTER            |                   |       |       |            |          | 
|   3 |   TABLE ACCESS FULL| XML_OR            |  5000 |   258K|    23   (0)| 00:00:01 |
|*  4 |   INDEX RANGE SCAN | SYS_IOT_TOP_72422 |     1 |    40 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("NESTED_TABLE_ID"=:B1)
   2 - filter(SYS_XMLCONTAINS("E"."SYS_NC00009$",'Kunde #2285')>0 AND
              SYS_XMLCONV("E"."SYS_NC00007$",0,32,'3EB815A238894F4EE040A8C08C021394',1,4748,1)
               IS NOT NULL OR  EXISTS (SELECT 0 FROM "SCOTT"."STUECKE_OCT" "SYS_ALIAS_1"
              WHERE "NESTED_TABLE_ID"=:B1 AND SYS_XMLCONTAINS("SYS_ALIAS_1"."name",'Kunde
              #2285')>0 AND SYS_XMLCONV("SYS_ALIAS_1"."SYS_XDBPD$",0,32,'3EB815A238894F4EE040A
              8C08C021394',1,4750,1) IS NOT NULL))
   4 - access("NESTED_TABLE_ID"=:B1)
       filter(SYS_XMLCONTAINS("SYS_ALIAS_1"."name",'Kunde #2285')>0 AND
              SYS_XMLCONV("SYS_ALIAS_1"."SYS_XDBPD$",0,32,'3EB815A238894F4EE040A8C08C021394',1,4750,1) IS NOT NULL)
Der XPath-Ausdruck findet sich nicht mehr wieder, also hat das Query Rewrite funktioniert. Die Frage ist nur, ob man mit dem Ergebnis so richtig glücklich wird. ora:contains ist wesentlich mehr als ein einfacher Zeichenkettenvergleich - die Funktion macht eine Volltextrecherche im angesprochenen XML-Knoten. Um in großen Datenmengen performant suchen zu können, würde auf jeden Fall ein Volltextindex benötigt - mehr zu diesem Thema findet sich in der Oracle-Dokumentation. Gefragt war vielleicht eher eine einfache Wildcard-Suche, wie beim SQL-Like ... und auch dies ist erreichbar:
explain plan for
select /* asdkljdasjhklkjf */ * 
from xml_or e, table(xmlsequence(extract(value(e), '//stueck/name'))) n
where extractvalue(
  value(n), 
  '/name'
) like '%Kunde #2285%' or
extractvalue(
  value(e), 
  '/name'
) like '%Kunde #2285%'
/

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

-------------------------------------------------------------------------------------------
| Id  | Operation             | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                   |   244 | 22692 |   167   (2)| 00:00:03 |
|*  1 |  INDEX RANGE SCAN     | SYS_IOT_TOP_72422 |    10 |   400 |     3   (0)| 00:00:01 |
|*  2 |  HASH JOIN            |                   |   244 | 22692 |   167   (2)| 00:00:03 |
|*  3 |   INDEX FAST FULL SCAN| SYS_IOT_TOP_72422 |   244 |  9760 |   144   (2)| 00:00:02 |
|   4 |   TABLE ACCESS FULL   | XML_OR            |  5000 |   258K|    23   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("NESTED_TABLE_ID"=:B1)
   2 - access("NESTED_TABLE_ID"="E"."SYS_NC0001100012$")
   3 - filter(("SYS_ALIAS_1"."name" LIKE '%Kunde #2285%' OR NULL LIKE '%Kunde #2285%') 
        AND SYS_XMLCONV("SYS_ALIAS_1"."SYS_XDBPD$",0,32,'3EB815A238894F4EE040A8C08C021394',1,4750,1) IS NOT NULL)
Man sieht, dass auch in diesem Beispiel kein XPath-Ausdruck mehr im Ausführungsplan enthalten ist. In diesem Beispiel haben wir die Abfrage zwar gehörig umgeschrieben ... das Ziel (funktionierendes Query Rewrite) ist es jedoch auf jeden Fall wert.
Bitte behaltet stets im Hinterkopf: Ohne Query Rewrite wird die Abfrage trotz objektrelationaler Strukturen durch XML-Parsing ausgeführt - Indizes, Partitionierung und alle anderen Mittel der Datenbank zur Abfrageoptimierung bleiben ungenutzt. Das Sicherstellen von Query Rewrite ist bei der XML DB also eine fundamentale Tuning-Maßnahme!

Wenn sich also bei einer XPath-Abfrage herausstellt, dass Query Rewrite nicht funktioniert, dann sollte die erste Maßnahme stets sein, eben dieses sicherzustellen - im Notfall auch durch Umschreiben der eigentlichen Abfrage.

Keine Kommentare:

Beliebte Postings