 | |
help: high "gc busy buffer"
comp.databases.oracle.server comp.databases.oracle.server
 |
| | Thread Tools | Display Modes |
#1
 
| |||
| |||
|
help: high "gc busy buffer" -
01-11-2011
, 04:15 PM
#2
| |||
| |||
|
|
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 |
load test. Some of the win-runners and other load testing software
cannot (IMProfessionalO) accurately simulate real-life day to day
workloads.
What was the timeframe for these statistics? 1 minute? 1hr? I am
curious as to how you have a 300 row table that does 1800 inserts.
2000 updates - again what timeframe. As for the number, it does not
seem that bad to me... What is the effect of these buffer busy waits?
Are users actually waiting for a response in the app?
If there are at most 300 rows with 1800 delete/inserts and 2000
updates, you may want to do another transactional analysis and ask the
question as to why are you doing it this way and is there a better way
to achieve the end goal - whatever that may be.
#3
| |||
| |||
|
|
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? |
partitioning". You should make sure that no table is being heavily
updated from more than 1 node at any given time.
--
http://mgogala.byethost5.com
#4
| |||
| |||
|
|
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 Lewis
http://jonathanlewis.wordpress.com
#5
| |||
| |||
|
|
"charles" <dshprope... (AT) gmail (DOT) com> wrote in message news:0448b00a-5519-478f-b3e4-893d3eebdc69 (AT) l7g2000vbv (DOT) 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 - |
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?
#6
| |||
| |||
|
|
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? |
What is the primary key - constantly increasing number, session id, GUID
....
How big is the table likely to grow ?
If the table is going to stay small and you have lots of inserts and
deletes then
a) the index will be small
b) all the index entries will be packed into a small number of blocks
Reverse key won't help. If you are licensed for the partitioning option
then a hash partitioned IOT with (say) 512 or 1024 partitions might keep
the contention within acceptable limits. But since I'm only guessing what
the table is for and how it will be used that's not a very solid
suggestion.
--
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.