Incidentally, Richard: I meant to say in all seriousness that this was an
exceedingly good post, just like a Mr. Kipling's Cup Cake. And was
appreciated almost as much as one of the said cup cakes.
Now, a while back I wrote in a different context that you shouldn't call
yourself a DBA until you can do a block dump and interpret the results...
and I seem to remember a spate of hilarity arising as a result, mostly
scatalogically inclined, and punning on the word 'dump'.
Amongst other things, your post has reminded me that I was right!
Yet another reason why it was very much appreciated. (;-)
When OCP teaches you this stuff, it might be worth the money. But I won't
hold my breath in the meantime, if that's OK with you!
Regards
HJR
"Howard J. Rogers" <howardjr2000_at_yahoo.com.au> wrote in message
news:GemV9.25009$jM5.66234_at_newsfeeds.bigpond.com...
> 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 Thu Jan 16 2003 - 02:13:00 CST
