Re: Performance issue while loading large amount of data

"Noons" <nsouto_at_optusnet.com.au.nospam> wrote in message news:Xns9301E3662423Cmineminemine_at_210.49.20.254...
> "Howard J. Rogers" <howardjr2000_at_yahoo.com.au> wrote in
> news:XnaU9.21831$jM5.59935_at_newsfeeds.bigpond.com and I quote:
>
> >>
> >
> > Well, it implies getting on for 18 bytes per row, since the rowid of the
> > record about to be inserted is stored in rollback. Small, perhaps. But
not
> > nothing.
> >
>
> Must be fairly new. In most of V8 I never got ANY
> roolback activity with pure INSERT (no indexes,
> vanilla table settings).

Hi Noons

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

As Howard says, inserts most certainly do generate undo and have for a long long time (since I was a baby in nappies).

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

>
>
> >
> > Er, I could be wrong, but see above: there *is* some rollback generated
on
> > the table being inserted into. The rollback generated by an insert is
not
> > just for the recursive stuff.
> >
>
>
> More than willing to believe there is now.
> But for a long time, there wasn't any signfificant
> rollback. All needed to be done to reset the table
> on a failed long INSERT-only xaction was reset
> the hwm. That was bugger all rollback.

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.

>
> 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 ??)

> With LMT, it would be even less I suppose?

A little in that less extent DD might be recorded but only a little.

>
> Of course, as I said: vanilla table INSERT, no indexes.
> Something like PCTUSED set way high in a highly volatile
> table or FREELISTS > 1 and all that changes.

Not sure why ?

>
>
> That reminds me: has anyone ever investigated the impact
> of PCTUSED set way high with multiple FREELISTS? What
> happens when a block becomes free for INSERT as a result
> of the high PCTUSED? Will it use more FREELISTS than
> specified or share them? It was never very clear in the
> doco and I don't think anyone ever bothered to find out
> in detail.

Share them.

Bedtime !!

Richard Received on Sun Jan 12 2003 - 08:34:07 CST