 | |
#1
 
| |||
| |||
|
Buffer Waits -
11-12-2009
, 11:21 AM
#2
| |||
| |||
|
|
Hi, I was poking around and took a look at v$waitstat: CLASS * * * * * * * * * COUNT * * * TIME ------------------ ---------- ---------- data block * * * * * * *15079 * * * 6348 sort block * * * * * * * * *0 * * * * *0 save undo block * * * * * * 0 * * * * *0 segment header * * * * * * *7 * * * * *0 save undo header * * * * * *0 * * * * *0 free list * * * * * * * * * 0 * * * * *0 extent map * * * * * * * * *0 * * * * *0 1st level bmb * * * * * * * 2 * * * * *0 2nd level bmb * * * * * * * 1 * * * * *1 3rd level bmb * * * * * * * 0 * * * * *0 bitmap block * * * * * * * *0 * * * * *0 bitmap index block * * * * *0 * * * * *0 file header block * * * * *13 * * * * *1 unused * * * * * * * * * * *0 * * * * *0 system undo header * * * * *0 * * * * *0 system undo block * * * * * 0 * * * * *0 undo header * * * * * * *8633 * * * 1071 undo block * * * * * * * 1440 * * * * *7 Looks like "data block" and "undo header" are a serious problem. *It seems that every tablespace was created with ASSM enabled. *I've read in some places that ASSM is supposed to fix the data block contention, while others say it does nothing and the real way to fix this is using FREELISTS. This is a highly OLTP system. *I know there are thousands of things that can be looked at, but, which is correct here? *ASSM or non-ASSM? That may make a significant difference? We're on 10gR2. I'm still reading other sites and such, but was looking for everyones thoughts and opinions. |
bad number. Some documentation said to look at V$SESSION_WAIT and
look at P1, P2 & P3 to determine the object causing the contention.
Well, P2 & P3 are 0 in nearly all of them. In fact, 95% of the events
are "SQL*Net message from client"
So, I do not understand where the high number of data blocks come
from.
#3
| |||
| |||
|
|
On Nov 12, 11:21*am, The Magnet <a... (AT) unsu (DOT) com> wrote: Hi, I was poking around and took a look at v$waitstat: CLASS * * * * * * * * * COUNT * * * TIME ------------------ ---------- ---------- data block * * * * * * *15079 * * * 6348 sort block * * * * * * * * *0 * * * * *0 save undo block * * * * * * 0 * * * * *0 segment header * * * * * * *7 * * * * *0 save undo header * * * * * *0 * * * * *0 free list * * * * * * * * * 0 * * * * *0 extent map * * * * * * * * *0 * * * * *0 1st level bmb * * * * * * * 2 * * * * *0 2nd level bmb * * * * * * * 1 * * * * *1 3rd level bmb * * * * * * * 0 * * * * *0 bitmap block * * * * * * * *0 * * * * *0 bitmap index block * * * * *0 * * * * *0 file header block * * * * *13 * * * * *1 unused * * * * * * * * * * *0 * * * * *0 system undo header * * * * *0 * * * * *0 system undo block * * * * * 0 * * * * *0 undo header * * * * * * *8633 * * * 1071 undo block * * * * * * * 1440 * * * * *7 Looks like "data block" and "undo header" are a serious problem. *It seems that every tablespace was created with ASSM enabled. *I've read in some places that ASSM is supposed to fix the data block contention, while others say it does nothing and the real way to fix this is using FREELISTS. This is a highly OLTP system. *I know there are thousands of things that can be looked at, but, which is correct here? *ASSM or non-ASSM? That may make a significant difference? We're on 10gR2. I'm still reading other sites and such, but was looking for everyones thoughts and opinions. Something else that makes no sense. *Look at "data block", a pretty bad number. *Some documentation said to look at V$SESSION_WAIT and look at P1, P2 & P3 to determine the object causing the contention. Well, P2 & P3 are 0 in nearly all of them. *In fact, 95% of the events are "SQL*Net message from client" So, I do not understand where the high number of data blocks come from. |
startup? How long since startup? According to the wait stats your
instance wasted about 74 seconds total on buffer busy waits - is that
a lot compared to total uptime? In other words - are you experiencing
a performance issue with your database that you can definitely
attribute to buffer cache contention? If you are not - don't bother
fixing what's not broken.
Regards,
Vladimir M. Zakharychev
N-Networks, makers of Dynamic PSP(tm)
http://www.dynamicpsp.com
 |
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.