RE: RAC partitioning idea bounce
Date: Fri, 6 Sep 2013 16:21:42 +0000
Message-ID: <BAA6E28B6241F046AED1E62D8697516C6F78D83A_at_COPDCEXMB08.cable.comcast.com>
Thanks David. I should have mentioned that. I don't think I can do much about re-mastering, but it should only re-master if a block is hit regularly on one node and once the block is full then it won't get touched again until the processing job hits it and that would all be from just one-node. I'm hoping the logic of hash-partitioning will solve most of the contention and allow us to insert across all nodes - hopefully meaning faster loading. From: David Fitzjarrell [mailto:oratune_at_yahoo.com] Sent: Friday, September 06, 2013 10:09 AM To: Walker, Jed S; oracle-l_at_freelists.org Subject: Re: RAC partitioning idea bounce
Your logic is sound, as I see it, but the RAC performance issue may not be caused solely by the segment contention. Depending on the volume of data inserted running multi-node inserts into a table may cause Oracle to re-master the blocks from the current node to the busiest node at the time, and if that activity occurs on different nodes in succession the constant re-mastering may create undue waits in addition to the block contention. Using a single node prevents this re-mastering.
My two cents.
David Fitzjarrell
From: "Walker, Jed S" <Jed_Walker_at_cable.comcast.com>
To: "oracle-l_at_freelists.org" <oracle-l_at_freelists.org>
Sent: Friday, September 6, 2013 9:55 AM
Subject: RAC partitioning idea bounce
Hi All,
I have a system on 11.2.0.3 4-node RAC that I'm trying to help with. They have a process that inserts rows into a table. A database job then takes those rows and processes them into another table. There is a service configured on the RAC so that the inserting applications only connect to one RAC instance because they say the contention is too great when running across all nodes. I'm figuring the contention is due to all 4 nodes trying to insert into the same segment.
So, I thought, what if I partition based on the sole index (UK) on the table using Hash partitioning - to spread the inserts into multiple segments. The catch is that the table is partitioned already on a special partition_value column that is used by the processing job so it can process one partition of data at a time and then truncate the partition. So, if I re-partition on the UK column then the processing job can't truncate the partitions any more. I don't have the option of changing all the processing code.
So, I think this is my best option and I'm looking for the insight of anyone with lots of partitioning experience. Recreate the table with partitioning on the partition_value column so that the job can do run the way it does currently, and then sub-partition on the UK column using a hash partition so that inserts will be distributed into many segments to reduce the multi-node contention.
Am I thinking through this correctly?
If I haven't provided enough info please ask for it.
Thanks,
Jed
--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l
Received on Fri Sep 06 2013 - 18:21:42 CEST