Solved

Improving performance of EXTRACTVALUE XML DB

Posted on 2006-07-13
10
2,297 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
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

[Webinar] Learn How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

688 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