RAC partitioning idea bounce
- From: "Walker, Jed S" <Jed_Walker@xxxxxxxxxxxxxxxxx>
- To: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 6 Sep 2013 15:55:59 +0000
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
