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

From: Roger Jackson <rjackson1_at_hotkey.net.au>
Date: Mon, 11 Feb 2002 10:09:24 GMT
Message-ID: <3c6798aa_1@news.iprimus.com.au>

Mark,

Thanks for the recommendations.

We have performed some tests over the last few days to see if we can replicate the problem.

This are our findings so far to date:

- No changes have been made to the database for over 2 months.
- Increasing database writers made no difference.
- ini_trans and freelists didn't make any difference as you suggested.
- Unfortunately, increasing the buffer cache didn't fix the problem.
- We were able to find the user causing the buffer to be locked and narrowed

it down to a SQL statement which produced a report. It has 14 "UNION ALL" statements!!!.
- We found that killing the user at the database level didn't work. We had to kill the user at the machine level. Once the user was killed all waiting users continued without any problems.
- We confirmed that only SELECT statements were being executed no other DML statements.
- The SQL has many nested loop joins
- All waiting users and holding users are running the same SQL report just different parameters being passed.
- Cannot upgrade to 8.1.7 as the vendor doesn't support this version of Oracle 8i.

Work completed:
- We have tried to replicate the problem by running the same reports simulatenously but without success
- Rebuilt all indexes and tables used in the SQL statement, the problem still appears.
- Performed a compute statistics on the tables used in the SQL statement, the problem still appears.
- The only positive thing out of this so far it that we don't have to bounce the database everytime this occurs. We refer to v$access to find the waiting and holding users and kill the user holding the buffer. Although, you would agree this is not a suitable solution. We are looking at tuning the SQL statement and seeing whether this will help. I found out today that this query was implemented back in October 2001 and hasn't caused any problems until last week. The volumes of data haven't changed as data is archived daily. Therefore the explain shouldn't have changed but we don't know for sure.

We know the symptons we just need the cause... Maybe we have hit an Oracle bug in 8.1.6.3!!!!

Regards,

Roger.

"Mark J. Bobak" <mark_at_bobak.net> wrote in message news:pan.2002.02.07.20.52.15.766611.12018_at_bobak.net...
> 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 Mon Feb 11 2002 - 04:09:24 CST