Resend: performance problem with fetch using 8.1.7 32-bit client to access a 64-bit server

Please ignore previous message.

We are running 8.1.7.4 64-bit Oracle on Solaris 8/sparc3. We have a client which was compiled on a 32-bit sparcII platform. We find that when we run multiple instances of the client we find that it doesn't scale very well, even though the product vendor believes that it should and we have evidence to prove this in different sites. We have already spent a lot of effort tuning the server and we now believe that it is well tuned. The server spends 90% of it's time idle. We have found that the client also spends about 90% of its time sleeping even though it has work to do, and there are plenty of free resources (only 25% of 24 CPUs are utilised, 40% of free memory, and IO is healthy).

When we dig deeper into what the client is doing using pstack, we find that that it spend most of its time fetching from various cursor and specifically in the read function. An example stack trace is below:

fea9e570 read (a, 8077ee, 810)

fefc1a88 sntpread (810, 80560c, 804aec, 8077ee, 804a9c, 7f2544) + 10

fefbf114 ntprd (804aa8, 805108, 7fb3d8, ff257314, 0, 7f9088) + 134

fee1f490 nsprecv (8048c8, ffbe875f, ff285ce8, 0, 7f9088, 7fb3d8) + 58c

fee24184 nsrdr (8048c8, 8080f0, ff285ce0, 0, 27, a) + 1dc

fee09bc8 nsdo (7fcd08, 1, 0, 0, 7f9088, 8048c8) + f14

fee91044 nioqrc (7f250c, ff257314, c27, 7fcd08, 0, 7f9088) + 6c0

fefca86c ttcdrv (7f5c78, 7f5fbc, ff257314, 7f5fac, 7f5fae, 7f5fac) + b7c

fee996ec nioqwa (7f250c, 0, fefc9cf0, 7f5c78, 7f2490, 0) + 48

fed19330 upirtrc (7f2468, 7f250c, 0, 0, 7f1df4, 7f2544) + 7f8

fed6ab60 kpurcsc (7f25e8, ffbe9cae, 7f242c, 7f5c78, 7f6804, 0) + 70

fedccd7c kpufch0 (1f5b2c0, 2, 7f2468, 7f5b74, 1f5b308, 6340) + a68

fedcdc28 kpufch (1f5b2c0, 7f1f0c, 0, ff257314, 7f242c, 2) + 92c

fecebec4 sqlcucFetch (1f5a5d0, ff2848b4, 1, 1f5a5d0, 0, 7f1f0c) + 20

fecda5b4 sqlall (ff2848b4, 1, 22a0c28, 16, 58, 16) + 1bf4

fecd17a8 sqlnst (382a62, ffbedb50, ffbedb50, 1, ff2848b4, a7f0f0) + 7b44

fecb3d3c sqlcmex (0, a7f0f0, ffbedb50, 37d918, ff22d13c, ff257314) + 488

fecb4180 sqlcxt (0, 7615ec, ffbedb50, 37d918, 37d800, d0) + 58

We run pstack repeatedly and the client is invariably in the above function.

The client uses a local bequeath connection so I assume that the read() is reading from a pipe. I've noticed that the third parameter of read(), the number of bytes, is always 810. It is possible that this is the bottleneck. It is the sdu that controls the number of bytes read. We have not set the sdu and oracle documentation says that the default value is 2048. But then when in only 810 bytes read? (We have run pstack many time and it is always 810 bytes).

Even if the read size needs tuning this does not explain why the client processes do not scale. The performance increases reasonably linearly up to eight parallel processes but beyond this the increase in throughput slopes off, and beyond 14 processes there is no increase in throughput at all, even though the oracle server is idle and there are plenty of free resources.

We were told that there was a bit when using a 32-bit client to access a 64-bit server, but we have installed the relevant patch 2298968 and this did not improve the situation.

We do not have source code for the client application but as it calls sqlcxt I assume it uses Pro*C. Any similar experience or any idea of what can be causing the problem?

Ciaran Received on Mon Feb 09 2004 - 02:36:25 CST