Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: SQL*Net message from client waits
On Wed, 27 Jun 2007 12:46:00 -0700, baba_jaga_at_tahiti.net wrote:
>
>some users complain about a Powercenter performance, I traced the
>sessions for an hour, please have a look at last section of the the
>output of tkprof below. It is a 32 processors Sun running 9.2.0.6, not
>dedicated to this application. Running the trace the box was in avg.
>30% idle. I checked the plans and they seem to be OK, I know that
>these wait events *may* be idle. Do I have problems with the network
>in your opinion? Has anybody dealt with comparable problems?
>
>Best regards and thanks in advance for your time
>Christoph
Your problems are
- you parse every statement
- you don't use the array interface, ie: you fetch every individual
record, instead of fetching n records at a time.
Each fetch results in a sqlnet message to client and a sqlnet message
from client. The number of sqlnet messages about matches the number of
fetches. Note: not using the array interface will also increase the
number of consistent gets
- What I can't see from here, but what is likely: your Session Data
Unit for sqlnet (SDU, default 2k) doesn't match the MTU of your
network usually 1500. Consequently your network card will fragment the
2k packets. Oracle won't see this.
You need to set sqlbet.DEFAULT_SDU_SIZE in sqlnet.ora on the server to
a multiple of 1500 and smaller than 32767.
You need to add a line SDU= in tnsnames.ora. The value needs to be
identical with the server value.
-- Sybrand Bakker Senior Oracle DBAReceived on Wed Jun 27 2007 - 15:13:22 CDT