Performance problems for Oracle on Sun

From: Scott Holt <scott_at_prism.gatech.EDU>
Date: 1 Jul 92 21:07:00 GMT
Message-ID: <62427_at_hydra.gatech.EDU>

We have been having some serious performance problems on one of our database servers: a Sun 490 running SunOS 4.1.1 and Oracle 6.0.33.1.1. The symptoms are one or more Oracle shadow processes hogging all CPU resources and a very high rate of system calls overall.

We performed a trace on one of the processes and found something shocking which is shown below:

read (5, "".., 4096) = 49
write (5, "".., 5) = 5
read (5, "".., 4096) = 49
write (5, "".., 5) = 5
read (5, "".., 4096) = 49
write (5, "".., 5) = 5
read (5, "".., 4096) = 19
write (5, "".., 49) = 49
read (5, "".., 4096) = 15
select (32, 0xf7ffe748, 0, 0, 0x212aa8) = 0


                .

                .

                .

     812 identical lines deleted.....


                .

                .

                .

select (32, 0xf7ffe748, 0, 0, 0x212aa8) = 0 write (5, "".., 53) = 53
read (5, "".., 4096) = 53

What this excerpt doesn't show is the fact that the > 800 selects were generated in the space of a few seconds. There were also no reads or writes between the selects. No other system calls at all.

If I am reading this correctly, it seems that the shadow process invokes select which quickly returns because of either a timeout or becuase the timer value is 0 (indicating a poll). My question is then, why are so many select's being issued?

I can think of a few things. First, it could be polling - why!?!?! Second, it could be waiting a very short period of time (apparently too short). Third, it could be waiting a short period of time after which it does some real work (work that involves no system calls) which the designers thought would take much more time than it apparently does. In any case, its killing our system. System call overhead is far in excess of 40%. Often its as high as 70%.

There are a couple of other specifics which might have some significance - we are using SQL*Net and the client of this shadow process was SQL*Plus.

• Scott

-- 
This is my signature. There are many like it, but this one is mine.
Scott Holt                 		Internet: scott_at_prism.gatech.edu
Georgia Tech 				UUCP: ..!gatech!prism!scott
Office of Information Technology, Technical Services

Received on Wed Jul 01 1992 - 23:07:00 CEST