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: <baba_jaga_at_tahiti.net>
Date: Wed, 27 Jun 2007 14:15:31 -0700
Message-ID: <1182978931.457409.183910@o61g2000hsh.googlegroups.com>


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

Original text of this message

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