RE: RAC partitioning idea bounce

Hi Jed,

How much of the contention is caused by the table and how much by the UK? If the UK contributes significantly to the problem, try partitioning it /GLOBAL HASH PARTITION comes to mind/.

Iordan Iotzov

Check out SmartSource Xpress, our new iPad app! Follow us on Twitter | Like us on Facebook

-----Original Message-----

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Walker, Jed S Sent: Friday, September 06, 2013 11:56 AM To: oracle-l_at_freelists.org
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

This message and its attachments may contain legally privileged or confidential information. It is intended solely for the named addressee. If you are not the addressee indicated in this message (or responsible for delivery of the message to the addressee), you may not copy or deliver this message or its attachments to anyone. Rather, you should permanently delete this message and its attachments and kindly notify the sender by reply e-mail. Any content of this message and its attachments that does not relate to the official business of News America Incorporated or its subsidiaries must be taken not to have been sent or endorsed by any of them. No warranty is made that the e-mail or attachment(s) are free from computer virus or other defect.

--

http://www.freelists.org/webpage/oracle-l Received on Fri Sep 06 2013 - 19:34:10 CEST