Re: Buffer busy waits are 10.96% of non-idle waits

Hi,

 Your major problems with buffer busy waits are in the

Data blocks class: (1), undo block(2), segment header (3)

For Data block Class:

Solution:

1. Reduce no of rows by changing pctfree/pctused
2. check when the last time your indexes were re-built and rebuild them often (indexes causing inserts into the same block will be reduced)

For undo block class :
Solution:

1. Increase the size of the rollbacksegment

For segment header :
Solution:

1. Add more freelists and or freelist group
2. Check your extent sizes (may be it is too small)
   • Hope this helps, Thanks, Kavi

oraora oraora wrote:

> Hi Kavitha,
>
> querying v$waitstat gives me the o/p below.
>
> CLASS COUNT TIME
> ------------------ ---------- ----------
> data block 131525173 225446798
> sort block 0 0
> save undo block 0 0
> segment header 4968 16264
> save undo header 0 0
> free list 0 0
> extent map 0 0
> bitmap block 0 0
> bitmap index block 0 0
> unused 0 0
> system undo header 0 0
> system undo block 0 0
> undo header 1582 14
> undo block 45965 3008
>
> the data block above belongs to a datafile USERS01.DAT which has
> all the tables and indexes the application uses.
>
> the top 25 SQL statements are always SELECT statements.
> they get executed repeatedly.
> is it b'coz all SQLs are with literals and no bind variables ?
>
> it's a highly read OLTP system.
>
> will not
> -- using bind variables instead of literals
> -- seperating tables and indexes to diferent tabelspace
>
> solve my problem ?
>
> Regards,
> prem.
>
> On Fri, 12 Jul 2002 Kavitha Muthukumaren wrote :
> >
> >Hi ,
> >
> >TOAD gives this alarm often. what does it mean ? which view
> >will
> >give me the wait statistics ?
> >
> >Answer :
> >======
> >Please run STATSPACK to if this is one of the top waitevents to
> >check if the percentage of wait - can be treated as problematic
> >one
> >
> > SELECT p1 "File", p2 "Block", p3 "Reason"
> > FROM v$session_wait
> > WHERE event='buffer busy waits'
> >Repeatedly run the above statement and collect the output. After
> >a period
> >of time sort the results to see which file & blocks are showing
> >contention:
> >
> >
> >" it occurs when a session cannot access a block because it is
> >in
> >use by another session. The two most common causes are
> >insufficient free lists for a table or insufficient rollback
> >segments. " --- IS THIS THE REASON ?
> >
> >Answer:
> >======
> > Yes on top of the wait event could also occur
> > could also occur if
> >
> >a. if the application is going against a set of same blocks (hot
> >blocks)
> >
> >Thanks,
> >Kavi
>
> _________________________________________________________
> There is always a better job for you at Monsterindia.com.
> Go now http://monsterindia.rediff.com/jobs
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: oraora oraora
> INET: oraoraora_at_rediffmail.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: Kavitha Muthukumaren
  INET: Kavitha.Muthukumaren_at_oracle.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).



text/x-vcard attachment: Card for Kavitha Muthukumaren