Re: Buffer busy waits with P3 value 130 in v$session_wait

P3=130 means that a session is trying to do a CR get on a buffer and the buffer is busy due to another process reading data from disk and loading into the buffer.

Given that:
1.) Increase number of db_writers will not help. This is a read problem, not a write problem.
2.) Increase the buffer cache. This *might* help, we need to investigate further.
3.) Increase initrans. This will do no good, as it has no impact on select statements.
4.) Increase freelists. Again, no impact, completely different mechanism.

Note that 1,2, and 4 could potnetially be solutions for other category of buffer busy wait, but not P3=130.

So, what is the problem? Well, Are there other processes that are waiting a lot on sequential read? Is it always the same SQL statement from multiple concurrent sessions? What does the execution plan for that statement look like? Does the statement(s) in question have nested loop joins and index driven indexes? Are the indexes effective? (Are they selective enough to be useful?)

My thoughts as to most likely causes:
1.) Inefficient SQL. Tune it.
2.) Unselective indexes. Related to above. Find a better access path.
3.) Undersized buffer cache. If you have n buffers in the cache, and you have SQL that needs to visit n+1 buffers, then when you hit the (n+1)th buffer, you bump buffer 1 out of the cache. Now if you loop around to buffer 1, you bump buffer 2 out of the cache. 2 will bump 3, etc. You end up doing lots of I/O and will probably see significant 'db file sequential read' waits. If you add multiple concurrent processes into the mix in this situation, you'll begin seeing 'buffer busy wait' events as well. Obvious solution here is increase the buffer cache size.

Next steps, try to get a fix on what file#/block# are being waited on, and what object(s) they relate to in the database.

Once you've done that, it should be easier to see which is the most likely scenario.

Hope that helps,

-Mark

On Thu, 07 Feb 2002 05:50:37 -0500, Roger Jackson wrote:

> Hi,
>
> We found 20 DML (SELECT statements only) were hanging due to a "buffer
> busy waits" problem. The sessions were hung for about 4 hours as we
> tried to determine what caused the problem in the first place. In the
> end the database was bounced as the business could not afford any
> further delays.
>
> We detemined which table was causing the problem and the sessions and
> SQL statements which were *waiting* on the buffer. Looking at
> v$session_wait we found all waiting sessions had a P3 value of 130.
>
> We understand what the P3=130 means but how do we determine which
> session caused the "buffer busy wait" problem to occur in the first
> place?
>
> Some people have said you should do the following: 1. Increase the
> number of db_writers 2. Increase the buffer cache 3. Increase
> ini_trans on the table in question 4. Increase freelists on the table
> in question
>
> Database and O/S details Oracle V8.1.6.3, AIX 4.3.3
>
> Can someone please point me in the right direction?
>
> Please let me know if you require any further information.
>
> Thanks in advance.
Received on Thu Feb 07 2002 - 19:52:22 CST