Thanks.
--
Niall Litchfield
Oracle DBA
Audit Commission UK
"Ricky Sanchez" <rsanchez_at_more.net> wrote in message
news:3AF99F9D.103DD35F_at_more.net...
> 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 Thu May 10 2001 - 07:24:43 CDT
