Dark ages performance problem (UNIX SVR3 running ORACLE 6.0.30)

I'm investigating a performance problem on a UNIX System V Release 3.2.2 machine running ORACLE 6.0.30. We have a relatively small database
(about 5000 records in a single table). Our transaction consists
primarily of querying for a record based on the only defined index and updating that record some time afterwords. The transaction rate is not particularly large (say 1 transaction every 10 to 20 seconds but bursts can create traffic at about 1 every 2 seconds). The query response time
(measured internally in a Pro*C compiled program) is usually on the
order of .01 to .09 seconds. On rare occasions, this time can balloon to 3 or 4 seconds!!! The ORACLE tools (monitor bstat/estat, etc) don't suggest any strangeness. With additional software instrumentation, I can see that the database writer is always trying to write something during these poor response times. Many times, the ORACLE logger is too. I can also see other disk activity at the same time. The cpu occupancy isn't too terribly large (say 10-20% during the intervals in question), but it is larger than at other times. It is also the case the wait IO percentage is fairly large (70-90%). I have noticed one other phenomenon that leads me to believe that this is not an ORACLE issue at all (rather a UNIX one). I have some data to suggest that our C application performing the query actually loses the processor for 2 to 3 seconds just before it makes the query (The process logs to a file when it receives the query request and just before it performs the query in ORACLE. Each of these statements is time stamped and the intervening code is in memory stuff like strcpy(). Usually, these log statements appear to have the same time stamps, but during the slow response times, the time stamps are separated by 2 to 3 seconds. Also, this condition can sometimes appear to persist for a period of 30 seconds. In other words, later transactions also show this strange logging behavior, but the difference in time stamps can vary greatly - some transactions should little difference while others still show 1 to 2 second differences). Is there some buffer contention in UNIX that I am running up against ? Is it too busy flushing buffers ? Is ORACLE exacerbating this problem ? Can I tune away these problems in UNIX or ORACLE or both ? Got any ideas or suggestions ?

Barry Ostroff
Lucent Technologies
kidnme_at_dr.lucent.com Received on Fri Sep 26 1997 - 00:00:00 CDT