Oracle 9i2 and XML performance

Hello,

I am working on a project using XMLType datatype.

We are storing few but very large XMLType documents. As a beginning, I thought using Oracle Text for speeding up queries, but it does not work because we are not searching data through XML but only extracting data for a single row (found with an Integer ID). As I have understood, Oracle Text allow You to search through multiples rows.

So my application is pretty slow. I have installed Statpack for monitoring the database ...

The problem is : the queries are takin huge CPU's and few consistent gets. I think the manipulations of XML Data made by Oracle are huge CPU consummers.

example of queries we have written :

SELECT extract(value(t),'./data @status').getstringval from patient

pat,              table(xmlsequence(extract(pat.pat_data,'//data'))) t
        where pat.pat_id=:b1

Our XMLData we are storing are from 300Kb up to 1Mb large.

Thx per advance,

Olivier

Has somebody an idea on howto speed up Received on Sun Jul 04 2004 - 05:49:00 CDT