Buffer Waits
The Magnet
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.
The Magnet
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.
Vladimir M. Zakharychev
Is it really high? How many consistent gets were performed since
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