Re: Performance issue while loading large amount of data

From: Howard J. Rogers <howardjr2000_at_yahoo.com.au>
Date: Tue, 14 Jan 2003 07:06:37 +1100
Message-ID: <EREU9.23382$jM5.62333@newsfeeds.bigpond.com>

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

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

The rowid of each row being inserted (the "before image" of each new row).

> I suspect it's not the
> bytes per row but the bytes per extent/block.

So, on this theory, would it be fair to say that if I pre-allocated the space (for example, by doing an 'alter table bigone allocate extent' about 20 times), you'd expect zero rollback to be generated?

Because if it *would* be fair to say that, then I'm sorry to disappoint:

SQL> create table bigtwo storage (minextents 20) tablespace users as select * from dba_objects where 1=2; Table created.

SQL> insert into bigtwo select * from dba_objects; 29360 rows created.

SQL> select used_ublk from v$transaction;

USED_UBLK

(same result as before).

>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.

I should perhaps have mentioned that my earlier demo was done using locally managed tablespace. But it makes little difference either way (this next bit was done in an 8i database using a larger block size than my original 9i database):

SQL> select extent_management from dba_tablespaces where tablespace_name='USERS';
EXTENT_MAN

DICTIONARY SQL> create table bigtwo tablespace users as select * from dba_objects where 1=2;
Table created.

SQL> insert into bigtwo select * from dba_objects; 24632 rows created.

SQL> select used_ublk from v$transaction; USED_UBLK

SQL> show parameter db_block_size

NAME                                 TYPE    VALUE
------------------------------------ ------- ------------------------------
db_block_size                        integer 8192

(12 8K blocks for fewer records, instead of 40 4K blocks).

Oh, and the amount of undo generated is most definitely proportional to the number of records inserted (back to the 9i database!):

SQL> truncate table bigtwo;
Table truncated.

SQL> insert into bigtwo select * from dba_objects; 29360 rows created.

SQL> select used_ublk from v$transaction;

USED_UBLK

118

(If one insert creates 40 blocks of rollback, 3 should create 120. 118 is close enough to prove the point).

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

You'll have to explain that one more slowly! What's the difference between an insert and 'storing rows in blocks'? I thought the latter was precisely a definition of what the former did!

> Nevertheless, it's still way too small to be any worry.
>

Er, inserting 10 million rows in a table with no indexes whatsoever is going to generate about 58 MEGAbytes of undo. Slap some indexes on before the load, and you can expect that to increase accordingly. Now, in the days of 200GB hard disks, I suppose 100MB of rollback is peanuts. But not if you created all your rollback segments at, say, 10MB because you thought you were generally an OLTP shop.

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

Nope. See above. No fundamental difference at all: inserts still generate rollback/undo, regardless of version and regardless of the nature of the tablespace, and regardless of whether you are using 9i undo segments or 8i rollback segments.

Regards
HJR Received on Mon Jan 13 2003 - 14:06:37 CST