Re: Buffer Waits
Date: Fri, 13 Nov 2009 01:50:02 -0800 (PST)
On Nov 12, 11:26 pm, The Magnet <a..._at_unsu.com> wrote:
> On Nov 12, 11:21 am, The Magnet <a..._at_unsu.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
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.
Vladimir M. Zakharychev
N-Networks, makers of Dynamic PSP(tm) http://www.dynamicpsp.com Received on Fri Nov 13 2009 - 03:50:02 CST