Hi all. I am having some performance issues when writing queries in a certain style (I can rewrite the queries to be faster, but most of the queries in our system are auto-generated and I'd really like to keep the existing syntax). I have posted a sample schema below, including the indexes that we have created and some test data. We have defined a few data types in our schema and then store our data with a sort of entity attribute value model (name / value pairs). I am using an XMLTable construct to shred the data into a relational table and then restricting the results from there. When restricting using relational operations on the XMLTable I am seeing really poor results (see examples below). On the flip side, when I use XMLExists to restrict the data then the performance is satisfactory. I am sure that I am doing something wrong with the query, the indexes or both and would be grateful if some of you more experienced XML guys could help me out. In particular I am interested in:
1. Are there any general suggestions regarding performance with the existing schema / index creation?
2. Is there something I am doing wrong in the 1st query that makes the performance so bad? Can I update the syntax or the indexes to boost performance?
(Note that I posted similar a question about this schema about a month or so ago and got some really helpful pointers. After a short break we tried to put that info to good use and this is where we are now.)
Oracle Version_
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
Register Schema_
BEGIN
DBMS_XMLSCHEMA.registerSchema(
enablehierarchy => DBMS_XMLSCHEMA.ENABLE_HIERARCHY_NONE ,
GENTYPES => TRUE, -- generate object types
GENBEAN => FALSE, -- no java beans
GENTABLES => TRUE, -- generate object tables
FORCE => FALSE,
--OPTIONS => DBMS_XMLSCHEMA.REGISTER_BINARYXML,
OWNER => USER,
SCHEMAURL => 'http://atestschema.com/test.xsd',
SCHEMADOC =>
'
<xs:schema attributeFormDefault="unqualified"
elementFormDefault="qualified"
xmlns:xs="http://www.w3.org/2001/XMLSchema"
xmlns:xdb="http://xmlns.oracle.com/xdb"
version="1.0"
xdb:storeVarrayAsTable="true">
<!-- Element name has restricted max length -->
<xs:simpleType name="elementName">
<xs:restriction base="xs:string">
<xs:maxLength value="30" />
</xs:restriction>
</xs:simpleType>
<xs:element name="dateField" xdb:defaultTable ="OS_DATA_DATE">
<xs:complexType>
<xs:simpleContent>
<xs:extension base="xs:dateTime" >
<xs:attribute type="elementName" name="name" use="required"/>
<xs:attribute type="xs:nonNegativeInteger" name="precision" use="required"/>
</xs:extension>
</xs:simpleContent>
</xs:complexType>
</xs:element>
<xs:element name="integerField" xdb:defaultTable ="OS_DATA_INTEGER">
<xs:complexType>
<xs:simpleContent>
<xs:extension base="xs:integer">
<xs:attribute type="elementName" name="name" use="required"/>
</xs:extension>
</xs:simpleContent>
</xs:complexType>
</xs:element>
<xs:element name="dataset" xdb:defaultTable ="OS_DATA_DATASET" >
<xs:complexType>
<xs:choice maxOccurs="unbounded" minOccurs="0">
<xs:element ref="dateField"/>
<xs:element ref="integerField"/>
</xs:choice>
</xs:complexType>
</xs:element>
</xs:schema>
',
LOCAL => TRUE );
END;
/
Create Test Table_
create table TEST_DATA (
ID NUMBER NOT NULL,
XML_DATA XMLTYPE
)
xmltype XML_DATA
STORE AS OBJECT RELATIONAL
XMLSCHEMA "http://atestschema.com/test.xsd"
ELEMENT "dataset"
;
Rename Ordered Collection Tables and Create Indexes_
---
-- RENAME COLLECTION TABLES
---
begin
DBMS_XMLSTORAGE_MANAGE.renameCollectionTable(USER,'TEST_DATA','XML_DATA','/dataset/integerField/@name','OCT_INTEGER');
end;
/
begin
DBMS_XMLSTORAGE_MANAGE.renameCollectionTable(USER,'TEST_DATA','XML_DATA','/dataset/dateField/@name','OCT_DATE');
end;
/
create index OCT_INTEGER_NAME_IDX on "OCT_INTEGER"("name") compute statistics;
create index OCT_INTEGER_VAL_IDX on "OCT_INTEGER"("name", "SYS_XDBBODY$") compute statistics;
create index OCT_DATE_NAME_IDX on "OCT_DATE"("name") compute statistics;
create index OCT_DATE_VAL_IDX on "OCT_DATE"("name", "SYS_XDBBODY$") compute statistics;
Load Test Data (50K records) and Gather Stats_
DECLARE
v_date1 VARCHAR2(500);
v_date2 VARCHAR2(500);
v_int1 NUMBER;
v_int2 NUMBER;
BEGIN
FOR v_cnt in 1..50000 LOOP
v_date1 := to_char(current_timestamp - NUMTODSINTERVAL(dbms_random.value(0, 1000), 'DAY'), 'YYYY-MM-DD"T"HH24.MI.SS.FF');
v_date2 := to_char(current_timestamp - NUMTODSINTERVAL(dbms_random.value(0, 1000), 'DAY'), 'YYYY-MM-DD"T"HH24.MI.SS.FF');
v_int1 := trunc(dbms_random.value(0, 1000));
v_int2 := trunc(dbms_random.value(0, 1000));
insert into TEST_DATA (id, xml_data) values (v_cnt, XMLType(
'<dataset>
<dateField name="date1">' || v_date1 || '</dateField>
<dateField name="date2">' || v_date2 || '</dateField>
<integerField name="int1">' || v_int1 || '</integerField>
<integerField name="int2">' || v_int2 || '</integerField>
</dataset>'));
commit;
END LOOP;
END;
/
begin
DBMS_STATS.gather_schema_stats('MYSCHEMA');
end;
/
Sample Query 1 (restrict to dates within 100 days using relational style)_
select count(*)
from test_data t,
XMLTable('$p/dataset'
PASSING t.xml_data as "p"
COLUMNS date1 TIMESTAMP PATH 'dateField[@name="date1"xml
where xml.date1 > (current_timestamp - NUMTODSINTERVAL(100, 'DAY'));
COUNT(*)
----------
4978
Elapsed: 00:00:11.794
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 39 | 139K|
| 1 | SORT AGGREGATE | | 1 | 39 | |
| 2 | FILTER | | | | |
| 3 | TABLE ACCESS FULL | TEST_DATA | 50000 | 1904K| 184 |
| 4 | SORT AGGREGATE | | 1 | 33 | |
| 5 | TABLE ACCESS BY INDEX ROWID| OCT_DATE | 1 | 33 | 3 |
| 6 | INDEX RANGE SCAN | SYS_C0035482 | 2 | | 2 |
-------------------------------------------------------------------------------
Sample Query 2 (restrict to dates within 100 days using XMLExists)_
select count(*)
from test_data t,
XMLTable('$p/dataset'
PASSING t.xml_data as "p"
COLUMNS date1 TIMESTAMP PATH 'dateField[@name="date1"xml
where XMLExists('$p/dataset/dateField[@name="date1"][xs:dateTime(.) > fn:current-dateTime() - xs:dayTimeDuration("P100D")]' passing t.xml_data as "p");
COUNT(*)
----------
4978
Elapsed: 00:00:00.298
----------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 47 | 285 |
| 1 | SORT AGGREGATE | | 1 | 47 | |
| 2 | NESTED LOOPS | | 2500 | 114K| 285 |
| 3 | SORT UNIQUE | | 2500 | 75000 | 284 |
| 4 | TABLE ACCESS FULL| OCT_DATE | 2500 | 75000 | 284 |
| 5 | INDEX UNIQUE SCAN | OCT_DATE_MIDX | 1 | 17 | 0 |
----------------------------------------------------------------------