RE: RAC partitioning idea bounce

  • From: "Mark W. Farnham" <mwf@xxxxxxxx>
  • To: <oratune@xxxxxxxxx>, <Jed_Walker@xxxxxxxxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
  • Date: Fri, 6 Sep 2013 12:30:14 -0400

First, see Tim Gorman's various descriptions of "Scaling to Infinity" to see
if that method is a fit. IF it is a fit for this staging, that's probably
the way to do it.

Second, David's issue of block mastering is real.

Third, instead of a hash, why not use instance_id for secondary
partitioning. You would have to have a way to give a parameter for the
instance id instead of the real current instance id in clean-up mode if some
particular instance with a partially processed batch is down (and will
continue to be down for too long).

This way, except in the broken instance case, you won't have dynamic block
re-mastering and the partitioning matches both the current requirement for
the processing job AND instance affinity. (You might experiment with having
the partitions in tablespaces organized by instance to enhance the lessening
of opportunity for block remastering. That may be over engineering.)

Someone's probably got a really slick way to do this that I haven't thought
of, and maybe the "Scaling to Infinity" partitioning swapping deal is
useful.

mwf

-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of David Fitzjarrell
Sent: Friday, September 06, 2013 12:09 PM
To: Jed_Walker@xxxxxxxxxxxxxxxxx; oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxxxxxxxx>
To: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx> 
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


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

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


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


Other related posts: