RE: Insert contention on RAC

If you can figure out a way to choose the table name to insert into from the instance you have happened to connect to for a particular session (and possibly more conditions like modolo on session number if you need to fan out wider than the number of instances you have), then you can have a table of tables for each instance, avoiding all the gc stuff, and then do a Gorman with the tables after the load is complete.

(A Gorman is where you totally avoid contention with an existing big partitioned table being affected by dataloads by doing a partition exchange after the data is loaded. So this is a modified Gorman, in that you'll be exchanging in the n tables to something that was formerly empty.)

(If Tim doesn't like me calling that a Gorman, I'll withdraw it.)

Finally, since you've added the information that you start empty each time and create the indexes after the load, I *suspect* that you simply do not use that data for anything until the load is complete. If that is true you might be better off having each insert session create a sequence generator named table for its inserts, insert the table name in a job control list, and then build a little utility to automate doing the Gorman when all the sessions are complete (or copy append them all to the destination if you don't have partitioning). If this is followed up with postprocessing jobs, knowing the affinity between partitions and instances *might* facilitate logically parallel processing (as opposed to parallel degree) on each partition separately with essentially no gc traffic.

mwf

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Tim Gorman
Sent: Wednesday, June 25, 2008 5:45 PM
To: mwf_at_rsiz.com
Cc: paul.baumgartel_at_credit-suisse.com; oracle-l_at_freelists.org Subject: RE: Insert contention on RAC

Dang Mark! We're going to have to rename you "MacGyver"..... ;-)

Quoting "Mark W. Farnham" <mwf_at_rsiz.com>:

> You can decouple from the time of the run by inflating a table of a
> different name and using rename. You can mess with pctfree and pctused so
> you can inflate the table with one row per block, alter pctfree and
pctused,
> and then run the delete.
>
> If the target table has its own tablespace you can drop the tablespace and
> re-transport in the tablespace with the pre-inflated empty table.

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







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




application/ms-tnef attachment: winmail.dat