| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Performance issue while loading large amount of data
"Howard J. Rogers" <howardjr2000_at_yahoo.com.au> wrote in message
news:EzZU9.24111$jM5.64133_at_newsfeeds.bigpond.com...
<big almighty snip>
> Time to start block dumping, I think.
Hi Guys,
OK, kids are in bed, time to do some investigating (and starting by looking at a few block dumps is a good way to go).
As I said somewhere previously, inserts most certainly do generate undo, they must for rollback, read consistency reasons, etc. pretty well as Howard has said. I hopefully will prove that in a minute. The only thing I disagree with what Howard has said/show, to a degree, is what is actually stored in the undo blocks and the amount of undo that actually gets generated. Hopefully I'll also prove that an insert takes somewhere in the order of 70 odd bytes or so and that it is not the rowid that gets stored (as such).
OK, word of warning to everyone, this might be a tad long so if none of this is of interest or block dumps bore you to tears, exit now !! But if you're a newbie to Oracle and you're interested in how people might determine what the hell Oracle does, then hey hang in there, this might useful to you.
Couple of birds with one stone here. Firstly, an insert does generate undo, each and every one of them and the amount of undo is proportional to the number of rows inserted as Howard states. However, the *issue* I have with the examples in this discussion and the reason why it generates so little undo per row is that they all use a subselect. By doing so, Oracle is using an *array insert* mechanism and as such it dramatically reduces the amount of undo that gets generated as multiple rows being inserted into the same block can be recorded very efficiently within the *one* undo record.
Lets take a look at what I mean.
SQL> create table insert_bowie as select * from dba_objects where 1=2;
Table created.
SQL> insert into insert_bowie select * from dba_objects;
30127 rows created.
SQL> select start_ubafil, ubafil, start_ubablk, ubablk, used_ublk, used_urec 2 from v$transaction;
START_UBAFIL UBAFIL START_UBABLK UBABLK USED_UBLK USED_UREC ------------ ---------- ------------ ---------- ---------- ----------
2 2 1330 1345 15 760
We note that 30127 rows were inserted, using just 15 * 8K blocks and interestingly just 760 undo records. That's no more than 4 bytes of undo per inserted row. How the hell does Oracle use so little undo ?
We'll lets look at one of these undo blocks (in other session) ...
SQL> alter system dump datafile 2 block 1335;
System altered.
I've dumped one of the blocks listed above and this is a small portion of it. Comments with ** is just me trying to explain stuff :)
Start dump data blocks tsn: 1 file#: 2 minblk 1335 maxblk 1335
buffer tsn: 1 rdba: 0x00800537 (2/1335)
scn: 0x0000.016aa6e3 seq: 0x19 flg: 0x04 tail: 0xa6e30219
frmt: 0x02 chkval: 0x9aa2 type: 0x02=KTU UNDO BLOCK
Rec Offset Rec Offset Rec Offset Rec Offset Rec Offset
0x01 0x1f34 0x02 0x1ea8 0x03 0x1e0c 0x04 0x1d74 0x05 0x1cf8 0x06 0x1c38 0x07 0x1bd0 0x08 0x1af4 0x09 0x1aa4 0x0a 0x19c0 0x0b 0x1978 0x0c 0x1898 0x0d 0x1834 0x0e 0x1770 0x0f 0x16f0 0x10 0x1648 0x11 0x15b0 0x12 0x1524 0x13 0x1470 0x14 0x13fc 0x15 0x1330 0x16 0x12d8 0x17 0x1204 0x18 0x11c8 0x19 0x10f0 0x1a 0x109c 0x1b 0x0fd4 0x1c 0x0f68 0x1d 0x0eb4 0x1e 0x0e2c 0x1f 0x0d8c 0x20 0x0ce8 0x21 0x0c60 0x22 0x0ba0 0x23 0x0b34 0x24 0x0a64 0x25 0x0a14 0x26 0x093c 0x27 0x08f4 0x28 0x0818 0x29 0x07bc 0x2a 0x06f8 0x2b 0x0684 0x2c 0x05d0 0x2d 0x0540 0x2e 0x04ac 0x2f 0x0404 0x30 0x0388 0x31 0x02c8 0x32 0x0264 0x33 0x0184 0x34 0x0138
*-----------------------------
*-----------------------------
slot[0]: 0 slot[1]: 1 slot[2]: 2 slot[3]: 3 slot[4]: 4 slot[5]: 5 slot[6]: 6 slot[7]: 7 slot[8]: 8 slot[9]: 9 slot[10]: 10 slot[11]: 11 slot[12]: 12 slot[13]: 13 slot[14]: 14 slot[15]: 15 slot[16]: 16 slot[17]: 17 slot[18]: 18 slot[19]: 19 slot[20]: 20 slot[21]: 21 slot[22]: 22 slot[23]: 23 slot[24]: 24 slot[25]: 25 slot[26]: 26 slot[27]: 27 slot[28]: 28 slot[29]: 29 slot[30]: 30 slot[31]: 31 slot[32]: 32 slot[33]: 33 slot[34]: 34 slot[35]: 35 slot[36]: 36 slot[37]: 37 slot[38]: 38 slot[39]: 39
That's a key point I'm trying to illustrate.
*-----------------------------
OK, while I've got your attention (if you're not asleep already), I might show you another interesting example. This time let's see what difference an index makes (because it does make more of a difference than some might expect)
Rollback complete.
SQL> create index insert_bowie_idx on insert_bowie(owner);
Index created.
SQL> insert into insert_bowie select * from dba_objects;
30128 rows created.
SQL> select start_ubafil, ubafil, start_ubablk, ubablk, used_ublk, used_urec 2 from v$transaction;
START_UBAFIL UBAFIL START_UBABLK UBABLK USED_UBLK USED_UREC ------------ ---------- ------------ ---------- ---------- ----------
2 2 445 2883 80 1449
Note that by simply adding one little weeny index which we might suspect will only need roughly double the undo to store has actually generated 433% more undo. Why ? Let's look ...
SQL> alter system dump datafile 2 block 446;
System altered.
*-----------------------------
*-----------------------------
*-----------------------------
*-----------------------------
slot[0]: 20 slot[1]: 21 slot[2]: 22 slot[3]: 23 slot[4]: 24 slot[5]: 25 slot[6]: 26 slot[7]: 27 slot[8]: 28 slot[9]: 29 slot[10]: 30 slot[11]: 31 slot[12]: 32 slot[13]: 33 slot[14]: 34 slot[15]: 35 slot[16]: 36 slot[17]: 37 slot[18]: 38 slot[19]: 39 slot[20]: 40 slot[21]: 41 slot[22]: 42 slot[23]: 43 slot[24]: 44 slot[25]: 45 slot[26]: 46 slot[27]: 47 slot[28]: 48 slot[29]: 49 slot[30]: 50 slot[31]: 51 slot[32]: 52 slot[33]: 53 slot[34]: 54 slot[35]: 55 slot[36]: 56 slot[37]: 57 slot[38]: 58 slot[39]: 59 slot[40]: 60 slot[41]: 61 slot[42]: 62 slot[43]: 63 slot[44]: 64 slot[45]: 65 slot[46]: 66 slot[47]: 67 slot[48]: 68
I've listed 2 undo records here, one for the index and one for the table. As you can see, there's a fair amount of undo that needs to be recorded for an index. Note also that indexes have branch pages, leaf pages that split etc. and all this needs to be recorded.
Certainly a table that's inserted via an array insert with no indexes generates proportionally much less undo than one that does have an index (or more).
OK, now lets see how thing differ when we don't use an insert array mechanism. Hopefully, we'll see that proportionally the amount of undo that's generated sky rockets.
SQL> rollback;
Rollback complete.
SQL> create table insert_bowie_2 (x number);
Table created.
SQL> insert into insert_bowie_2 values (1);
1 row created.
SQL> / etc...
I now carry on and insert 340 rows into this table.
SQL> select count(*) from insert_bowie_2;
COUNT(*)
340
SQL> select start_ubafil, ubafil, start_ubablk, ubablk, used_ublk, used_urec 2 from v$transaction;
START_UBAFIL UBAFIL START_UBABLK UBABLK USED_UBLK USED_UREC ------------ ---------- ------------ ---------- ---------- ----------
2 2 316 318 3 340
Now this is interesting. With 340 rows, we note that *3* undo blocks are required (assuming the 3rd block is full, that's approximately *70* bytes per row. Also note that the undo record count matches the number of rows *exactly*. I'm not too happy with not knowing the fullness of the last block as it kinda makes the average undo per row too variable for my liking.
So lets keep adding 10 more rows undo we hit 4 undo blocks ...
SQL> insert into insert_bowie_2 values (1);
1 row created.
etc.
SQL> select start_ubafil, ubafil, start_ubablk, ubablk, used_ublk, used_urec 2 from v$transaction;
START_UBAFIL UBAFIL START_UBABLK UBABLK USED_UBLK USED_UREC ------------ ---------- ------------ ---------- ---------- ----------
2 2 316 319 4 350
Alright !! Now with 350 rows (and corresponding undo records) we now have 3 totally full undo blocks and one almost empty. So we can say that approximately 70 bytes per row is required here for each insert row (the block dump will confirm this is as pretty close to the truth). This is way way way more than the 4 bytes average we originally had !!
The reason should of course be obvious but let's look anyway.
SQL> alter system dump datafile 2 block 317;
System altered.
*-----------------------------
*-----------------------------
*-----------------------------
*-----------------------------
*-----------------------------
*-----------------------------
I've only listed 3 undo records but as you can see each one corresponds to only the one row as we are not using an array insert in this example. This means that the large undo record header section is duplicated for each and every entry resulting in a vastly increased undo rate per row.
So there we go.
Hopefully this shows that:
Sorry if I've bored you all to tears but I hope some of this was useful to you and any newbies out there.
Cheers
Richard Received on Wed Jan 15 2003 - 07:29:08 CST
 |
 |