Re: gc buffer busy and rac testing
Date: Thu, 8 Jan 2009 03:53:50 -0800 (PST)
helter skelter wrote:
> 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.
> 2 nodes - 324
> 1 node - 473
> 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.
When we implemented we RAC, we found that we had to make some schema changes to optimize its performance. Primarily, we had to partition several tables and indexes to ensure the same blocks were not being requested between instances. We almost always saw this contention on index blocks used by the primary key during inserts that used a sequence for the primary key value.
I would suggest running a query against dba_hist_active_sess_history (assuming you are licensed for it) for the period of the test and getting a count grouped by sql_id, p1, and p2 where the event is gc buffer busy. That will allow you to identify the SQL that generates the wait, as well as the file and block in contention. As soon as you have that, query dba_extents to get the segment that "owns" the block, and dba_hist_sqltext to get the SQL statement.
Assuming this is the issue, then rather than partitioning, you can also look at using a reverse key index, but that removes index range scans as a possible execution plan. This may or may not affect you.
HTH, Steve Received on Thu Jan 08 2009 - 05:53:50 CST