The Oracle (tm) Users' Co-Operative FAQ

I am getting several hundred "buffer busy waits" on data blocks per day, even though I have increased FREELISTS on all objects - what should I do ?


Author's name: Jonathan Lewis

Author's Email: Jonathan@jlcomp.demon.co.uk

Date written: 20th June 2002

Oracle version(s): 8.1.7.0

Buffer Busy Waits - found in v$waitstat - are often considered to be a problem. They arise when two processes want a buffered block "simultaneously", and their reasons for wanting the block are incompatible.

When the waits are for data blocks this is often due to inserting from multiple concurrent processes, and if you can identify the table (specifically) that is suffering, then increasing the number of freelists for that table will help. Sometimes, though, this is not the problem.

Back to index of questions


The questions you want to ask yourself are, presumably: which object has the problem, do I need to fix the problem, and how do I fix it. This note addresses the first two parts of that question.

Do I need to fix the problem

Whilst a few hundred waits per day does not look like a good number, you do need to compare it with the actual amount of work done, and check the time lost. In other words, is it likely to make any noticeable if you solve the problem ? There are two cross-checks to make. In v$system_event, is the total time waited on buffer busy waits significant compared to waits like file reads, enqueues, latches ? Then in v$sysstat - is the number of waits you have recorded tiny compared to the total number of consistent gets (say one in one million), is the time lost significant compared to the CPU used when call started.

Of course, it is always a little misleading just to look at system-level figures. This can easily give you a false sense of security by hiding crticial peaks in a swamp of average behaviour. It is just possible that 99% of all your buffer busy waits take place in a critical 30 minutes of the day on one very important process; so you really need to look at regular snapshots (statspack can help) and probe down to the session level (v$sesstat and v$session_event)

Once you have decided that you do need to investigate further, the next step is to identify the problem object. Note - when one session suffers buffer busy waits, it isn't necessarily a problem caused by that session; it may be another session that is doing something completely different; however it is often the case the these waits show up because multiple sessions are doing the same sort of thing at the same time, so you will often find that you can identify the cause by researching the effect.

In 8.1 there is generally no better way than watching a session that you think will suffer a number of these waits. If you check v$session_event joined to v$session, you may notice that a particular program suffers most of the waits. If this is the case, then pick one of those sessions and fire event 10046 at level 8 at it, for example using:

sqlplus "/ as sysdba"
select spid, pid
from v$process
where addr = (
	select paddr 
	from v$session
	where sid = 99
);
oradebug setorapid {the pid above}
or
oradebug setospid {the spid above}
then
oradebug event 10046 trace name context forever, level 8

Wait a while, then look at the trace file. You may find some buffer busy waits recorded in the trace file, and be able to use the p1 and p2 values (file number and block number) in a query against dba_extents to identify the object, and the p3 value to get the reason for the wait.

If you do not want to have sql_trace running, then you could simply query v$session_wait repeatedly,

select sid, p1,p2,p3
from v$session_wait
where event = 'buffer busy waits';

Steve Adams (http://www.ixora.com.au) has a more subtle approach to using v$session_wait that increases your chances of spotting a wait when using this view.

 

Note - In Oracle 9.2, there is a view called v$segstat that can be cued to record all sorts of activity per segment - so a quick report on this dynamic view would tell you which segments were suffering most buffer busy wait.


Further reading: N/A


Back to top

Back to index of questions