Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: now what??

Re: now what??

From: Robyn <>
Date: Tue, 24 Aug 2004 12:22:34 -0400
Message-ID: <>


These are primarily data buffer / 130 type waits and the sql causing most of them is cursor type junk that needs to be rewritten. I added freelists to a few objects yesterday and saw the numbers decrease slightly, but I believe everything that is left falls exactly into this category.

sigh .. further confirmation that this is going to take a long, long time ...


On Tue, 24 Aug 2004 11:29:26 -0400, Bobak, Mark <> wrote:
> In addition to Cary's excellent advice, here's a bit of specific =
> knowledge
> that may help you. When you encounter buffer busy waits, there are a =
> few
> things to consider. What type of block is seeing the waits? If it's a
> segment header, then looking to increase free lists is probably a good
> idea. If it's data blocks (table or index), then what's the P3 value?
> If it's 130, that means that the buffer is busy cause data is being read
> into it (due to one of the db file sequential/scattered reads). These
> types of buffer busy waits are secondary to heavy physical I/O that's
> colliding on blocks. (This can easily be seen/simulated by doing
> concurrent full table scans on "large" tables, for sufficiently large
> values of "large", or even due to concurrent fast full index scans or
> grossly inefficient index full or range scans.) So, if you see P3
> set to 130 for data blocks, you want to focus on your inefficient SQL
> that's causing db file sequential/scattered reads. When you get those
> under control, that entire class of buffer busy waits ought to =
> disappear.
> There are other cases of buffer busy waits, but in my experience=20
> they're less common, and I won't write more on them unless you reply =
> back
> that you're seeing things that don't fit either the segment header case=20
> or the data block w/ P3 set to 130 case.
> You definitely want to follow Cary's advice as to the overall approach,
> but the above maybe helpful in diagnosing and dealing w/ buffer busy
> waits as you encounter them.
> Hope that helps,
> -Mark

Please see the official ORACLE-L FAQ:

To unsubscribe send email to: put 'unsubscribe' in the subject line.
Archives are at
FAQ is at
Received on Tue Aug 24 2004 - 11:18:20 CDT

Original text of this message