| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Buffer busy waits are 10.96% of non-idle waits
Hi Ganesh,
First of all the problem here is "Buffer busy waits "
When you have determined the "Buffer busy waits" is one of the top wait
events
causing problems then
Top ' classes' were
Data blocks(1), undo header (2) segment header(3)
Question :
What does the extent size has to do with segment header contention.
====> There is no "segment header" contention ,
this is "Buffer Busy waits" problem
Answer:
"Segment header" class being one of the top class ==> to avoid this wait
in the future the following needs to be performed
a. Adding more free lists (since you did not have enough freelists to
keep with the insert)
b. Extent sizes of the involved objects were small (tables keeps
growing)
therefore increasing the extent sixes would also help
Question :
Data block Class: was one of the top waits
Solution:
Thanks,
Kavi
Ganesh Raja wrote:
> 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: 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: Ganesh Raja > INET: rlnsimha_at_emirates.net.ae > > 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).Received on Fri Jul 12 2002 - 13:33:23 CDT
- text/x-vcard attachment: Card for Kavitha Muthukumaren
 |
 |