Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Buffer busy waits with P3 value 130 in v$session_wait

Buffer busy waits with P3 value 130 in v$session_wait

From: Roger Jackson <rjackson1_at_hotkey.net.au>
Date: Thu, 07 Feb 2002 10:50:37 GMT
Message-ID: <3c625c44$1_1@news.iprimus.com.au>


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 - 04:50:37 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US