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: [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).
begin:vcard
n:;Kavitha
x-mozilla-html:FALSE
adr:;;;;;;
version:2.1
email;internet:[EMAIL PROTECTED]
fn:Kavitha Muthukumaren
end:vcard
