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: Wait analysis

Re: Wait analysis

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Tue, 23 Apr 2002 17:44:38 +0100
Message-ID: <1019580201.15860.0.nnrp-12.9e984b29@news.demon.co.uk>

In some vague way it is an average that
has some slight meaning (except for the
log file parallel write which couldn't have come from the batch jobs - but it's a very small error anyway).

But I don't think it adds much value other than as a rough guideline to indicate that there is a significant contention problem and some scope for improvement. In fact, unless the batch jobs are identical, on
virtually identical data sets, it might be very misleading to average things out
like this instead of drilling into a
v$session_event snapshot to see if
that categorises waits by batch task.

Also, with 96 active processes, the
number of CPUs, hence length of the
run queues, are also important factors
to consider - some, if not quite a lot,
of that wait time could be time spent
runnable but not running.

--
Jonathan Lewis
http://www.jlcomp.demon.co.uk

Author of:
Practical Oracle 8i: Building Efficient Databases

Next Seminar - Australia - July/August
http://www.jlcomp.demon.co.uk/seminar.html

Host to The Co-Operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html



Andrew Mobbs wrote in message <+bC*+bwmp_at_news.chiark.greenend.org.uk>...

>(Oracle 9.0.1.3 on HP-UX 11i)
>
>If I have an instance that's under steady load from batch processes,
>is it valid to simply add up the wait times and divide by the number of
>processes to get average wait time?
>
>For example, I have a statspack with the following:
>
> Snap Id Snap Time Sessions Curs/Sess Comment
> ------- ------------------ -------- --------- -----------------
--
>Begin Snap: 244 17-Apr-02 15:41:43 144 11.5
> End Snap: 245 17-Apr-02 15:46:55 144 11.5
> Elapsed: 5.20 (mins)
>
>[...snip...]
>
>Top 5 Wait Events
>~~~~~~~~~~~~~~~~~ Wait %
Total
>Event Waits Time (s) Wt
Time
>-------------------------------------------- ------------ ----------- -----
--
>latch free 610,186 13,688
70.61
>buffer busy waits 27,625 4,504
23.23
>enqueue 1,084 776
4.00
>db file sequential read 156,662 356
1.84
>log file parallel write 6,564 42
.22
> -------------------------------------------------------------
>
>I know that there were 96 concurrent connections from batch processes,
>and the other sessions were largely idle. Can I say that each one spent
>on average =>
>
>(13688+4504+776+356+42)/(96*5.2*60)
>= 64.7%
>
>of wall time blocking on Oracle wait events?
>
>--
>Andrew Mobbs - http://www.chiark.greenend.org.uk/~andrewm/
Received on Tue Apr 23 2002 - 11:44:38 CDT

Original text of this message

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