Re: Huge INSERT and unfriendly ROLLBACK

> I'd like to get some opinions from people about a problem already raised in
> this group, though, with a dissatisfying answer.
>
> I run a huge INSERT, approx. 500,000 records. The reason I do it is the speed
> of the operation. If I use PL/SQL with commits after each 10,000 records, as
> was suggested here, it takes ages.
>
> Now in more than 60% of cases, transactions just hang, doing some "db
> sequential reads" or giving me the "unable to extend rollback segment" error.
> My RBS is 140M and if I look at all of the segments they rarely extend 40M.
>
> So what might be the solution?

If you can do this with no users on-line here's what I'd do.

First create a very large rollback tablespace with one very large rollback segment with very large extents.
Second take all the other rollback segments off-line Third drop all indices.
Run the load procedure.
Rebuild the indices.
Take the large rollback segment off-line and put your regular rollback segments back on-line.

One thing that can kill performance in this situation as fast as anything is extent sizes that are too small. If the table you are loading has more than 20 extents you have a major performance killer and need to rebuild the table from scratch.

Daniel A. Morgan Received on Wed Nov 11 1998 - 00:00:00 CST