RE: How to avoid gc current block busy

Not a bad idea. If you want to avoid partitioning for any reason, you can make n (n=3 in this case) “base tables” and reference them via a union all view when you need the entire contents or by the local base table name if that is a convenient query to generate.  

You might also be able to put the rows in one at a time followed by dummy rows (that you can later delete or not) such that each row is resident in a chunk likely to be “block mastered” on the node that updates it all the time. For one row per node tables this has similar dictionary overhead to partitioning without potential license costs.  

Attribute clustering on load might also do this for you, depending on your release and platform.  

Finally, if you really don’t need to view (pun intended) all n together, then just have one per node. While Oracle can sort all this out for you in a single table, certain combinations such as this one by their nature drive pinging things between nodes. It’s not that it won’t work, but you want to avoid the longer elapsed time for gcc coherency than doing each of these operations “locally.”  

mwf  

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Sayan Malakshinov Sent: Wednesday, September 25, 2019 4:20 AM To: GG
Cc: ORACLE-L
Subject: Re: How to avoid gc current block busy  

Hi Greg,  

If you have a problem with one table only, it would be easier to partition your table by clusterid, so each node read/write own partition only  

ср, 25 сент. 2019 г., 11:14 GG <grzegorzof_at_interia.pl>:

Hello,

I've got this 2 node RAC 11.2.0.4 and an app (actually 3 app servers) which is realy rac unfriendly.

There is a table 'clusternode' with 3 rows (64kb = 8 blocks) and ever 1-3 sec there is an update run on one of the rac node:

update clusternode set lastheartbeat = sysdate where clusterid = 'oneOftheStringwitClusterId' .

Normaly this query takes 8 li/o and takes below 1sec it is doing full table scan (there is no index on clusteridand Full table scan is the only possible plan).

But out of nothing it can take ~30 sec (but still 8 logical i/o) and the app is hanging, during that time I can see 'gc current block busy' .

It is 11.2.0.4 and the odd thing is I can not see any interconnect activity on AWR during the time of the issue (seems like a bug to me).

Is there any way to mitigate such behaviour, my current thoughts:

• node affinity via singleton service
• shutdown 1 rac node and run on 1 node only
• make this table pctfeee 99 ?

Any other ideas ?

Regards.

Greg

--
http://www.freelists.org/webpage/oracle-l






--
http://www.freelists.org/webpage/oracle-l