Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

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

Re: now what??

From: Robyn <robyn.sands_at_gmail.com>
Date: Tue, 24 Aug 2004 12:22:34 -0400
Message-ID: <ece8554c04082409223684ebd7@mail.gmail.com>


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_at_il.proquest.com> 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_at_freelists.org put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Received on Tue Aug 24 2004 - 11:18:20 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US