XML queries and Excessive Network Traffic

From: Jared Still <jkstill_at_gmail.com>
Date: Fri, 18 Jul 2014 14:03:39 -0700
Message-ID: <CAORjz=NL=-gDS-hOp_-uDp_LjJ71DpJfA0UtMoKuqV=93F878w_at_mail.gmail.com>

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

Received on Fri Jul 18 2014 - 23:03:39 CEST