Re: q: buffer busy wait(data block wait) when P3=130

ricky,
here is some additional info I found in statspack.

1. table x is defined with freelists =10 freelist groups =1
2. during the 3 hrs there are about 100,000 records were inserted into x.
3. the primary key is an oracle sequence.
4. when one query(300 times during 3hrs)  run against the table x.

   I got buffer busy wait p3=130 on different blocks on the same    table.
my questions are:
1. why am I getting p3=130 instead of 230 ?

   1010 Trying to get a buffer in CR/CRX mode , but a  or 230 modification has started on the buffer that has not

           yet been completed.
2. would increasing freelist groups help ?

thnx ted    

Ricky Sanchez <rsanchez_at_more.net> wrote in message news:<3C38CCFF.5DD00C22_at_more.net>...
> Ted-
>
> INITRANS will do nothing for you here. Buffer busy waits refer to the
> whole block, while INITRANS affects the itl list within the block. You
> said you know what table this is, so I assume it is not an index.
>
> The underlying cause here is an IO wait, or possibly a phenomenon known
> as a "buffer deadlock". A buffer deadlock is not a deadlock at all,
> simply a timeout while waiting for a buffer to be available. I mention
> this because the deadlock thing is fairly common when you have high
> inserts into a primary key index with a sequence. The fix for such a
> problem would probably be to implement reverse key indexes.
>
> In the case of a table, you might trace the application with 10046 level
> 12 for a few seconds, or simply dig out the sql that is executing at
> that time. You need to know if you are inserting or updating. If this is
> a high insert situation, then adding freelists or freelist groups are
> probably going to help. Start with process freelists since that table
> attribute can be altered on the fly. Freelist groups require a table
> recreation.
>
> In the case of updates or selects, you have an application problem where
> too many sessions are hitting the same block(s). That requires some
> design thinking, since the application is probably doing something dumb.
> Consider an app modification that allows the client to cache data from
> the frequently accessed rows that are only being read. For updates, you
> just have to study the app and come up with something sensible that does
> not require the common rows to be repeatedly updated. Fortunately, these
> two causes are less common than the insert "freelist" problem.
>
> So, review the sql, consider the root cause. If it is update or select
> driven, review the application design. If it is caused by many
> concurrent inserts, consider adding process freelists or freelist
> groups. If this is 9i, consider the new bitmap segment feature.
>
> - ricky
>
>
> Ted Chyn wrote:
> >
> > all,
> >
> > 1. table cause the following waits had been identified.
> > this table defined with default freelists and freelist group and
> > intrans.
> >
>
> >
> > 3. Question: would increaing freelists/freelist group/intrans would
> > help in reducing data block wait when p3 = 130 ?
> >
> > thnx ted chyn
> >
Received on Mon Jan 07 2002 - 14:15:50 CST