Re: XML queries and Excessive Network Traffic

From: Sayan Malakshinov <xt.and.r_at_gmail.com>
Date: Sat, 19 Jul 2014 01:56:35 +0400
Message-ID: <CAOVevU4R_6Vo6J=g-Tud1RGr=5fUzDPOY7T8oP67+3POuWKBRQ_at_mail.gmail.com>

I've made a couple tests:

set echo on feed on;
create table xtest as
select a as v

      ,xmltype(a) as x
      ,to_clob(a) as c

from (select '<a>'||lpad(1,3900,1)||'</a>' a from dual connect by level<=1000);
set arrays 1000;
set autot trace stat;

select v,v,v from xtest;
select x,x,x from xtest;
select c,c,c from xtest;

set autot off;
set echo off feed off;

Part of the output(full output in the attachment):

SQL> select v,v,v from xtest;

1000 rows selected.
Statistics

---

<skipped>

      29001  bytes sent via SQL*Net to client
        360  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client

SQL> select x,x,x from xtest;

1000 rows selected.
Statistics

---

<skipped>

   12731016 bytes sent via SQL*Net to client

     427179  bytes received via SQL*Net from client
       3009  SQL*Net roundtrips to/from client

SQL> select c,c,c from xtest;

1000 rows selected.
Statistics

---

<skipped>

   24494417 bytes sent via SQL*Net to client

     500360  bytes received via SQL*Net from client
       4002  SQL*Net roundtrips to/from client

So we can see that test with xmltypes has a little less roundtrips than clob, but In fifteen hundred times more than with varchar2.

On Sat, Jul 19, 2014 at 1:45 AM, Jared Still <jkstill_at_gmail.com> wrote:

> Thanks for the info
>
> Until now I've not had to deal with XML or CLOB all that much, so much
> appreciated.
>
> The amount of coordination going on between client and server seems
> excessive.
>
> sent from my mobile, so expert typos
> On Jul 18, 2014 2:17 PM, "Sayan Malakshinov" <xt.and.r_at_gmail.com> wrote:
>
>> AFAIK, xmltype transports as clob, so I think, it's just a result of
>> getting xmltypes by locator through additional sql*net roundtrip per each
>> xmltype.
>>
>>
>> --
>> Best regards,
>> Sayan Malakshinov
>> Senior performance tuning engineer
>> PSBank
>> http://orasql.org
>>
>

-- 
Best regards,
Sayan Malakshinov
Senior performance tuning engineer
PSBank
http://orasql.org




--
http://www.freelists.org/webpage/oracle-l




text/plain attachment: output.txt



application/octet-stream attachment: xml2.sql

Received on Fri Jul 18 2014 - 23:56:35 CEST