Database Software

We are using XDB 9.2.0.4. I am seeing a severe performance degradation when attempting to extract larger XML documents from XDB (somewhere over 3M). Smaller documents appear to be working fine.

I have been reading in the forum that the problem I am running into is most likely related to the storage model being used. ie) There are several repeating elements within the schema.

I have added xdb:storeVarrayAsTable="true" statement to the schema and re-registered. I can see, based on user_nested_tables, that XDB appears to be storing the repeating elements as nested tables vs varrays.

The change to the storage model does not seem to have significantly changed the queries performance.

The schemas I am using can be found at http://www.sasked.gov.sk.ca/xsd/sl/1.x/SLMessage.xsd & http://www.sasked.gov.sk.ca/xsd/sl/1.x/SDSElements.xsd
The schema documentation can be found at http://www.sasked.gov.sk.ca/sds/xml/SchemaDocumentation/SLMessage.html

The element /SL_Message/SL_Event/SL_ObjectData/SL_EventObject is the primary repeating element

I am using a table with an XMLType column
CREATE TABLE XML_SL_MESSAGE
(XML_SL_MESSAGE_ID NUMBER(11) NOT NULL
,DTE_TIMESTAMP TIMESTAMP DEFAULT SYSTIMESTAMP NOT NULL
,ORIGINAL_XML_SL_MESSAGE_ID NUMBER(11)
,VALID_SL_MESSAGE_XML sys.XMLType
,INVALID_XML CLOB
,ERROR_MESSAGE VARCHAR2(4000)
) xmltype column valid_sl_message_xml XMLSCHEMA "http://www.sasked.gov.sk.ca/xsd/sl/1.x/SLMessage.xsd" element "SL_Message"

The SQL I am using is attempting to bring the XMLType back as a clob, the query seems to be intensive in both CPU and I/O. (looks like it is the getClobVal function)
select xsm.xml_sl_message_id
,xsm.dte_timestamp
,nvl(xsm.valid_sl_message_xml.getClobVal(),xsm.invalid_xml) as xml_clob
,xsm.error_message
,xsm.original_xml_sl_message_id
from xml_sl_message xsm
where xsm.dte_timestamp > sysdate –1

I guess what I am wondering is what are my options ? Changing the storage model ? Applying Indexes ?

On an unrelated topic, Are there many differences in XDB 9.2.0.5 and 9.2.0.4 ? (I don’t believe 10g will be an option here … yet)

Thanx in advance
Trent