Re: Dark ages performance problem (UNIX SVR3 running ORACLE 6.0.30)

Barry,

It's always interesting to find people who cares for *real detective work* From your article you have come to the comclution that it's a un*x problem, not an oracle. And i think you are right here. It might be as simple as "syncer(1)" flushing out buffer cache.

One way of bypassing could be to run your tables on a raw device instead of a file-system. That would gain 2 things: 1- less buffercache activity and thus less impact from syncer 2- independence of filesystem i/o and better performance

On the dark side you would have to backup the tables with another method then your regular backup.

Peter h (address mungled to save me some spam)

Barry Ostroff <kidnme_at_dr.lucent.com> wrote:
: 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
 

-- 
Unsolicited commercial/propaganda email subject to legal action.  Under US
Code Title 47, Sec.227(a)(2)(B), Sec.227(b)(1)(C), and Sec.227(b)(3)(C), a
State may impose a fine of not less than $500 per message.  Read the full
text of Title 47 Sec 227 at http://www.law.cornell.edu/uscode/47/227.html

<peter.devnull_at_vtd.volvo.se> (remove ".devnull" before use!)
Peter Håkanson,Volvo Technological Development. Dep 6970,Gothenburg,Sweden