Re: high "gc busy buffer"
From: charles <dshproperty_at_gmail.com>
Date: Wed, 12 Jan 2011 12:09:26 -0800 (PST)
Message-ID: <ecd9f8b4-0bcc-497b-9e49-9a0f146159a2_at_i17g2000vbq.googlegroups.com>
On Jan 12, 7:42 am, "Jonathan Lewis" <jonat..._at_jlcomp.demon.co.uk> wrote:
> "charles" <dshprope..._at_gmail.com> wrote in message
>
> news:0448b00a-5519-478f-b3e4-893d3eebdc69_at_l7g2000vbv.googlegroups.com...
>
>
>
>
>
> > Dear group,
>
> > We are using Oracle 10g on linux RAC with 4 nodes. We have a small
> > table, no more than 300 rows, but most people will update/delete/
> > insert on it during load testing. And ASH report shows it is the
> > cause of this contention.
>
> > EVENT CNT
> > ------------------------------ ----------
> > gc current retry 4
> > gc current grant 2-way 31
> > gc current request 47
> > gc cr request 66
> > gc cr multi block request 69
> > gc current block busy 79
> > gc cr grant 2-way 81
> > gc current grant busy 113
> > gc current block 2-way 416
> > gc cr block busy 420
> > gc cr block 2-way 568
> > gc buffer busy 6448
>
> > All the "gc buffer busy" points to one small table, no index/triggers
> > on this table, which 2000 update, 1800 insert, 1600 select. The table
> > is created in a ASSM tablespace, so i guess freelist is not going to
> > help here.
>
> > Could somebody give me some advice?
>
> > Thanks
>
> How can you have no more than 300 rows if you have 1800 inserts and no
> deletes ?
>
> Before asking for a solution, make sure you are describing the problem
> properly.
>
> No indexes - means every select/update will have to do a full tablescan,
> which means you must constantly be moving all the table blocks across the
> interconnect. If you can't isolate the activity to just one node you need
> to spread the contention as much as possible. If the table really is small
> and is going to stay small you could try implementing a single table hash
> cluster with one key per block, so two nodes will only compete for a block
> if they both want the same row at the same time. (With a single table hash
> cluster you can also avoid having to maintain an index and still get high
> precision access).
>
> --
> Regards
>
> Jonathan Lewishttp://jonathanlewis.wordpress.com- Hide quoted text -
>
> - Show quoted text -
Date: Wed, 12 Jan 2011 12:09:26 -0800 (PST)
Message-ID: <ecd9f8b4-0bcc-497b-9e49-9a0f146159a2_at_i17g2000vbq.googlegroups.com>
On Jan 12, 7:42 am, "Jonathan Lewis" <jonat..._at_jlcomp.demon.co.uk> wrote:
> "charles" <dshprope..._at_gmail.com> wrote in message
>
> news:0448b00a-5519-478f-b3e4-893d3eebdc69_at_l7g2000vbv.googlegroups.com...
>
>
>
>
>
> > Dear group,
>
> > We are using Oracle 10g on linux RAC with 4 nodes. We have a small
> > table, no more than 300 rows, but most people will update/delete/
> > insert on it during load testing. And ASH report shows it is the
> > cause of this contention.
>
> > EVENT CNT
> > ------------------------------ ----------
> > gc current retry 4
> > gc current grant 2-way 31
> > gc current request 47
> > gc cr request 66
> > gc cr multi block request 69
> > gc current block busy 79
> > gc cr grant 2-way 81
> > gc current grant busy 113
> > gc current block 2-way 416
> > gc cr block busy 420
> > gc cr block 2-way 568
> > gc buffer busy 6448
>
> > All the "gc buffer busy" points to one small table, no index/triggers
> > on this table, which 2000 update, 1800 insert, 1600 select. The table
> > is created in a ASSM tablespace, so i guess freelist is not going to
> > help here.
>
> > Could somebody give me some advice?
>
> > Thanks
>
> How can you have no more than 300 rows if you have 1800 inserts and no
> deletes ?
>
> Before asking for a solution, make sure you are describing the problem
> properly.
>
> No indexes - means every select/update will have to do a full tablescan,
> which means you must constantly be moving all the table blocks across the
> interconnect. If you can't isolate the activity to just one node you need
> to spread the contention as much as possible. If the table really is small
> and is going to stay small you could try implementing a single table hash
> cluster with one key per block, so two nodes will only compete for a block
> if they both want the same row at the same time. (With a single table hash
> cluster you can also avoid having to maintain an index and still get high
> precision access).
>
> --
> Regards
>
> Jonathan Lewishttp://jonathanlewis.wordpress.com- Hide quoted text -
>
> - Show quoted text -
Jonathan,
thanks very much for your help. No wonder i set pctfree=99, and the situation get worse. Yes, i forget, there is select, insert, update, delete with 5,000 people sign on at the same time.
I will try to use one node tomorrow, but not sure CPU will be able to handle that.
If i do not want to use single hash clustered table, as i am not sure that table will stay small, i should create an reverse key index for this, am I right? Received on Wed Jan 12 2011 - 14:09:26 CST