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
--- Viraj Luthra <[EMAIL PROTECTED]> wrote:
> Hello folks,
>
> How do I come to know that there is a contention
> going on in the freelists? Is there any v$ table or
> this type of contention you derive from some other
> info. like a ripple effect from some parameter?
>
> TIA.
>
> raja
>
>
> Get 250 color business cards for FREE!
> http://businesscards.lycos.com/vp/fastpath/
> --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> --
> Author: Viraj Luthra
> INET: [EMAIL PROTECTED]
>
> Fat City Network Services -- (858) 538-5051 FAX:
> (858) 538-5051
> San Diego, California -- Public Internet
> access / Mailing Lists
>
--------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an
> E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of
> 'ListGuru') and in
> the message BODY, include a line containing: UNSUB
> ORACLE-L
> (or the name of mailing list you want to be removed
> from). You may
> also send the HELP command for other information
> (like subscribing).
__________________________________________________
Do You Yahoo!?
Make international calls for as low as $.04/minute with Yahoo! Messenger
http://phonecard.yahoo.com/
