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@xxxxxxxxx> 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@xxxxxxxxx> 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
SQL> create table xtest as
2 select a as v
3 ,xmltype(a) as x
4 ,to_clob(a) as c
5 from (select '<a>'||lpad(1,3900,1)||'</a>' a from dual connect by
level<=1000);
Table created.
SQL> set arrays 1000;
SQL> set autot trace stat;
SQL> select v,v,v from xtest;
1000 rows selected.
Statistics
----------------------------------------------------------
7 recursive calls
0 db block gets
1068 consistent gets
1000 physical reads
0 redo size
29001 bytes sent via SQL*Net to client
360 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1000 rows processed
SQL> select x,x,x from xtest;
1000 rows selected.
Statistics
----------------------------------------------------------
377 recursive calls
0 db block gets
5083 consistent gets
31 physical reads
0 redo size
12731016 bytes sent via SQL*Net to client
427179 bytes received via SQL*Net from client
3009 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
1000 rows processed
SQL> select c,c,c from xtest;
1000 rows selected.
Statistics
----------------------------------------------------------
5 recursive calls
0 db block gets
4066 consistent gets
3000 physical reads
0 redo size
24494417 bytes sent via SQL*Net to client
500360 bytes received via SQL*Net from client
4002 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1000 rows processed
SQL> set autot off;Attachment:
xml2.sql
Description: Binary data
