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

2 comments on this post.
  1. Kevin Mede:

    I am confused by your use of the term UN-MIGRATED. To my knowledge, there is no such thing as an un-migrate event. Please explain what you mean.

    Are you suggesting that with each update of a migrated row, Oracle checks to see if it could fit in the prior owning block? I could believe that an update to a migrated row might cause it to migrate again, and that by co-incidence a prior owning block might receive this row, at which point the receiving block knowing that it is receiving a migrated row might check the migration chain to see if it already owns a pointer to this row and if so clean up the chain. Is that what happened? Or did Oracle actively check to see if migrated rows being updated could be sent back up their migration chain? If so, does this happen for non-compressed tables? I have never heard of it.

    Kevin

  2. Alberto Dell'Era:

    Hi Kevin,

    a row can be “unmigrated”, the most effective demo I know is this one:

    https://jonathanlewis.wordpress.com/2014/02/10/row-migration/

    interestingly, Jonathan shows that the “unmigrating trigger” is the row needing to be migrated again, at which point Oracle tries, first thing, to migrate it back where the row header is (and that makes perfect sense, since when updating, the row header is locked and its block is [should be?] pinned, so the overhead of checking its free space is almost immaterial). Note that the row must grow enough to be forced to migrate again to be considered for “unmigration”.

    In my test case I haven’t changed the row size at all (I have switched the letter case only, so the size remains exactly the same); I don’t know what triggers this – possibly, as you say, it’s an optimization for compressed tables only, but that should be investigated properly (I might do it when I have time).

    The scope of the post, however, was to investigate whether compression is triggered or not – and it looks rather strange to me that migrated rows gets compressed but “unmigrated” ones not.

Leave a comment