Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Performance issue while loading large amount of data

Re: Performance issue while loading large amount of data

From: Noons <nsouto_at_optusnet.com.au.nospam>
Date: 14 Jan 2003 14:05:42 GMT
Message-ID: <Xns93048DC96CDmineminemine@210.49.20.254>


"Howard J. Rogers" <howardjr2000_at_yahoo.com.au> wrote in news:EREU9.23382$jM5.62333_at_newsfeeds.bigpond.com and I quote:

>>
>> What would those 6 bytes be?

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

Sorry, too variant to be the rowid. I mean: does the 6 bytes/row average size stay constant? I do recall Richard suggested around 100 bytes/row. I've just repeated some of your tests on my notebook with 8.1.7.4 and 8K block size and got 12 bytes/row! It's got to be something else other than rowids or else there wouldn't be such a huge variation.

Besides, what would the rowid be in undo for? What exactly would that achieve on a commit or rollback? All that a rollback of an INSERT has to do is poke all the headers of blocks inserted into, back to original. And little else. Other than dict management, which we are abstracting here.

It sure ain't gonna clean out the data section of every block. Look at a rollback from a huge INSERT: it takes no time at all. Versus a rollback from a big UPDATE, which can take a VERY significant amount of time.

It's not like an update either, where there might be a need to multi-version the data: inserted data does not exist for anyone else until transaction commits.

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

no, not at all. I think you're using LMT no? So there would be virtually no space management stuff.

What you'd see is undo images of the changed header portion of any block in which new rows were inserted.

I can't recall exactly what is changed, (and probably it would be wrong now in 9i anyway!) but there is a header change in an empty block when rows are inserted. That is what needs to be in undo. For the remaining inserts in that same block by this transaction there won't be another entry created in undo. All we want is the original header data so that the block can be quickly "rolled back".

Do you think we should dump the data blocks to confirm the data area is not re-set, just the headers? I can probably do that sometime tomorrow in my notebook if you want (too late today, bedtime).

>
> (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!):

I'd say proportional to the *number* of blocks that have received new data. Rather than the total amount of new data inserted.

Try what I said before:
Insert one row.
Check undo use.
Insert another row.
Check undo use, stays constant.
Insert quite a few rows. In my case
I had to insert 40 or so before the
first block of data was full.
used_ublk was *still* the same, 1 block!

Now: in my case,
after inserting all objects I got
used_ublk = 5 (3200 rows from all_objects). For 80 blocks of inserted data.

That to me indicates the determining factor in amount of undo use in an INSERT is not the number of rowids generated. I can't however prove it is the number of blocks inserted into (can't think of an exact way of doing it).

Anyone game to try?
I suppose I could say: take the normal size of a block header(approx 600 bytes?), multiply by 80 blocks of data, then divide by my undo block size (8k) and it is darn near to the 5 undo blocks I got! But I can't get an exact number.

>

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

OK. Undo generated to store a rowid like you suggest is related directly to the INSERT-a-row operation, no?

Undo generated because block headers have changed is a side-effect of the INSERT. One for each block changed, not one for each row inserted. Same for undo generated by dict management (recursive stuff).

>

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

If that. That's nothing, when the partition is nearly 20 Gb in size. 60Mb in 20Gb is not even worth worrying about. Even if you have 10Mb undo segment extent size, that's 6 extents. Piddle stuff. Once again, if the size of undo for INSERT is proportional to number of blocks actually added to table, then this would be highly dependent on block size.

Of course if you want to worry about that, go ahead! Me, I had to worry about these guys wanting to keep 90 days of this 20Gb stuff on-line. But that's another story...

> Slap some indexes on
> before the load, and you can expect that to increase accordingly.

Obviously. I never said INSERT into indexed generates little undo. In fact it won't increase accordingly: it will increase a LOT more!

That is the whole point. Slap an UPDATE on your 20000 odd records and watch the undo use go through the roof, relatively speaking. Slap an index in there and do an INSERT or DELETE and watch the undo usage blow up as well. We are now storing much bigger undo images.

Do just an INSERT without any indexes and it's largely irrelevant compared to any other change operation in that same data.

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

Yes, but very little compared to any other change operation.

-- 
Cheers
Nuno Souto
nsouto_at_optusnet.com.au.nospam
Received on Tue Jan 14 2003 - 08:05:42 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US