Re: RAC partitioning idea bounce

From: goran bogdanovic <goran00_at_gmail.com>
Date: Fri, 22 Nov 2013 19:05:40 +0100
Message-ID: <CAGyPXK6ZTcnoGi6dj15r_c7RbsZD=Be0U2VO2oFzW+Wdwkb1-A_at_mail.gmail.com>

Contention can be also as of low cardinality of affected indexed columns. If possible, modify sql's to access local hash partitions. Hth,
goran
On Sep 6, 2013 5:57 PM, "Walker, Jed S" <Jed_Walker_at_cable.comcast.com> wrote:

> 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 Nov 22 2013 - 19:05:40 CET