Direct I/O and XML Query Performance
imaione 2700007WSP Visits (8862)
When assessing performance for queries, particularly when performance is insufficient or when doing query performance tuning, assessing both CPU utilization and bufferpool I/O (especially data,index, and temporary physical reads) via snapshots or monitor routines are essential metrics which are fundamental to identifying what areas of the query processing are of greatest importance. When running queries which involve XML data, direct I/O can also be an important factor in query performance
Queries which have XML operations such as XMLquery predicates, or which fetch XML column data (for example via XML2CLOB) may end up doing temporary processing which involves direct I/O during query execution. As a simple example of a single table select which includes XML constructs:
SELECT C1, C2...
WHERE <other local predicates> AND
This query is assumed to contain some regular (non-XML) predicates on T1, as well as two XMLQUERY predicates which have to be applied on the XML data (in column MY_XML_COLUMN). The query also selects data from the XML column using the XML2CLOB scalar function. An explain plan for this query might look similar to the following:
The query plan includes an XSCAN operator which corresponds to the fetching of the XML column data to be returned via XMLCLOB, as well as the application of the XMLquery based predicates. When monitoring this type of query, check for direct I/O activity associated with the application handle and ensure that the I/O response times look adequate. Note also that these direct I/Os would generally be associated with temporary tablespace activity, although there is no explicit temp operation indicated in the explain graph:
In this example, although the average direct read time indicated by the snapshots looks fast, the direct write times (average 7002210 ms/1167414 = ~5ms/write) which is a very high number for write I/O. This would indicate a potential issue in the I/O subsystem which would likely create a serious bottleneck for the query's performance.
In addition to ensuring that direct I/O performance of the I/O subsystem is adequate, in some cases it may be possible to reduce the amount of direct I/O incurred by the query by creating appropriate XML indexes, if applicable. Note that using XML indexes may not always prevent such an XSCAN however. Depending on the type of XQuery predicates specified by the query, this may restrict whether an XML index can be used to apply those predicates. Also, if the query selects the full XML column data, as this one does, an XSCAN may still be required to fetch the data.