Re: gc buffer busy and rac testing
From: helter skelter <helterskelter_at_gmail.com>
Date: Thu, 08 Jan 2009 15:07:19 +0100
Message-ID: <gk51nr$gv8$1_at_mx1.internetia.pl>
Jonathan Lewis pisze:
> "helter skelter" <helterskelter_at_gmail.com> wrote in message
> news:gk4ors$qae$1_at_mx1.internetia.pl...
>
>
> "gc buffer busy" is the RAC equivalent of "buffer busy waits"
> when you have multiple nodes that (usually) want to modify
> the same blocks at the same time.
>
> In your case, with order entry, the fact that you are using ASSM
> probably stops this from happening in the tables, but you will
> have primary key indexes on things like ORDERS and ORDER_LINES
> which are probably sequence-based, and the "right hand" block
> from these indexes is probably suffering severe competition as it
> hammers back and fore between the two nodes.
>
> There may also be problems with the block holding the sequence
> definitions, but in this case you'd also see waits on the SQ enqueue.
>
> Check the AWR reports (or statspack running at level 7 if you're
> not licensed for AWR) and you will find:
>
> "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.
>
> If the object do happen to be sequence-based primary key indexes,
> then you may be able to address the issue simply by rebuilding the indexes
> as REVERSE indexes. (This does have some side-effects, including the
> potential for increasing the size of the buffer cache to help Oracle
> maintain
> the caching benefit it had on this index before reversal).
>
>
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 ? Received on Thu Jan 08 2009 - 08:07:19 CST
Date: Thu, 08 Jan 2009 15:07:19 +0100
Message-ID: <gk51nr$gv8$1_at_mx1.internetia.pl>
Jonathan Lewis pisze:
> "helter skelter" <helterskelter_at_gmail.com> wrote in message
> news:gk4ors$qae$1_at_mx1.internetia.pl...
>> Hi, >> >> I'am testing 2 node RAC but I haven't got too much experince with this >> technology and I am little puzzled after test results. >> I used Swingemch (order_entry test) and when I ran this test on 2 nodes >> RAC performance is much lower then on only one node of this cluster. >> >> TPS >> 2 nodes - 324 >> 1 node - 473 >> >> TMP >> 2 nodes - 17571 >> 1 node - 25272 >> >> When I look at waits I see (2 node test) that 60% are "gc buffer busy". >> Isn't it too much ? any advices what more can I check? >> >> Tablespace is localy managed, with auto segment space management and >> system allocation type. Interconnect on dedicated 1GB ethernet. Oracle >> 10.2.0.4 (standard edition) on RHEL5 with asm. >> >> thakns
>
>
> "gc buffer busy" is the RAC equivalent of "buffer busy waits"
> when you have multiple nodes that (usually) want to modify
> the same blocks at the same time.
>
> In your case, with order entry, the fact that you are using ASSM
> probably stops this from happening in the tables, but you will
> have primary key indexes on things like ORDERS and ORDER_LINES
> which are probably sequence-based, and the "right hand" block
> from these indexes is probably suffering severe competition as it
> hammers back and fore between the two nodes.
>
> There may also be problems with the block holding the sequence
> definitions, but in this case you'd also see waits on the SQ enqueue.
>
> Check the AWR reports (or statspack running at level 7 if you're
> not licensed for AWR) and you will find:
>
> "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.
>
> If the object do happen to be sequence-based primary key indexes,
> then you may be able to address the issue simply by rebuilding the indexes
> as REVERSE indexes. (This does have some side-effects, including the
> potential for increasing the size of the buffer cache to help Oracle
> maintain
> the caching benefit it had on this index before reversal).
>
>
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 ? Received on Thu Jan 08 2009 - 08:07:19 CST