Solved

Improving performance of EXTRACTVALUE XML DB

Posted on 2006-07-13
10
2,180 Views
Last Modified: 2008-01-09

I have posted many times here in the last few weeks.  The main problem I am have is this select statement.
If I have a count of 5000 as above then I have to crate a loop from 1 to 500 where I pass in i . The bellow statement takes more then a second.

select EXTRACTVALUE(SYS_NC_ROWINFO$,'/PDATA/EVH[i]') "EVH"
from XMLTABLE
WHERE EXISTSNODE (SYS_NC_ROWINFO$, '/PDATA/EVH') = 1;

I will have to process 640 million recs in 8 hours possible? Files will arrive in no greater then 1.5 MB.

if the above takes 2 seconds.
What I retrieve is <EVH>1~2~3~4~5~6~7~8~9~10||1~2~3~4~5~6~7~8~9~10||n times </EVH>

This has to be parse using || delim which represents 1 rec.
This has to be parsed 1~2~3~4~5~6~7~8~9~10  using ~ then inserted or update a table.

So far the EXTRACTVALUE is the bottle neck I really need to improve this. I just posted another question on how to register xsd to see if that will speed thins up.

I am also directly loading xml file into a clob data type.

I basically have two approaches load to xmltype table or direct from xml.

Because the xml file is fairly simlpe 3 tags 1 of which has embeded || and ~ does it make more sesne to build my own parse using instr to sepreate out hdr , evh and evd tags.

I am storing the parsed values in a nested table, is this the most efficient, maybe varray?
Could the temp values be stored in memory to avoid disk reads.
Can I use bulk loading techniques?

Also I am working on a database that was setup by someone else.
There there are only 4 tablespaces
system
sysaux
undo
temp
the system and sysaux are 99% full. This will slow processing down for sure.
The table and index are also use system table space, I can create new table space for table data and for index.
We do not have tablespaces on different disks. That could be an improvement placing tablespaces on diff disks.

Does the XDB schema default to system tablespace? Should that have its own tablespace?

All the code is on www.ihos-t.com/code

Lots of questions, in crisis mode at this time. I don't think it can be done but I want to get to a stage where at least its not dog slow.
Thanks in advance.


0
Comment
Question by:dplinnane
  • 5
  • 4
10 Comments
 

Author Comment

by:dplinnane
ID: 17102433
hERE ARE SOME state from v$sqlarea  this is limiting the loop to 50 i= 1 to 50

DISK_READS      BUFFER_GETS      PARSE_CALLS      USER_IO_WAIT_TIME      ROWS_PROCESSED

11968                              31173      3      4236831      5
144704                              376860      3      67324519      66
144704                              376860      3      68225328      66

for HDR, EVH and EVD
select EXTRACTVALUE(SYS_NC_ROWINFO$,'/PDATA/EVH[i]') "EVH"
from XMLTABLE
WHERE EXISTSNODE (SYS_NC_ROWINFO$, '/PDATA/EVH') = 1;
0
 
LVL 14

Expert Comment

by:sathyagiri
ID: 17103690
Instead of using extractvalue , did you try using XSL style sheet to transform your data ?
0
 

Author Comment

by:dplinnane
ID: 17103750
I'm new to dealing xml so I don't know all the options how would I go about that?
0
 
LVL 14

Expert Comment

by:sathyagiri
ID: 17104918
One other suggestion ,try creating the following indexes and see if you see any improvements

CREATE INDEX XML_fn_index ON XMLTABLE e
   (EXTRACTVALUE(VALUE(e),'/PDATA/EVH'));


CREATE INDEX xml_index1 ON xmltable(SYS_NC_ROWINFO$) indextype is ctxsys.CTXXPATH;

0
 

Author Comment

by:dplinnane
ID: 17105259
FOr the first indes I get
ORA-19025: EXTRACTVALUE returns value of only one node

The second index I get
ORA-19102: XQuery string literal expected



0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 14

Expert Comment

by:sathyagiri
ID: 17105310
0
 
LVL 16

Accepted Solution

by:
MohanKNair earned 500 total points
ID: 17105836
>> I will have to process 640 million recs in 8 hours possible? Files will arrive in no greater then 1.5 MB.

Use C based external procedures to do faster processing. Write a C code to parse the XML files. The C code returns a string after filtering the data.
http://www.stanford.edu/dept/itss/docs/oracle/10g/appdev.101/b10795/adfns_ex.htm

Or use Pro*C to read and parse XML files and insert records into tables
http://www.cs.umbc.edu/help/oracle8/server.815/a68022/toc.htm
0
 

Author Comment

by:dplinnane
ID: 17105949
I'm not a C programmer or Pro C* I'm looking for improvements in what I alreay have.
0
 
LVL 14

Expert Comment

by:sathyagiri
ID: 17115166
Did you try creating the index? You can create CTXSYS.CONTEXT index and see if there's any change in your query performance
0
 

Author Comment

by:dplinnane
ID: 17424472
Had to write my own parser in the end , xml functionalit was way to slow. I will always be dealing with 4 tags. The performance gains were huge.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now