So you've got two list partitions but the data spread is 90/10. Is this
something like Active/Inactive?
When you load new data does it all go into one of the partitions or is
there a similar 90/10 split between partitions.
Part of the strategy of partitioning is to break big jobs into little jobs
- so if you're having problems inserting your 700M rows into a 20B row
table then try to find ways of getting more out of partitions.
a) You plan doesn't show any partition-wise activity. Is it possible to
partition the source tables so that the join and insert can operate in a
partition-wise way? It's possible that this MIGHT reduce the overheads of
index maintenance on the insert (and your post about "load as select" /
"index maintenance" with the monitor plan confirms that it was a direct
path load, with unto (mostly) generated by index maintenance).
b) If you can't optimize the monolithic join, you could create an empty,
unindexed clone of the target table and insert into that with a direct path
insert. Then execute a loop walks the subpartitions in turn, inserting from
the staging table to the target table, committing after each subpartition.
This should avoid the ORA-01628 as you will be rotating around the undo
segments. One other thought about the ORA-01628.
A separate thought about the ORA-01628. There must be something about your
processing in the hours and days preceding the ORA-01628 that results in a
lot of small undo segments being created and then being "robbed" of their
initial extents by the big job. If you can't find out why this happens
then set up a proced which, on a regular basis (e.g. every day) Creates a
new undo tablespace, and switches the instance into the new undo
tablespace, then (after a delay which the database will force) drops the
old undo tablespace. If you can do this in a timely fashion your big job
won't be stealing lots of little extents to grow, it will simply use the
large amount of free space in the new tablespace. (It would be interesting
to see how many extents of what size are in the problem undo segment when
it hits the limit (select blocks, count(*) ....)
Regards
Jonathan Lewis
