Re: Buffer Waits
From: Vladimir M. Zakharychev <vladimir.zakharychev_at_gmail.com>
Date: Fri, 13 Nov 2009 01:50:02 -0800 (PST)
Message-ID: <bd3a5f54-7111-4e2f-ba86-a7c524b9bce8_at_g27g2000yqn.googlegroups.com>
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
> from.
Date: Fri, 13 Nov 2009 01:50:02 -0800 (PST)
Message-ID: <bd3a5f54-7111-4e2f-ba86-a7c524b9bce8_at_g27g2000yqn.googlegroups.com>
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
> from.
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
Received on Fri Nov 13 2009 - 03:50:02 CST