Re: Buffer Waits
From: The Magnet <art_at_unsu.com>
Date: Thu, 12 Nov 2009 12:26:05 -0800 (PST)
Message-ID: <b1514988-6fbf-43d9-b9b4-08888f32215d_at_d21g2000yqn.googlegroups.com>
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.
Date: Thu, 12 Nov 2009 12:26:05 -0800 (PST)
Message-ID: <b1514988-6fbf-43d9-b9b4-08888f32215d_at_d21g2000yqn.googlegroups.com>
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. Received on Thu Nov 12 2009 - 14:26:05 CST