Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Performance problem with Oracle server

Re: Performance problem with Oracle server

From: <xhoster_at_gmail.com>
Date: 28 Feb 2006 16:07:39 GMT
Message-ID: <20060228111115.782$LH@newsreader.com>


bubblecat_at_gmail.com wrote:
>
> Frank - Re your point that 'SQL*Net message from client' event should
> be included, I'm not sure I follow this. Indeed this event comes out on
> top, but isn't that normal? You would get this event every time the
> dbase waits for the front-end, right? So assuming users don't respond
> at lightning speed and even go for coffee with the form still open,
> isn't it to be expected that this event accounts for most of the
> waiting time? Please explain if I misunderstand this event.

At this level of granularity, the message from client event is pretty useless, as there is no way to distinguish harmless ones (end user is getting some coffee, whatever) from harmful ones (the end-user's client is constantly programmatically yacking back and forth with the server, suffering the death of a thousand small "idle" waits). But if you are tracing individual sessions, then it should not be pre-emptively dismissed.

If you can get the actual histogram of "message from client" wait times, then with that and a little intuition it is usually obvious where to draw the line between "Waiting for the end-user personally" and "Waiting for the end-user's client program". For example, ignore all waits more than 1 second, and see what the total is of the ones under 1 second each.

>
> Xho - I know read time exceeds enqueue/latch time, but I mean, wouldn't
> the enqueue and latch times normally be lower on a properly tuned
> system, given the scattered/sequential read times?

I'll go out on a limb and say the proportion of enqueue waits have little to do with tuning. If the task your database is trying to accomplish is inherently contentious, or is contentious through bad design, then you will high enqueue waits; but this is at the level of design and/or reality, not the tuning level. If you have badly tuned DML, they can hold locks longer than they otherwise would, increasing enqueue waits. But that would also increase the other waits more or less proportionally. (However, it could be out of proportion if there are multiple thing queued up on the same resource. If one session holds a lock while it does 5 seconds of IO, and 4 sessions are blocked waiting for that row, this results in 5 seconds of IO wait and 20 seconds of enqueue wait.) So, assuming you can't redesign your applications at this point, focus on the sequential read time (which most likely means focus on the logical IO behind the reads) and enqueue will likely come down as well. For that matter, it is likely the latch waits and perhaps even the buffer busy waits will as well.

BTW, do you know if the buffer busy waits are mostly for data blocks, or header blocks, or what?

> What is typical?

There is no typical. Your database needs to do what you need it do do for you, my database needs to do what I need it to do for me. That said, unless your task at hand is inherently highly contentious, the enqueue waits to do seem out of line to me.

Xho

-- 
-------------------- http://NewsReader.Com/ --------------------
Usenet Newsgroup Service                        $9.95/Month 30GB
Received on Tue Feb 28 2006 - 10:07:39 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US