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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Tim Gorman
Sent: Wednesday, June 25, 2008 5:45 PM
To: mwf@xxxxxxxx
Cc: paul.baumgartel@xxxxxxxxxxxxxxxxx; oracle-l@xxxxxxxxxxxxx
Subject: RE: Insert contention on RAC

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


Quoting "Mark W. Farnham" <mwf@xxxxxxxx>:

> 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

Other related posts: