Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: buffer busy waits PUZZLES - is there actually a problem?

Re: buffer busy waits PUZZLES - is there actually a problem?

From: Terry Sutton <>
Date: Wed, 18 May 2005 20:09:29 -0700
Message-ID: <00b701c55c20$4eff4bf0$6700a8c0@TerrySutton>

Some of the others have given you good advice about isolating your problem sessions to see if the buffer busy waits are related to the sessions timing out. I'd like to add a couple points to what they've said.

It has been mentioned that you are only listing number of buffer busy waits, not time waited. Whether you're looking at an individual session or systemwide stats, the number of waits is not very important (though, at a query level, it can sometimes help with diagnosis). What is important is the amount of time spent waiting. For example if the total amount of time your system has spent on buffer busy waits is 100 seconds, and you've had hundreds of transactions timing out after waiting 60 seconds each, then you can figure that the buffer busy waits aren't your big problem. Time waited is what you need to be measuring.

It has also been mentioned, but you don't seem to have addressed it- you need to determine the reason code of the buffer busy wait (the P3 value in v$session_wait). There are about 10 different reason codes (see Metalink doc #34405.1) and they point to different causes of the buffer busy wait. While sometimes a buffer busy wait can due to a freelist problem (though I've never come across that as the problem in a production system), there are several other causes which have nothing to do with freelists. If you find that buffer busy waits are a significant problem (in time waited), then find out the reason code and what it represents.


First of all thnks for your valuable time and suggestions.

However, there is no change in our transaction rate, they are almost the same value at any given time.
However, I am not doing it for any testing or learning purpose. We are facing some transactions time-out in our production database uding texudo middleware.
The tables which I increased the freelist are the one who are referring almost in every transaction.
Server configuration, 9gb of RAM with 5cpu, AIX 5m on D240 storage. I dont see 'buffer busy wait' as top 5 timed events in any statspack report but very seldomly I can see them in v$session_wait. My thinking was, all these tables have only 1 freelist (default value), when they are involving heavy concurent insertion, it would be a good idea to play with its freelist.

On 5/18/05, Mark W. Farnham <> wrote:
> Did your transactional throughput go up, down, or not change much?


> Asked another way, are these higher waits insignificant side effects of
> un-jambing some other problem?

> It is not possible to know without the times, and even with the times, we
> cannot know whether this is a problem or simply a report of a change in
> statistics unless you tells us whether throughput is now changed, and how=
> Asked yet another way, is some process materially slower than you need it=
> be due to the amount of TIME spent waiting for these waits.

> Abstract attempts to change the values of statistics reports from running
> systems without correlation to a process or task that is running slower t=
> required have little (little includes nothing) to do with making things
> actually process faster.

> Collection and observation of some statistics without correlation to prob=
> processes can be useful, but that is part of capacity planning and load
> management, not optimization. Even then (unless you're using systems
> statistics as a proxy for company business growth in an attempt to predic=
> stock outcomes or something like that) you need to correlate the statisti=
> you observe to known scaling limits of your existing environment.
> So, are you trying to fix some process that is going slower than you thin=
> it should (or more appropriately, slower than your service level
> requirement),
> or are you just trying to manipulate the statistics?

> Now I will not deprecate that activity if you're doing it as a learning
> exercise or even if you're just servicing your CTD. But I would like to k=
> if you have a throughput problem you're trying to solve (or not).

> To answer your question, if changes you made ungated pent up demand for
> these tables, sure, you could have driven the load on the tables higher. =
> your throughput went up, then the higher frequency of wait events is a ha=
> side effect. If your throughput went down, the times and a correlation wi=
> the now slower process(es) *might* show that the increase in waits
> represents a problem to solve.

> Regards,

> mwf

> -----Original Message-----
> From:
> []On Behalf Of The Human Fly
> Sent: Wednesday, May 18, 2005 2:11 AM
> To:
> Cc:; Oracle-L Freelists
> Subject: Re: buffer busy waits PUZZLES

> However, I haven't have buffer busy wait as my tops events nor I see
> any session execessively waiting for this event. I have already
> mentioned in my previous replies that I have grabbed these values from
> v$segment_statistics dynamic view.

> The following are today's result for the buffer busy waits.

> FM_OLTP_LOG 2727

> I have increased default freelist 1 to 5 to those tables and now I can
> clearly see that those tables buffer busy wait event has increased
> madly. This could be due to heavy access on those tables?

> However, I have been reading Cary's book past 1 year and still I
> enjoying reading it. Thank god, now, I started undertanding the
> concept behind method R and the true power of 10046 trace for
> resolving performance issues.

> On 5/18/05, Alex Gorbachev <> wrote:
> > My bad...
> >=3D20
> > 2005/5/17, Cary Millsap <>:
> > > Ah! Credit to Gaja and Kirti for that one...
> > >=3D3D20
> > > Cary Millsap
> > > Hotsos Enterprises, Ltd.
> > >
> > > * Nullius in verba *
> > >=3D3D20
> > > Visit for curriculum and schedule details...
> > >=3D3D20
> > >=3D3D20
> > > -----Original Message-----
> > > From: [mailto:oracle-l-bounce_at_freelists=
> rg=3D3D
> > ]
> > > On Behalf Of Alex Gorbachev
> > > Sent: Tuesday, May 17, 2005 2:42 PM
> > > To:
> > > Cc: K Gopalakrishnan;
> > > Subject: Re: buffer busy waits PUZZLES
> > >=3D3D20
> > > sessions/transactions than you you've got CTD ("Compulsive Tuning
> > > Disorder") speaking Carry's language.
> >=3D20
> > --=3D3D20
> > Best regards,
> > Alex Gorbachev
> > --
> >
> >=3D20

> --=3D20
> Best Regards,
> Jaffar, OCP DBA
> Banque Saudi Fransi
> Saudi Arabia
> -------------------------------------------------------------------------=
> -------------
> "It is your atittude, not your aptitude that determins your altitude."
> --

Best Regards,
Jaffar, OCP DBA
Banque Saudi Fransi
Saudi Arabia


"It is your atittude, not your aptitude that determins your altitude."

Received on Wed May 18 2005 - 23:15:07 CDT

Original text of this message