"Network Tuning" your Application

This article first appeared in the November/December 1995 issue of _The Oracle Integrator_.

"Network Tuning" your Application

     by Thomas B. Cox

With all our brave talk of 'client server' development, how many of us really develop applications with any awareness of network issues? The whole point of client-server, after all, is to allow a network to be used to link the client and the server -- thus giving us our choice of client operating systems, server operating systems, geographic locations, etc.

In fact, we should probably be calling it "Client-Network-Server".

Every client-server application needs to be written and tuned in three ways:  efficient client execution, efficient server execution, and efficient use of the network. Unfortunately, most of us tune the server a lot, and the application a little. Network tuning is done (in most cases) not at all.

One reason we don't do a better job of tuning our client-server applications' use of the network is that we haven't had very good tools for knowing what was going on across the network.

For example, when your application logs on to Oracle, how many packets get exchanged with the server? How many SQL statements? How long did they take?  Can any of them be eliminated?

When you have your application validate an entered value against a list of valid values, how many SQL statements are used? How many packets are exchanged?

If you're like most, you have no idea. I didn't, until I started using a brand-new product from Network General (makers of the Sniffer[TM] line of products). This is an add-on to your existing Sniffer[TM] that shows you which packets on the network are SQL*Net v2.x packets (using TNS, the Transparent Network Substrate), and which are not.

The first use I had for this product, the Sniffer[TM] "Database Adapter" module, was to see if the network people were right in blaming my application for some network slowdowns we were experiencing. As luck would have it, we ran SQL*Net over TCP/IP while the rest of the network services (file sharing, print serving, name lookups, etc.) were on Banyan VINES. The Database Adapter showed me which TCP packets were being used for SQL*Net (all of them), and the Sniffer[TM] showed me that less than 24% of all traffic was from TCP/IP. During the heaviest loads, it was less than 10% TCP/IP. With this report in hand, I was able to get the network people off the backs of the development team, and more importantly they were able to focus on the real problems -- using 386es for file servers. I kid you not.

The second thing that occurred to me to use this for was to see if I could blame some performance problems we were having on our network. (The politically correct way of putting that is to say I was "trying to pinpoint the problem" but in reality I was hoping to point a finger.) This arose with a particular query-only screen that could pull up thousands of rows -- the first screenful was reasonably fast, but jumping to the last row took minutes.

I tried to see if the query itself was slow, but the 5,000-row query ran in under five seconds in a SQL*Plus session on the server box. EXPLAIN PLAN showed we were using good indexes. So we couldn't blame the server.

Next I tried setting the Buffer Size (the number of rows to fetch using the OCI Array Fetch feature) to different amounts, and watching the network with the Sniffer[TM] and the Database Adapter.

With a Buffer Size of 1, the network simply thrashed. We exchanged four packets per row, and of course it took a long time. No surprise there.

But as I increased the Buffer Size to 50, 100, 200, and 500, I began to see a pattern emerge. The client program would ask for a set of, say, 200 rows; then the server would begin to send a veritable stream of packets (about 60 packets for 200 rows), followed by a long pause. Then, after several seconds, the client would emerge from its stupor to ask for the next set of rows. The server almost instantly replied, the rows flew across the net, and again the client sat there pondering.

The bigger the Buffer Size, the longer the client would pause before asking for the next set of rows. We had our smoking gun all right, but it was in my hand -- I wasn't handling the rows efficiently on my end. No finger got pointed at the network on this one.

The moral of the story is this: if you want to focus your people on solving the real problems and not on blaming each other, get a product like the Sniffer[TM] with the Database Adapter. Without it, there's often no way of knowing where client-server performance problems lie. (If anything else does the job, please let me know -- I'm not aware of anything else that does this.)

Thomas B. Cox <tcox_at_netcom.com> is Director of Technology at TrueNorth Consulting, Inc., Portland, Oregon.

-- 
Thomas B. Cox   tcox_at_netcom.com
Author of _Oracle Workgroup Server Handbook_, ISBN 0-07-881186-4