Re: XML queries and Excessive Network Traffic

getclobval() was deprecated in 11.2.0.1
<http://docs.oracle.com/cd/E11882_01/appdev.112/e10492/whatsnew.htm#ADXDB5978>. Try using xmlserialize instead. I have seen major performance differences between these two functions.

Seth Miller

On Fri, Jul 18, 2014 at 4:03 PM, Jared Still <jkstill_at_gmail.com> wrote:

> Fellow oracle-aliens (say it out loud, it makes more sense :)
>
> Version info:
> Server: Linux 6.x 64 bit
> Database: Oracle 11.2.0.4 64 bit
>
> This week I have run into an interesting issue that causes some pretty
> slow queries on XML data.
>
> The problem is not the speed at which oracle returns the data - the
> problem is the amount of sqlnet traffic being generated.
>
> A client had been working on the same network the database was on, and
> then changed to a remote location. Suddenly the XML data queries he was
> running via SQL Developer on his PC became quite slow.
>
> Before contacting me he did some good troubleshooting.
> He found that when running the queries in a VM local to the data center
> they ran quite fast.
>
> Those same queries at his remote site were quite slow, painfully slow.
>
> As part of one test he converted the XMLType data to VARCHAR2, and the
> query ran quite quickly.
> Using Wireshark he could see that there was quite a few more network
> packets being sent for the XMLType vs the VARCHAR2 data.
>
> The slow results are in part due to the Firewall and QOS in the data
> center.
> (working on that with the net admin)
> The effect is magnified by the large number of packets being returned for
> the XML data.
> Many of these packets appear to be coordination of some type, but I cannot
> yet find any info on this. I will get to those packets in a bit.
>
> And before you ask, setting SDU(client and server), SND/RCV buffer (client
> and server) sizes and the arraysize had zero effect on this.
>
> I have been able to duplicate this issue on an 11.2.0.4 database
> (connecting from another server via sqlnet)
>
> The sqlnet traffic can be dumped to a trace file with event 10079 level 2.
> (must be sysdba and use oradebug for this to work)
>
> Combined with event 10046 the difference in network traffic for queries is
> quite apparent.
>
> The queries have all been designed to return at most 4000 bytes across the
> network for equivalent comparison with VARCHAR2.
>
> Here's an example (IFC is the prefix for network interface send/receive
> events)
>
> Get count of IFC (sqlnet packets)
>
> grep -c ^IFC *10046.trc
> js02_ora_13729_VARCHAR-10046.trc:42
> js02_ora_13749_CLOB-10046.trc:5152
> js02_ora_13769_XMLDATA-10046.trc:5152
>
>
> Get count of sqlnet waits:
>
> grep -c 'SQL\*Net message' *10046.trc
> js02_ora_13729_VARCHAR-10046.trc:19
> js02_ora_13749_CLOB-10046.trc:5149
> js02_ora_13769_XMLDATA-10046.trc:5149
>
>
> The next step is to create an SR, but I nearly always consider that a last
> resort.
>
> If anyone has seen this issue, and/or has some insight into why so much
> network traffic is being generated for XML. please reply to this thread.
>
> A complete reproducible test case is available if you want to play with it.
>
> oracle-xml-excessive-network-traffic.zip
> <https://drive.google.com/file/d/0B7WR5mMZCg3jQ0ZRUWhwX1R0UU0/edit?usp=sharing>
>
> See the README file in the zip file.
>
> Jared Still
> Certifiable Oracle DBA and Part Time Perl Evangelist
> Sr Oracle DBA at Pythian
> Pythian Blog http://www.pythian.com/blog/author/still/
> Oracle Blog: http://jkstill.blogspot.com
> Home Page: http://jaredstill.com
>

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