Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Huge INSERT and unfriendly ROLLBACK
I'm actually moving data from one part of the database to another, that's why SQL*Loader won't help.
I run the transcations on Sun 20, Oracle 7.2.3
The mystery is:
Table1 (220M, 500.000 records of character and numeric data) - 1 hour 5 min. Table2 (120M, 300.000 records ) - hangs
All indexes, used in the transaction are of the same proportions, explain plans are the same, table structures, both source and destination are the same.
The second query execution ends up in creation of a 90M TEMPORARY segment in temporary tablespace of 43 extents with endless boring reading of the source table after it (as of V$SESSION_WAIT).
I have no idea what's going on and how can be the data so different that it really becomes a tragedy for Oracle.
Second part of transaction, also involving data migration, frequently ends up with a rollback error, though I really doubt that a simple INSERT SELECT transaction of 120M may cause rollback overflow of 140M rollback segment with no other jobs in progress.
> prochak_at_my-dejanews.com wrote:
> buzkoff_at_my-dejanews.com wrote:
> > 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?
> >
>
> What format is your data in that you need to do 500,000 records at a time?
> You can try running the 10,000 record batches in parallel, which may help
> if you have the CPU power for it. Another solution is to bypass the rollback
> segments using the SQL*LOADER DIRECT option. BUT BE CAREFUL! I don't recall
> the version that fixes it but there is a bug which can corrupt your data
> if the load is interrupted. (It was posted in this newsgroup within the
> last week or so.)
>
> You have 500,000 records. What's the average size of the records? There
> is overhead in oracle so if the data bulk is close to your RBS limit,
> then oops. Also are there other transactions happening at the same time?
> They can consume rollback space too.
>
> What timeframe are we talking about? How long is too long ("it took ages")?
>
> SQL*LOADER Direct is the only alternative I can suggest. But if your
> data is coming from a PL/SQL procedure, it may not be an option for you.
>
> --
> Ed Prochak
> Magic Interface, Ltd.
> 440-498-3702
>
-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Tue Nov 10 1998 - 00:00:00 CST