Re: Sessions are waiting on buffer busy wait with P3 value 130 in v$s

Hi Steve,

A couple questions: whihc p3 value is the value that indicates a need to increase freelists? Are there any plans to adapt your freeliusts estimation script for tables for indexes as well, or pointers on how to do so (I think I can do a direct port to work off dba_indexes, butparts of the logic aren't clear to me, so I feel like that would be shooting in the dark.)

Thanks,

George

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Friday, May 25, 2001 12:09 PM v$s

> Hi Sri,
>
> If you can identify the segments involved from the p1 and p2 parameters
and put
> those segments into a KEEP buffer pool you should be able to get a lot of
> relief. Failing that, or if they are too big, consider either increasing
> db_block_buffers if you can or setting _db_percent_hot_default to
something like
> 80. That would allow up to 80% of the DEFAULT buffer pool to be used for
hot
> buffers thereby improving the caching of the buffers that are causing you
to
> wait.
>
> @ Regards,
> @ Steve Adams
> @ http://www.ixora.com.au/
> @ http://www.christianity.net.au/
>
>
> -----Original Message-----
> Sent: Friday, 25 May 2001 9:26
> To: Multiple recipients of list ORACLE-L
> v$s
>
>
> We've lot buffer busy waits in data block with P3 value 130 in V$session.
>
> I've read about this in few notes, but still I do not understand few
things.
>
>
> P3=130 means:
> -------------
> Block is being read by another session and no other
> suitable block image was found, so we wait until the read
> is completed. This may also occur after a buffer cache
> assumed deadlock. The kernel can't get a buffer in a
> certain amount of time and assumes a deadlock.
> Therefore it will read the CR version of the block.
>
> Does it mean that the block is being held in an incompatible mode by the
> first session or set of sessions reading it in shared mode, and current
> session is trying to lock it exclusively for a DML and waiting for the
same.
>
>
> What are steps we can take to avoid this types of buffer busy waits?
>
> Thanks for your help.
> Sri
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Srikannan Gopalsamy
> INET: sgopalsamy_at_walmart.com
>
> 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: ListGuru_at_fatcity.com (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).
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Steve Adams
> INET: steve.adams_at_ixora.com.au
>
> 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: ListGuru_at_fatcity.com (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).
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: George Schlossnagle
  INET: george_at_omniti.com

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: ListGuru_at_fatcity.com (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).