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

On Mon, 9 Feb 2004 15:36:25 +0700, <harronc_at_attglobal.net> wrote:

>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
>

Most likely your analysis is completely inadequate, and you are just barking at the wrong tree. The influence of sqlnet on total performance is completely negliceable. Also your assumption sqlnet always should transmit 2048 bytes demonstrates you don't know anything about the interaction between client and server. A sqlnet packet will never ever cross statement boundaries. If you see 810 byte packets all the time, the database only has 810 bytes to send for that statement. For your sqlnet analysis you should also rather use the trcasst tool than pstack, and if you insist on using pstack you should trace the dedicated server process server your client instead of the client. You would also check out to see what is going on in terms of sqlstatements, and you should check disk I/O. My crystall ball tells me, if you perform an adequate analysis, you will find you are dealing with an unscalable application.

--
Sybrand Bakker, Senior Oracle DBA