Re: gc buffer busy and rac testing
Date: Fri, 9 Jan 2009 10:17:40 -0000
Message-ID: <AoOdnSPDceXXufrUnZ2dneKdnZydnZ2d_at_bt.com>
"helter skelter" <helterskelter_at_gmail.com> wrote in message news:gk51nr$gv8$1_at_mx1.internetia.pl...
>>
>> "SQL ordered by cluster wait time" - this will probably show you
>> the most significant SQL relating to the problem. Then check
>> the segment statistics "'Segments by Global Cache Buffer Busy Waits "
>> this will tell you which objects are hit hardest.
>>
> I've done little investigation and I found sql resposible for this > waits, it's a simple query with join and order, quering two tables. > Is it normal ?
Not what I'd hope to see in the simplest case - unless the "simple query" is a select for update. Mladen's comments about the overheads of generating read-consistent blocks may be relevant though if the query is very busy reading blocks which are subject to lots of rapid update on the remote node.
Is the number of waits in the "segments by GC buffer busy waits" similar to the number of "gc buffer busy waits" - and do you get any further clues from the segment waits by "Current Blocks received", and "CR blocks received".
It's also worth checking the 'Global cache transfer stats' looking at the 'busy' column, as this will give you some clues about whether the problem blocks were data blocks, undo blocks, or "other".
Irrespective of whether the query you've identified is THE guilty party, though - any SQL that causes a lot of traffic across the interconnect is undesirable because of CPU usage and congestion effects - so if it's easy to make this query do less block visits then it may have a beneficial side effect on you gc buffer busy waits.
-- Regards Jonathan Lewis http://jonathanlewis.wordpress.com Author: Cost Based Oracle: Fundamentals http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.htmlReceived on Fri Jan 09 2009 - 04:17:40 CST