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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] 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@xxxxxxxxxx>:
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
--
//www.freelists.org/webpage/oracle-l
