I'm in shock. You managed to write a perfectly coherent and utterly
informative post with barely a mention of a certain rock star.
It was enlightening, informative, bloody clever, and answered a whole lot of
unresolved questions!
Where are my beta blockers?
Cheers, Richard
HJR
"Richard Foote" <richard.foote_at_bigpond.com> wrote in message
news:RdcV9.24793$jM5.65715_at_newsfeeds.bigpond.com...
> "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
>
> ** The section above is the undo header portion and show us the type of
> block, the scn, the relative database block address and so on. All good
> stuff...
>
>
> ****
> UNDO BLK:
> xid: 0x0001.003.000061ce seq: 0x293 cnt: 0x34 irb: 0x34 icl: 0x0 flg:
> 0x0000
>
> 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
>
> ** The section above is the undo record block address list telling us
where
> to find all the corresponding undo records in this particular undo block.
> I'm just going to list the first undo record ...
>
> *-----------------------------
> * Rec #0x2 slt: 0x03 objn: 31120(0x00007990) objd: 31120 tblspc:
> 9(0x00000009)
> * Layer: 11 (Row) opc: 1 rci 0x01
> Undo type: Regular undo Last buffer split: No
> Temp Object: No
> Tablespace Undo: No
> rdba: 0x00000000
> *-----------------------------
> KDO undo record:
> KTB Redo
> op: 0x03 ver: 0x01
> op: Z
> KDO Op code: QMD row dependencies Disabled
> xtype: XA bdba: 0x0240068d hdba: 0x0240203b
> itli: 1 ispac: 0 maxfr: 4858
> tabn: 0 lock: 0 nrow: 40
>
> ** Above is the undo record header telling us stuff such as what type of
> data the undo record is storing (rows), the object id in question and
> importantly the block address of the data block in question. Note that my
> insert subselect statement inserted a whole heap of rows *into the same
data
> block*. This is important.
>
> 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
>
> ** the above is now actually the undo data itself !! And what do we find ?
> Oracle actually only stores the row directory data for each inserted row
as
> the other data is already known. This means we only need to store a couple
> of bytes for each inserted record. That's how Oracle manages to keep the
> generated undo so small *IF* (big IF) it uses an insert array when
> performing the insert. No rowid as such although admittedly all the
> "portions" of the rowid are recorded somewhere above but not the whole
rowid
> for each and every row.
>
> That's a key point I'm trying to illustrate.
>
> *-----------------------------
> * Rec #0x3 slt: 0x03 objn: 31120(0x00007990) objd: 31120 tblspc:
> 9(0x00000009)
>
> 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.
>
> *-----------------------------
> * Rec #0x1 slt: 0x00 objn: 31121(0x00007991) objd: 31121 tblspc:
> 9(0x00000009)
> * Layer: 10 (Index) opc: 22 rci 0x00
> Undo type: Regular undo Last buffer split: No
> Temp Object: No
> Tablespace Undo: No
> rdba: 0x008001bd
> *-----------------------------
> index undo for leaf key operations
> KTB Redo
> op: 0x02 ver: 0x01
> op: C uba: 0x008001bd.024c.48
> Dump kdilk : itl=2, kdxlkflg=0x21 sdc=1 indexid=0x240069b block=0x0240069c
> purge leaf row
> number of keys: 80
> key sizes:
> 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11
11
> 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11
> 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11
> 11 11 11 11 11
> key :(880):
> 03 53 59 53 06 02 40 20 4a 00 0a 03 53 59 53 06 02 40 20 4a 00 0b 03 53
59
> 53 06 02 40 20 4a 00 0c 03 53 59 53 06 02 40 20 4a 00 0d 03 53 59 53 06
02
> 40 20 4a 00 0e 03 53 59 53 06 02 40 20 4a 00 0f 03 53 59 53 06 02 40 20
4a
> 00 10 03 53 59 53 06 02 40 20 4a 00 11 03 53 59 53 06 02 40 20 4a 00 12
03
> 53 59 53 06 02 40 20 4a 00 13 03 53 59 53 06 02 40 20 4a 00 14 03 53 59
53
> 06 02 40 20 4a 00 15 03 53 59 53 06 02 40 20 4a 00 16 03 53 59 53 06 02
40
> 20 4a 00 17 03 53 59 53 06 02 40 20 4a 00 18 03 53 59 53 06 02 40 20 4a
00
> 19 03 53 59 53 06 02 40 20 4a 00 1a 03 53 59 53 06 02 40 20 4a 00 1b 03
53
> 59 53 06 02 40 20 4a 00 1c 03 53 59 53 06 02 40 20 4a 00 1d 03 53 59 53
06
> 02 40 20 4a 00 1e 03 53 59 53 06 02 40 20 4a 00 1f 03 53 59 53 06 02 40
20
> 4a 00 20 03 53 59 53 06 02 40 20 4a 00 21 03 53 59 53 06 02 40 20 4a 00
22
> 03 53 59 53 06 02 40 20 4a 00 23 03 53 59 53 06 02 40 20 4a 00 24 03 53
59
> 53 06 02 40 20 4a 00 25 03 53 59 53 06 02 40 20 4a 00 26 03 53 59 53 06
02
> 40 20 4a 00 27 03 53 59 53 06 02 40 20 4a 00 28 03 53 59 53 06 02 40 20
4a
> 00 29 03 53 59 53 06 02 40 20 4a 00 2a 03 53 59 53 06 02 40 20 4a 00 2b
03
> 53 59 53 06 02 40 20 4a 00 2c 03 53 59 53 06 02 40 20 4a 00 2d 03 53 59
53
> 06 02 40 20 4a 00 2e 03 53 59 53 06 02 40 20 4a 00 2f 03 53 59 53 06 02
40
> 20 4a 00 30 03 53 59 53 06 02 40 20 4a 00 31 03 53 59 53 06 02 40 20 4a
00
> 32 03 53 59 53 06 02 40 20 4a 00 33 03 53 59 53 06 02 40 20 4a 00 34 03
53
> 59 53 06 02 40 20 4a 00 35 03 53 59 53 06 02 40 20 4a 00 36 03 53 59 53
06
> 02 40 20 4a 00 37 03 53 59 53 06 02 40 20 4a 00 38 03 53 59 53 06 02 40
20
> 4a 00 39 03 53 59 53 06 02 40 20 4a 00 3a 03 53 59 53 06 02 40 20 4a 00
3b
> 03 53 59 53 06 02 40 20 4a 00 3c 03 53 59 53 06 02 40 20 4a 00 3d 03 53
59
> 53 06 02 40 20 4a 00 3e 03 53 59 53 06 02 40 20 4a 00 3f 03 53 59 53 06
02
> 40 20 4a 00 40 03 53 59 53 06 02 40 20 4a 00 41 03 53 59 53 06 02 40 20
4a
> 00 42 03 53 59 53 06 02 40 20 4a 00 43 03 53 59 53 06 02 40 20 4a 00 44
03
> 53 59 53 06 02 40 20 4a 00 45 03 53 59 53 06 02 40 20 4b 00 00 03 53 59
53
> 06 02 40 20 4b 00 01 03 53 59 53 06 02 40 20 4b 00 02 03 53 59 53 06 02
40
> 20 4b 00 03 03 53 59 53 06 02 40 20 4b 00 04 03 53 59 53 06 02 40 20 4b
00
> 05 03 53 59 53 06 02 40 20 4b 00 06 03 53 59 53 06 02 40 20 4b 00 07 03
53
> 59 53 06 02 40 20 4b 00 08 03 53 59 53 06 02 40 20 4b 00 09 03 53 59 53
06
> 02 40 20 4b 00 0a 03 53 59 53 06 02 40 20 4b 00 0b 03 53 59 53 06 02 40
20
> 4b 00 0c 03 53 59 53 06 02 40 20 4b 00 0d 03 53 59 53 06 02 40 20 4b 00
0e
> 03 53 59 53 06 02 40 20 4b 00 0f 03 53 59 53 06 02 40 20 4b 00 10 03 53
59
> 53 06 02 40 20 4b 00 11 03 53 59 53 06 02 40 20 4b 00 12 03 53 59 53 06
02
> 40 20 4b 00 13
> keydata/bitmap: (10): ff ff ff ff ff ff ff ff ff ff
>
> *-----------------------------
> * Rec #0x2 slt: 0x00 objn: 31120(0x00007990) objd: 31120 tblspc:
> 9(0x00000009)
> * Layer: 11 (Row) opc: 1 rci 0x01
> Undo type: Regular undo Last buffer split: No
> Temp Object: No
> Tablespace Undo: No
> rdba: 0x00000000
> *-----------------------------
> KDO undo record:
> KTB Redo
> op: 0x02 ver: 0x01
> op: C uba: 0x008001bd.024c.4a
> KDO Op code: QMD row dependencies Disabled
> xtype: XA bdba: 0x0240204b hdba: 0x0240203b
> itli: 1 ispac: 0 maxfr: 4858
> tabn: 0 lock: 0 nrow: 49
> 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.
>
> *-----------------------------
> * Rec #0x1 slt: 0x2f objn: 31123(0x00007993) objd: 31123 tblspc:
> 9(0x00000009)
> * Layer: 11 (Row) opc: 1 rci 0x00
> Undo type: Regular undo Last buffer split: No
> Temp Object: No
> Tablespace Undo: No
> rdba: 0x0080013c
> *-----------------------------
> KDO undo record:
> KTB Redo
> op: 0x02 ver: 0x01
> op: C uba: 0x0080013c.0213.72
> KDO Op code: DRP row dependencies Disabled
> xtype: XA bdba: 0x024005b0 hdba: 0x024005ab
> itli: 1 ispac: 0 maxfr: 4858
> tabn: 0 slot: 95(0x5f)
>
> *-----------------------------
> * Rec #0x2 slt: 0x2f objn: 31123(0x00007993) objd: 31123 tblspc:
> 9(0x00000009)
> * Layer: 11 (Row) opc: 1 rci 0x01
> Undo type: Regular undo Last buffer split: No
> Temp Object: No
> Tablespace Undo: No
> rdba: 0x00000000
> *-----------------------------
> KDO undo record:
> KTB Redo
> op: 0x02 ver: 0x01
> op: C uba: 0x0080013d.0213.01
> KDO Op code: DRP row dependencies Disabled
> xtype: XA bdba: 0x024005b0 hdba: 0x024005ab
> itli: 1 ispac: 0 maxfr: 4858
> tabn: 0 slot: 96(0x60)
>
> *-----------------------------
> * Rec #0x3 slt: 0x2f objn: 31123(0x00007993) objd: 31123 tblspc:
> 9(0x00000009)
> * Layer: 11 (Row) opc: 1 rci 0x02
> Undo type: Regular undo Last buffer split: No
> Temp Object: No
> Tablespace Undo: No
> rdba: 0x00000000
> *-----------------------------
> KDO undo record:
> KTB Redo
> op: 0x02 ver: 0x01
> op: C uba: 0x0080013d.0213.02
> KDO Op code: DRP row dependencies Disabled
> xtype: XA bdba: 0x024005b0 hdba: 0x024005ab
> itli: 1 ispac: 0 maxfr: 4858
> tabn: 0 slot: 97(0x61)
>
> 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:
>
> 1) Yes undo is generate for each individual row that is inserted
(hopefully
> that settles that debate)
>
> 2) If an insert array is used, then proportionally only a small amount of
> undo is generated as only the row directory slot info is actually recorded
> for each row
>
> 3) If a conventional insert is used, then approximately 68 bytes per
> inserted row is used plus some (which hopefully will explain why sometimes
> much 'more' undo appears to be generated proportionally for inserts).
>
> 4) Indexes use (proportionally) a lot of undo for inserts.
>
> 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 - 17:45:21 CST
