Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: SQL*Net message from client waits
On 27 Jun., 22:13, sybra..._at_hccnet.nl wrote:
> On Wed, 27 Jun 2007 12:46:00 -0700, baba_j..._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.
Thanks a lot, Sybrand. As I mentioned it is a Powercenter (repository), 3rd party, we have no influence on this code. Inbetween I looked through my tkprof output, the number of parses is quite surprising, in a lot of them I can see bind variables. In fact it is not the first time I hear about very poor transfer rates of Powercenter, especially on the conns with other countries, I suppose last year somebody was playing with different sdu values at both peers as well.
I attach one typical example below, actuallly a pattern, for each execution 3 fetches.
SELECT DBTYPE_ID, DATATYPE_NAME, NATIVE_DATATYPE, PM_DATATYPE,
DEFAULT_PRECISION, DEFAULT_SCALE, PROP_FLAGS, PRINT_FLAGS,
ORDER_NUM,
OPB_OBJECT_ID
FROM
OPB_MMD_DATATYPE WHERE DBTYPE_ID > :1 ORDER BY DBTYPE_ID
call count cpu elapsed disk query current rows
Parse 398 0.03 0.04 0 0 0 0 Execute 398 0.03 0.08 0 0 0 0 Fetch 1194 0.43 0.43 0 3980 0 55322
total 1990 0.49 0.55 0 3980 0 55322
Ciao
Ch.
Received on Wed Jun 27 2007 - 16:15:31 CDT