Re: Performance issue while loading large amount of data

"Richard Foote" <richard.foote_at_bigpond.com> wrote in message news:<UUdU9.22027$jM5.59228_at_newsfeeds.bigpond.com>...

> Then you must have been focusing too much on your wood carving and not
> enough on the database ;)

Heh! That too. Thank God!!! :D

> I have to admit I have not performed an undo block dump in 9i but an insert
> took somewhere in the order of 80-100 bytes *per undo record* in 8.0

Hmmmm, very strange indeed. That doesn't tally with what I got. Hang on: "per undo record"... What exactly does that mean in terms of inserted rows? Is it one per row inserted? Or one per block written?

>
> The rollback of an insert does not and has not reset the HWM. For the simple
> reason that it can't as other transactions could have got in there, there's
> no way for Oracle to know. Even a failed insert (say due to insufficient
> space) will *not* reset the HWM.

Yup, quite right. Sorry, mixed up on that one.

> >
> > I used to load 10 million rows and hardly use
> > any rollback at all (<10M). That was in 8.0, no LMT.
>
> No way !! Need to see it to believe it (unless it we are talking some form
> of direct load ??)

IIRC, we tested it with/without direct load (APPEND hint) and it still used bugger all rollback. Partitioned table, loading one partition per day. 8.0.5. AFAIK, they still running it.

Same on V7.3, where I used to load 100Gb tables for a big data conversion with bugger rollback use. As soon as we did UPDATE/DELETE of course we had to be careful. But with INSERT, never an issue. Wouldn't use more than one extent in a rollback segment. According to v$rollstat anyways. No direct load.

> > Something like PCTUSED set way high in a highly volatile
> > table or FREELISTS > 1 and all that changes.
>
> Not sure why ?
>

If a half-full (half-empty?) block gets new rows added to it wouldn't the whole block be sent to redo? Come to think of it, the whole redo keeps changing. Used to be that only entire blocks ended up there.

>
> Share them.
>

Ta. That actually will be of help right now.

Cheers
Nuno Souto
nsouto_at_optushome.com.au Received on Sun Jan 12 2003 - 17:31:01 CST