Re: Brain Death

To add to Jonathan's suggestion, another convenient method for discovering network problems is sql*plus with autotrace. Set arraysize = 1 in sqlplus, then run the test query and observe sqlnet bytes and packets. Then set arraysize to a higher value, say 10, and run it again.

With larger array sizes you can observe fewer packets transmitted for approximately the same total number of bytes. Turn on local timing to get end-to-end data and you might see an order of magnitude gains by using array fetches. It would be most helpful to have the sqlplus client on the same relative network segment as the production application client if you want to closey mimic the actual application.

What is happening is that the third party application is simply not coded to perform array fetches when result sets are greater than a single row. Applications often grab large result sets to prepopulate drop-down lists or process reports and so forth. Array fetching is trivial to do, but most third party developers just aren't that sharp.

Local operations run fast because they are on LAN segments. Once you start hopping across routers, floods of tiny packets bog down the network. As far as server waits are concerned, the most likely wait you will see under these circumstances will be 'sqlnet more data to client', which means we are trying to push data to the client but have to wait for the network or a client fetch command.

You can also either use sql tracing at level 16 to see actual packet contents or get the assistance of a network administrator with a sniffer.

• ricky

Jonathan Lewis wrote:
>
> A file copy is probably not a reasonable test.
> 3rd party apps are notorious for indulging in
> lengthy row-at-a-time dialogues with Oracle,
> and such dialogues are synchronous.
>
> File transfer protocols generally work on
> the basis -
> Here's 8K
> Here's another 8K
> Here's another 8K
> Here's another 8K
> Here's another 8K
> Here's another 8K
> Oh, you missed the 3rd bit did you
> here it is again.
>
> One indicator is to hit a sample session
> with 10046 at level 8, with timed_statistics
> turned on, and look at:
> a) the number of selects/fetches
> b) the tim = figure on each line
>
> If there are lots of calls to the database in
> a short period of time, the app is at fault.
> If the time lag between calls is anomalous
> (e.g. 5/100 second between FETCH calls)
> then the network, or other infrastructure
> item (such as Windows DLLs) could be
> the problem.
>
> --
> Jonathan Lewis
> Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk
>
> Practical Oracle 8i: Building Efficient Databases
> Publishers: Addison-Wesley
>
> Reviews at: http://www.jlcomp.demon.co.uk/book_rev.html
>
> Niall Litchfield wrote in message
> <3af94bf7$0$12245$ed9e5944_at_reading.news.pipex.net>...
> >we run a 3rd party financials solution in a WAN environment.
> >
> >Problem is that remote sites report a (very real) loss of performance in
> >OLTP work. However
> >
> >a) local sites do not report this problem, suggesting database tuning is
> >adequate and
> >b) also report.txt gives no undue waits,locks or bad hit ratios etc.
> >
> >So I turn to the network. This is my weak point.
> >
> >File copies run at acceptable speed utilising the available bandwidth
> >appropriately. Routers etc report no problems (I'm relying on my CISCO guy
> >here).
> >
> >Network is IP only so all traffic uses same protocol. Only affected
> >application appears to be this one. other oracle based solutions are
> >performing normally somwehat suggesting database.
> >
> >Any suggestions as to where to look. especially is a file copy an adequate
> >test of network performance or is there more I could do? Ping and tracert
> >are OK.
> >
> >any suggestions that this problem has chosen to rear its ugly head after I
> >went down the pub are entirely justified.
> >
> >
> >
> >--
> >Niall Litchfield
> >Oracle DBA
> >Audit Commission UK
> >
> >
Received on Wed May 09 2001 - 14:50:35 CDT