| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Freelist Contention
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
ID's
<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.
Scott
 |
 |