Fro my clarity ..
What does the extent size has to do with segment header contention.
And about index rebuilds.. Why do u say that this will cause a Problem
for the waits he is Experencing.
Hope to get some clarity on this... Thanks....
Best Regards,
Ganesh R
Tel : +971 (4) 397 3337 Ext 420
Fax : +971 (4) 397 6262
HP : +971 (50) 745 6019
==========================================
Live to learn... forget... and learn again.
==========================================
-----Original Message-----
Muthukumaren
Sent: Friday, July 12, 2002 6:13 PM
To: Multiple recipients of list ORACLE-L
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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Ganesh Raja
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).
