Re: now what??
- From: Robyn <robyn.sands@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Tue, 24 Aug 2004 12:22:34 -0400
Mark,
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 ...
Robyn
On Tue, 24 Aug 2004 11:29:26 -0400, Bobak, Mark
<mark.bobak@xxxxxxxxxxxxxxx> 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: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
