insert xmltype performance

From: jeroen <vansluisdamster_at_gmail.com>
Date: 14 Feb 2005 12:24:40 -0800
Message-ID: <67579b0c.0502141224.2cd69d0c@posting.google.com>

Hi,

I have a serious performance problem with a piece code where an xmltype is created from
a clob variable, trace file reports
heavy cpu usage, snapshots confirms this. Any ideas where to look further?

Problem arises clearly with bigger xml messages, around 10K or more Small messages seem to perform reasonable, no complaints.

Code is pretty simple
create or replace procedure insertCbsMessagenew(msgIN in CLOB,

                                             msgOUT out CLOB)  is

lv_id number;
waarde xmltype;

begin
  select msgid.nextval
  into lv_id
  from dual;
  waarde := xmltype.createxml( msgIN );

waarde := xmltype( xmlData=> msgIN, validated => 1, wellformed => 1 );

insert into cbs_xml_in (id, msg ) values (lv_id, waarde);

*********************************************************************8

table is built without a schema definition, just as plain xmltype Have experimented also with xmltype creation and setting attributes about schema checking
false, this doesn't seem to make any difference.

QL> desc cbs_xml_in

 Name                                      Null?    Type
 ----------------------------------------- --------
----------------------------
 ID                                        NOT NULL NUMBER
 MSG                                                SYS.XMLTYPE

Details: Oracle 9.2.0.4 HP-UX 11.11

snaphot piece:

Instance Efficiency Percentages (Target 100%)

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Buffer Nowait %: 100.00

Redo NoWait %: 100.00 Buffer Hit %: 99.59 In-memory Sort %: 99.68 Library Hit %: 96.32 Soft Parse %: 91.24 Execute to Parse %: 92.23 Latch Hit %: 100.00 Parse CPU to Parse Elapsd %: 79.59 % Non-Parse CPU: 98.73 Shared Pool Statistics Begin End ------ ------ Memory Usage %: 9.98 11.85 % SQL with executions>1: 48.34 69.91 % Memory for SQL

w/exec>1: 40.68 75.07 Top 5 Timed Events ~~~~~~~~~~~~~~~~~~ % Total

Event Waits Time (s) Ela Time

-------------------------------------------- ------------ -----------

-------- CPU time 31 93.85 log file parallel write 39 1 3.69 db file sequential read 878 1 2.04 log file sync 5 0 .26 control file parallel write 67 0 .13

Piece of tracefile

INSERT into cbs_msg_in (id, msg)
values ( :b2, :b1 )

call     count       cpu    elapsed       disk      query    current  
     rows

------- ------ -------- ---------- ---------- ---------- ----------

Parse        1      0.00       0.00          0          0          0  
        0
Execute      1     24.54      51.95         15        217        764  
        1
Fetch        0      0.00       0.00          0          0          0  
        0

------- ------ -------- ---------- ---------- ---------- ----------

total        2     24.54      51.95         15        217        764  
        1

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 143 (CBSSUPP) (recursive depth: 1)

Rows Execution Plan

-------  ---------------------------------------------------
      0  INSERT STATEMENT   GOAL: CHOOSE

********************************************************************************

Hope you can give help me

Tnx,

Jeroen Received on Mon Feb 14 2005 - 14:24:40 CST