Re: What's wrong with these two ways of looking at wait events?

You can answer this question in a variety of ways.

First - it is __unusual__ for buffer busy waits to be responsible for such a high percentage of the wait time. But that doesn't make them a problem, only an oddity worthy of further investigation.

Second - as you have done - get some perspective. It is 1.5 seconds per user per hour, so it isn't necessarily significant. But averaging things out can make you overconfident:   it could have been two colliding processes which each lost 730 seconds (12 minutes) in that hour. It could have been 10 sessions losing 14.6 seconds each on 10 separate executions of a particular screen refresh.

My reaction to seeing an unusual figure like that would be to start running snapshots against v$session_event for event = 'buffer busy waits' to see how the waits were distributed across sessions (at different times of day, possibly).

-- 
Regards

Jonathan Lewis

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

http://www.jlcomp.demon.co.uk/seminar.html
Optimising Oracle Seminar - schedule updated Sept 2nd





"thu do" <secemad_at_hotmail.com> wrote in message
news:a777bc73.0409092240.1cc02bb4_at_posting.google.com...


> Hi,

>

> I am trying to figure out how much of a problem buffer busy waits is

> in our system.  If I have the following information in the statspack,

> how do I know if the 19.64% number is bad or not? (The statspack is

> made up since I don't have a real one, the number of waits is much

> much higher in case that number makes a difference).

>

> The statspack is for one hour period.  Our database is running with 80

> CPUs on Fujitsu, at 60% utilization with I/O wait or 35% utilization

> without I/O wait during peak hour.  This is an OLTP environment.

>

> The 19.64% looks high however if I take the Total Wait time of 1460

> and divide by number of users on the system, I'd get 1.5 second wait

> per user for the whole hour, that's nothing.

>

> How do I know when I'd have a problem or close to a huge problem?  I

> prefer not to wait until there is a huge problem before doing

> something about it.

>

> Top 5 Timed Events

> ~~~~~~~~~~~~~~~~~~

> % Total

> Event                                               Waits    Time (s)

> Ela Time

> -------------------------------------------- ------------ -----------

> --------

> buffer busy waits                                  10,669       1,460

>   19.64

>

> Thank you!

> Thu