OLTP compression: migrated rows are compressed

After the update, in our block the token is gone; the first 5 rows were uncompressed but were kept in the block since they fit in the free space:

tab 1, row 0, @0xc5c

tl: 407 fb: --H-FL-- lb: 0x2 cc: 1

col 0: [400] {repeat 61, 400 times}

bindmp: 2c 02 01 00 fa 01 90 {repeat 61, 400 times}

but , indeed, all the other 132 (=137-5) ones were migrated:

tab 1, row 5, @0x5f7

tl: 9 fb: --H----- lb: 0x2 cc: 0

nrid: 0x04000148.0

bindmp: 20 02 00 04 00 01 48 00 00

The flag byte “–H—–” means “this is just the row header” (check this great note by Steve Adams) and nrid is the “forwarding address” rowid we spoke about previously.

Now, the interesting part – migrated rows got compressed.

Indeed, walking down the blocks containing the migrated rows, we see

a) token 0, holding 400 bytes set to 0×61 (same as above, not shown)

b) a certain number (usually a dozen) of compressed rows:

tab 1, row 0, @0xce0

tl: 11 fb: ----FL-- lb: 0x1 cc: 1

hrid: 0x04000101.5

col 0: [400] {repeat 61, 400 times}

bindmp: 0c 01 01 04 00 01 01 00 05 01 00

note that the row is migrated: the flag byte “—-FL–” means “this is the First and Last row piece, the Header is not here” and hrid is the pointer to the header (i.e. the original row position). Of course, note that the row is compressed: the bindmp shows the usual row header triplet “0c 01 01″, then the hrid “04 00 01 01 00 05″ and then the usual “01″ meaning “1 token follows” plus “00″, the pointer to token zero.

c) uno or two uncompressed rows:

tab 1, row 10, @0xae0

tl: 413 fb: ----FL-- lb: 0x1 cc: 1

hrid: 0x04000101.f

col 0: [400] {repeat 61, 400 times}

bindmp: 0c 01 01 04 00 01 01 00 0f 00 fa 01 90 {repeat 61, 400 times}

note the bindmp, and the flag byte telling us, again, that this row is indeed migrated.

In addition, tosp is usually set to 0×907 (2311 bytes), about half of the block, honoring the PCTFREE setting to 50%.

This layout is typical of a series of row insertions in a empty block: the first inserts get simply stored uncompressed, until one that would make the used space cross the pctfree threshold triggers a potential compression before being inserted uncompressed. Note the order (already noted by Jonathan Lewis): first the compression is potentially attempted, then the row is stored – hence at least one uncompressed row is always present. There can be more because the compression is not always attempted, depending, possibly, on some cost consideration by the kernel. Check thr.sql and thr.trc if interested.

uncompressed un-migration

As a side investigation, I have also checked what happens when a row is “un-migrated”; I was expecting that un-migration could trigger a compression, but this does not seem to be the case, at least in my scenario.

I have prepared the first block (check the last part of main.trc) with only 12 migrated rows (leaving a very big free space of tosp=0xde6=3558 bytes), and then I have updated them to the original value in uppercase using “update t set x = upper(x)” . These rows, being identical, could be comfortably be un-migrated and compressed all together in the original block (just remember that we observed a block with 137 such rows at the start of the main investigation), but that has not happened. Instead, the first 8 rows have been un-migrated indeed but left uncompressed, and the last 4 has been simply left migrated; also, the block free space has dropped to a mere tosp=0×176=374 bytes, thus severely hitting the PCTFREE reserve.

Un-migration is a potential source of table size increase and/or pctfree space consumption, if this happens all the times in all scenarios – but I have not checked this extensively, though.

Page 2 of 2 | Previous page

Leave a comment