Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: SQL*Net message from client waits

Re: SQL*Net message from client waits

From: <sybrandb_at_hccnet.nl>
Date: Wed, 27 Jun 2007 22:13:22 +0200
Message-ID: <7sg5835r6tledbk7h8uojln9hd3pv64des@4ax.com>


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 DBA
Received on Wed Jun 27 2007 - 15:13:22 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US