Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Freelist Contention

Re: Freelist Contention

From: Scott <>
Date: Tue, 21 Aug 2001 20:13:35 -0700
Message-ID: <>

Raja, You will only have freelist contention on conventional insert operations only. Insert operations from direct loads won't cause freelist contention because of the way direct inserts are done. You can check for freelist contention by checking V$SESSION_WAIT and look for the Event 'Buffer Busy Waits'. If you see you have these waits you will need to look at the P1, P2 and P3 columns to determine what block is causing the wait as well as the type of wait.

P1 - Absolute file #
P2 - Block #
P3 - ID


<8.1.6 >=8.1.6

   0 0 Block being read

  1003 100 Want to new the block but is being read (maybe for undo)

  1007 200 Want to new the block but another session is holding it (possibly changing the block)

  1010 230 Trying to get a cr on the buffer but a session modifying the buffer has not completed.

  1012 A modification is happening on SCUR or XCUR (OPS or RAC) but has not completed

  1012 231 dup of 1010

  1013 130 block is being read another session and no other suitable image was found so we wait until the read is complete.

  1014 110 We want the current block but someone else is currently reading into the cache.

  1014 120 duplicate

  1016 210 The session wants the block in SCUR or XCUR mode.

  1016 220 Duplicate of 1014

If you join p1 and p2 to dba_segments columns Header_file and Header_block and you get match on the join chances are you have a freelist problem.

If p1 and p2 are datablocks then the block could be waiting on reads. If the waits are for reads chances are you are scanning unselective indexes. If the waits are for modifications then you need to add more inittrans or reduce the number of rows in the block. Reducing the number of rows in the block can be achieved by using pctfree/pctused or min records per block(8i feature). Data block waits could also be caused by multiple process inserting into an index at the index value.

If it is undo header then add more rollback segments. If it is undo blocks then you might try to make the rollback segment bigger.

BBW's are cause by a session waiting for another session to complete a read or the buffer is in incompatible mode. The blocker may be modifying the block we want or may be waiting on another wait operation (typically IO). The ID above should help show which. If we are in a buffer busy wait state and there is no other session which is causing this wait then possible causes are spinning process with buffer lock. Oracle will normally wait 1 second and if it has to wait again for an exclusive buffer it will wait 3 seconds.


Do You Yahoo!?
Make international calls for as low as $.04/minute with Yahoo! Messenger Received on Tue Aug 21 2001 - 22:13:35 CDT

Original text of this message