My Oracle Support Banner

Master Note for Oracle XML Database (XDB) Performance (Doc ID 1407946.1)

Last updated on JUNE 03, 2019

Applies to:

Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Database Cloud Exadata Service - Version N/A and later
Oracle Database Cloud Service - Version N/A and later
Oracle Database - Enterprise Edition - Version 9.2.0.3 and later
Information in this document applies to any platform.
***** Checked for Relevance on 23-Jan-2016 *****

Details

You can create indexes on your XML data, to focus on particular parts of it that you query often and thus improve performance. This master note provides information for DBA's on tuning queries with XML Database (XDB).

Actions

BEST PRACTICES

This note does examples with both XQuery and XMLType functions ExistsNode, Extract and ExtractValue It is recommended from 10.2.0.1 onwards to always use XQuery for the following reasons:


                   XMLElement("Element_Name",
                   ( SELECT XMLAgg(XMLContent
                   ORDER BY XML_Column
                   )--XMLAgg
                   FROM XML_Table_Name WHERE Column_B = 'Y'


                  XMLElement("Element_Name"            

                  ( SELECT XMLAgg(XMLContent
                  -- ORDER BY XML_Column <== remove the clause  from XMLAgg()
                  )--XMLAgg
                  FROM XML_Table_Name WHERE Column_B = 'Y''
                  ORDER BY XML_Column    -- <== push the order into the regular query

 

 

TYPES OF STORAGE:


It is recommended to store XML content in the XMLType datatype. Otherwise, if storing an XML document in a CLOB or VARCHAR2 column then the database has no idea that its managing XML content. The descriptions of storage listed below, explain the various storage options in the XMLType datatype, which is XML aware so this means that it can process XML data efficiently. There are benefits and disadvantages for each storage type, so the decision which is the most effective for your company will depend on your needs and data content/structure. Typically, if you are using Oracle 11g or higher, binary XML storage is the closest thing to a universal storage model for XML data, that can be used effectively for a very wide range of use cases, from document-centric to data-centric.

The following link provides detailed description of each storage type, the advantages and disadvantages for each storage option and also most importantly, guides you how to choose the best storage option, given your use case:

http://www.oracle.com/technetwork/database/features/xmldb/xmlchoosestorage-v1-132078.pdf


CLOB Based Storage

 



 

Object Relational Storage

 

 


            

SQL> alter session set events '19027 trace name context
     forever, level 8192'



 

SQL> SELECT TABLE_NAME, PARENT_TABLE_COLUMN FROM
     USER_NESTED_TABLES
     WHERE PARENT_TABLE_NAME = '<YOUR_BASE_TABLE_NAME>';



Binary XML Storage

 

 

TYPES OF INDEXES:



Function Based Indexes

 

 

SQL> CREATE TABLE po_xml_tab (
2    poid number(10),
3    poDoc XMLType NOT NULL);

Table created.

SQL> INSERT INTO po_xml_tab
2    VALUES(100, XMLType('<?xml version="1.0"?>
3    <PO pono="1">
4    <PNAME>Po_1</PNAME>
5    <CUSTNAME>John</CUSTNAME>
6    <SHIPADDR>
7    <STREET>1033, Main Street</STREET>
8    <CITY>Sunnyvalue</CITY>
9    <STATE>CA</STATE>
10   </SHIPADDR>
11   </PO>'));

1 row created.

SQL> commit;

Commit complete.

SQL> DESC po_xml_tab
Name Null? Type
----------------------------------------- -------- ----------------------------

POID NUMBER(10)
PODOC NOT NULL PUBLIC.XMLTYPE STORAGE BINARY

SQL> set timing on
SQL> set autotrace traceonly explain

SQL> SELECT * from po_xml_tab
2    where ExtractValue (podoc, '//CITY/text()') = 'Sunnyvalue'

--------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

--------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 2015 | 3 (0)| 00:00:01 |

|* 1 | TABLE ACCESS FULL| PO_XML_TAB | 1 | 2015 | 3 (0)| 00:00:01 |

--------------------------------------------------------------------------------

SQL> CREATE INDEX city_FBI_index on po_xml_tab
2   (ExtractValue(podoc, '//CITY/text()'))

Index created.


SQL> SELECT * from po_xml_tab
2    where ExtractValue(podoc, '//CITY/text()') = 'Sunnyvalue'

--------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 2015 | 2 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| PO_XML_TAB | 1 | 2015 | 2 (0)| 00:00:01 |

|* 2 | INDEX RANGE SCAN | CITY_FBI_INDEX | 1 | | 1 (0)| 00:00:01 |


--------------------------------------------------------------------------------

For example speeding up existsnode():



SQL> SELECT * FROM po_xml_tab e
2    WHERE e.poDoc.ExistsNode('//SHIPADDR') = 1;

--------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

--------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 2015 | 3 (0)| 00:00:01 |

|* 1 | TABLE ACCESS FULL| PO_XML_TAB | 1 | 2015 | 3 (0)| 00:00:01 |

--------------------------------------------------------------------------------

SQL> CREATE INDEX po_index ON po_xml_tab
2    (poDoc.existsNode('//SHIPADDR'));

Index created.

SQL> SELECT * FROM po_xml_tab e
2    WHERE e.poDoc.ExistsNode('//SHIPADDR') = 1;


--------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 2015 | 2 (0)|
00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| PO_XML_TAB | 1 | 2015 | 2 (0)|
00:00:01 |

|* 2 | INDEX RANGE SCAN | PO_INDEX | 1 | | 1 (0)|
00:00:01 |

--------------------------------------------------------------------------------

For example speeding up XMLEXISTS():

SQL> SELECT XMLQuery('//CITY' passing podoc returning content)
2    from po_xml_tab
3    where XMLExists('/PO/SHIPADDR[CITY="Sunnyvalue"]' passing podoc);

XMLQUERY('//CITY'PASSINGPODOCRETURNINGCONTENT)
-----------------------------------------------------------------------------

<CITY>Sunnyvalue</CITY>


SQL> CREATE INDEX my_po_city_index ON purchaseorder x          (x.purchase_col_xml.XMLDATA."SHIPADDR"."CITY");






B*Tree/Bitmap Indexes

 


             

SQL> CREATE INDEX my_po_index ON purchaseorder x 
     (x.XMLDATA."Reference");




            

SQL> CREATE INDEX my_po_index
     ON purchaseorder x
     (x.purchase_col_xml.XMLDATA."Reference");



Oracle Text Indexes



SQL> CREATE INDEX po_text_index ON
2    po_xml_tab(poDoc) indextype is ctxsys.context;

Index created.

SQL> SELECT * FROM po_xml_tab w
2    WHERE CONTAINS(w.poDoc, John) > 0;

--------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 2027 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| PO_XML_TAB | 1 | 2027 | 4 (0)| 00:00:01 |

|* 2 | DOMAIN INDEX | PO_TEXT_INDEX | | | 4 (0)| 00:00:01 |

--------------------------------------------------------------------------------




XMLIndex

 

 





SQL> CREATE INDEX po_xml_tab_xmlindex on po_xml_tab(podoc)
2    indextype is xdb.xmlindex;

Index created.

SQL> SELECT XMLQuery('/PO/SHIPADDR/STREET' passing
2    podoc returning content)
3    FROM po_xml_tab
4    WHERE xmlexists('/PO/SHIPADDR[STATE="CA"]' passing podoc)
5    /

--------------------------------------------------------------------------------


| 0 | SELECT STATEMENT | | 1 | 3835 | 4 (25)| 00:00:01 |

| 1 | SORT GROUP BY | | 1 | 3524 | | |

|* 2 | TABLE ACCESS BY INDEX ROWID | SYS122252_PO_XML_TA_PATH_TABLE | 1 | 3524 | 2 (0)| 00:00:01 |

|* 3 | INDEX RANGE SCAN | SYS122252_PO_XML_TA_PIKEY_IX | 1 | | 1 (0)| 00:00:01 |

| 4 | NESTED LOOPS | | 1 | 3835 | 4 (25)| 00:00:01 |

| 5 | SORT UNIQUE | | 1 | 3823 | 2 (0)| 00:00:01 |

|* 6 | TABLE ACCESS BY INDEX ROWID| SYS122252_PO_XML_TA_PATH_TABLE | 1 | 3823 | 2 (0)| 00:00:01 |

|* 7 | INDEX RANGE SCAN | SYS122252_PO_XML_TA_VALUE_IX | 1 | | 1 (0)| 00:00:01 |

| 8 | TABLE ACCESS BY USER ROWID | PO_XML_TAB | 1 |

12 | 1 (0)| 00:00:01 |

--------------------------------------------------------------------------------


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

2 - filter(SYS_XMLI_LOC_ISNODE("SYS_P0"."LOCATOR")=1)
3 - access("SYS_P0"."RID"=:B1 AND "SYS_P0"."PATHID"=HEXTORAW('2767') )
6 - filter("SYS_P2"."PATHID"=HEXTORAW('3F73') AND "SYS_P2"."VALUE"='CA' AND
SYS_XMLI_LOC_ISNODE("SYS_P2"."LOCATOR")=1)
7 - access(SUBSTRB("VALUE",1,1599)='CA')






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

2 - filter("PURCHASEORDER"."SYS_NC00022$"='SBELL' AND
SYS_CHECKACL("ACLOID","OWNERID",xmltype('<privilege
xmlns="http://xmlns.oracle.com/xdb/acl.xsd"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://xmlns.oracle.com/xdb/acl.xsd
http://xmlns.oracle.com/xdb/acl.xsd DAV:http://xmlns.oracle.comb/dav.xsd"






SQL> CALL dbms_xdbz.disable_hierarchy('<PUT_YOUR_SCHEMA_NAME_HERE>',
     'PURCHASEORDER') /



UPDATEXML()



 

 

OTHER GOTCHAS TO IMPROVE XML PERFORMANCE

 

 

 


SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(
     tabname => '<PUT_THE_TABLE_NAME>'
     ownname => '<PUT_OWNER_OF_TABLE>',
     cascade => 'TRUE');

OR at schema level:

SQL> EXEC dbms_stats.gather_schema_stats('<PUT_SCHEMA_HERE>');

 

ANALYZING THE EXECUTION PLAN



For example, the example below means that no query rewrite is taking place:


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

1 - filter(CAST("PURCHASEORDER"."SYS_NC00021$" AS VARCHAR2(128))='Sarah
J. Bell' AND SYS_CHECKACL("ACLOID","OWNERID",xmltype('<privilege
xmlns="http://xmlns.oracle.com/xdb/acl.xsd"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://xmlns.oracle.com/xdb/acl.xsd
http://xmlns.oracle.com/xdb/acl.xsd DAV:http://xmlns.oracle.com/xdb/dav.xsd
"><read-properties/><read-contents/></privilege>'))=1)





SQL> CREATE INDEX requestor_index ON purchaseorder ("SYS_NC00021$");
     /** Internal relation column name for requestor **/

OR

SQL> CREATE INDEX requestor_index ON purchaseorder        
     ("XMLDATA"."REQUESTOR");
     /** SQLName **/

OR

SQL> CREATE INDEX requestor_index ON purchaseorder
     (cast("XMLDATA"."REQUESTOR" AS VARCHAR2(128)));
     /** Function Based Index on cast () that matches the query **/

OR

SQL> CREATE INDEX requestor_index ON purchaseorder po
     (XMLCast(XMLQuery('$p/PurchaseOrder/Requestor'
     PASSING po.OBJECT_VALUE AS "p"
     RETURNING CONTENT) AS VARCHAR2(128)));





SQL> SELECT XMLCast( XMLQuery ('$p/PurchaseOrder/Reference/text()'
     passing object_value as "p" returning content)
     as VARCHAR2(32)) "Reference"
     from PURCHASEORDER
     where XMLExists
     ('$p/PurchaseOrder/LineItems/LineItem/Part[@Id="717951002372"]'
     passing object_value as "p")
     /


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

|* 4 | TABLE ACCESS FULL | LINEITEM_TABLE | 1 | 50 | 13(0)| 00:00:01 |


In order to remove the FTS, create an index on the nested table:


SQL> CREATE INDEX lineitem_part_index
2    ON lineitem_table l(l.part.part_number, l.NESTED_TABLE_ID);

OR

SQL> CREATE INDEX iPartNumber on LINEITEM_TABLE
    (PART.PART_NUMBER, NESTED_TABLE_ID) compute statistics;


Then rerunning the query:


SQL> SELECT XMLCast( XMLQuery ('$p/PurchaseOrder/Reference/text()'
     passing object_value as "p" returning content)
     as VARCHAR2(32)) "Reference"
     from PURCHASEORDER
     where XMLExists
     ('$p/PurchaseOrder/LineItems/LineItem/Part[@Id="717951002372"]' 
     passing object_value as "p")
     /


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

4 | TABLE ACCESS BY INDEX ROWID| LINEITEM_TABLE | 1 | 50 |1 (0)| 00:00:01 |

 

 

COMMON PERFORMANCE ISSUES AND POSSIBLE SOLUTIONS:

 

Collection iterator pickler fetch

Collection Iterator Pickler Fetch means that the XML document or fragment is handled in memory. This should be avoided because it is serialized and dealt via a Pickler Fetch routine, which cannot be optimized by Oracle. Oracle doesn't have enough information to re-write the query in a more optimal form. If a high amount of data is being processed, this will result in expensive CPU and memory usage, and may exhaust PGA resources.

XDB has two ways of evaluating an XPath or XQuery: the SQL execution path and the functional execution path. COLLECTION ITERATOR PICKLER FETCH XMLSEQUENCEFROMXMLTYPE in the explain plan for a query implies that the query is being resolved using the functional execution path rather than the SQL execution path. Therefore, the XQuery or XPath expression could not be re-written into an equivalent SQL operation.

The functional evaluation execution path has the advantage that it can handle a wider range of Query and XPath expressions than the SQL execution path. However, it is far less efficient than the SQL execution path. Functional execution can be acceptable if only a small number of documents are being processed. But for operations on large documents or a large number of documents, functional evaluation is unlikely to yield satisfactory results from a performance perspective. There are a few things that can cause re-write to fail and force a functional evaluation (query is resolved by building a DOM):

The use of wildcards in an XPath expression. Avoid using wildcards.

Using the double forward slash (//element3) notation when searching for an element with XPath. Write the full XPath expression (/element1/element2/element3).

There are repeating elements or nodes that occur more than once (collections) which are not stored in nested tables or are stored inefficiently in lobs:

 

XDB Demo Scripts

You can go through a variety of Live XDB demos from the browser that provide a simple introduction to storing, indexing, updating and querying XML document with Oracle Database:

 

 

Contacts

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Details
Actions
 BEST PRACTICES
 TYPES OF STORAGE:
 CLOB Based Storage
 Object Relational Storage
 Binary XML Storage
 TYPES OF INDEXES:
 Function Based Indexes
 B*Tree/Bitmap Indexes
 Oracle Text Indexes
 XMLIndex
 UPDATEXML()
 OTHER GOTCHAS TO IMPROVE XML PERFORMANCE
 ANALYZING THE EXECUTION PLAN
 COMMON PERFORMANCE ISSUES AND POSSIBLE SOLUTIONS:
 Collection iterator pickler fetch
 XDB Demo Scripts
 
Contacts
References

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.