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

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 Sun Jan 06 2002 - 16:16:47 CST