Re: Performance issue while loading large amount of data

"Howard J. Rogers" <howardjr2000_at_yahoo.com.au> wrote in news:29pU9.22722$jM5.60831_at_newsfeeds.bigpond.com and I quote:

>
> Bear in mind that SQL Loader can/does do array inserts with an implied
> commit after each array is inserted. Likewise the 'rows' parameter could
> be specified, meaning that a commit is issued after the given number of
> rows is inserted in conventional mode. Which means that the amount of
> *outstanding* (ie, un-over-writeable) undo might well be quite small.
> But nevertheless quite a large amount of undo *in total* would be
> generated.

Ooops sorry, I should have been more clear. No SQL Loader was used for V7. Tables were loaded using PL/SQL and SQL. From intermediate "data cleansing" tables. With v8 it was SQL*Loader alright.

>
> So a single insert of just a 4-digit number causes an entire 4K block of
> rollback to be 'used'. And to prove it's this insert doing the 'using':

Yeah, but now make 2 more inserts in that same table without commit. I think you'll find it won't create any more undo blocks. Then do sufficient inserts to fill up the first block and extent. Then it will use more undo.

>
> So, yes, the amount of rollback generated is relatively small (in this
> case, 163840/29359 about 6 bytes per row). But it's not nothing.
>

What would those 6 bytes be? I suspect it's not the bytes per row but the bytes per extent/block. IOW, there will be some undo generated for each extent/block created by the INSERT. Most probably to do with dict management ( things like dba_free_space, quotas and such?). Not really proportional to the data being loaded.

Ie, what we are seeing is a side effect of storing rows in blocks, not undo generated as a direct consequence of INSERT.

Of course, this is like saying tomato instead of tomahto! :D Nevertheless, it's still way too small to be any worry.

>
> Ooo-err. Do an append insert, and bugger-all rollback (TM Noons!) is
> generated. Which makes me think that your SQL Loads were indeed using
> the direct load method.
>

yup, the sql loads ended up using direct. But that was because the darn thing finished faster that way. We had a half hour window to load the daily partition with between 10 and 15 million rows. We had to use the go_fast init.ora parameter! ;)

As I said: undo space never became an issue at all with bulk inserts. This was with DMT, though. I have never tried repeating this stuff with LMT, but I suspect the results wouldn't be much different?

-- 
Cheers
Nuno Souto
nsouto_at_optusnet.com.au.nospam