Oracle Scratchpad

February 10, 2014

Row Migration

Filed under: Infrastructure,Oracle,Troubleshooting — Jonathan Lewis @ 6:55 pm BST Feb 10,2014

At one of the presentations I attended at RMOUG this year the presenter claimed that if a row kept increasing in size and had to migrate from block to block as a consequence then each migration of that row would leave a pointer in the previous block so that an indexed access to the row would start at the original table block and have to follow an ever growing chain of pointers to reach the data.

This is not correct, and it’s worth making a little fuss about the error since it’s the sort of thing that can easily become an urban legend that results in people rebuilding tables “for performance” when they don’t need to.

Oracle behaves quite intelligently with migrated rows. First, the migrated row has a pointer back to the original location and if the row has to migrate a second time the first place that Oracle checks for space is the original block, so the row might “de-migrate” itself; however, even if it can’t migrate back to the original block, it will still revisit the original block to change the pointer in that block to refer to the block it has moved on to – so the row is never more than one step away from its original location. As a quick demonstration, here’s some code to generate and manipulate some data:

create table t1 (
id	number(6,0),
v1	varchar2(1200)
)
pctfree 0
;

prompt	==========================================
prompt	The following code fits 74 rows to a block
prompt	==========================================

insert into t1 select rownum - 1, rpad('x',100) from all_objects where rownum <= 75;
commit;

prompt	======================================
prompt	Make the first row migrate and dump it
prompt	======================================

update t1 set v1 = rpad('x',400) where id = 0;
commit;

alter system flush buffer_cache;
execute dump_seg('t1',2)

prompt	===========================================================
prompt	Fill the block the long row is now in, force it to migrate,
prompt	then dump it again.
prompt	===========================================================

insert into t1 select rownum + 75, rpad('x',100) from all_objects where rownum <= 75;
commit;

update t1 set v1 = rpad('x',800) where id = 0;
commit;

alter system flush buffer_cache;
execute dump_seg('t1',3)

prompt	========================================================
prompt	Fill the block the long row is now in and shrink the row
prompt	to see if it returns to its original block. (It won't.)
prompt	========================================================

insert into t1 select rownum + 150, rpad('x',100) from all_objects where rownum <= 75;
commit;

update t1 set v1 = rpad('x',50) where id = 0;
commit;

alter system flush buffer_cache;
execute dump_seg('t1',3)

prompt	========================================================
prompt	Make a lot of space in the first block and force the row
prompt	to migrate again to see if it migrates back. (It does.)
prompt	========================================================

delete from t1 where id between 1 and 20;
commit;

update t1 set v1 = rpad('x',1200) where id = 0;
commit;

alter system flush buffer_cache;
execute dump_seg('t1',3)

My test database was using 8KB blocks (hence the 74 rows per block), and 1MB uniform extents with freelist management. The procedure dump_seg() takes a segment name as its first parameter and a number of blocks as the second (then the segment type and starting block as the third and fourth) and dumps the first N data blocks of the segment. To demonstrate what goes on, I’ve extracted the content of the first row (id = 0) after each of the four dumps:

After the first update - the column count (cc) is zero and the "next rowid" (nrid) is row 1 of block 0x0140000b

tab 0, row 0, @0xb3
tl: 9 fb: --H----- lb: 0x2  cc: 0
nrid:  0x0140000b.1

After the second update - the next rowid is row 7 of block 0x0140000c

tab 0, row 0, @0xb3
tl: 9 fb: --H----- lb: 0x1  cc: 0
nrid:  0x0140000c.7

After the third update (shrinking the row) the row hasn't moved from block 0x0140000c

tab 0, row 0, @0xb3
tl: 9 fb: --H----- lb: 0x2  cc: 0
nrid:  0x0140000c.7

After the fourth update (making space, and growing the row too much) the row moves back home

tab 0, row 0, @0x4c1
tl: 1208 fb: --H-FL-- lb: 0x2  cc: 2
col  0: [ 1]  80
col  1: [1200]
78 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20

My calls to dump blocks included the blocks where the row migrated to, so we’ll have a look at the target locations (as given by the original row location’s nrid) in those blocks over time. First we check block 0x0140000b, row 1 after the first two migrations:

tab 0, row 1, @0x1d7f
tl: 414 fb: ----FL-- lb: 0x2  cc: 2
hrid: 0x0140000a.0
col  0: [ 1]  80
col  1: [400]

tab 0, row 1, @0x1d7f
tl: 2 fb: ---DFL-- lb: 0x1

After the first migration (the row arrives here) we have a “head rowid” (hrid) pointer telling us where the row came from. After the second migration, when the row has moved on, we simply have a typical “deleted stub” – two bytes reserving the row directory entry until the commit has been done and cleaned out.

Then we can examine the second target (0x140000c, row 7) on the second and third and fourth updates:


tab 0, row 7, @0x1966
tl: 814 fb: ----FL-- lb: 0x2  cc: 2
hrid: 0x0140000a.0
col  0: [ 1]  80
col  1: [800]

tab 0, row 7, @0xb1
tl: 62 fb: ----FL-- lb: 0x1  cc: 2
hrid: 0x0140000a.0
col  0: [ 1]  80
col  1: [50]

tab 0, row 7, @0xb1
tl: 2 fb: ---DFL-- lb: 0x2

As you can see, on arrival this location gets the original rowid as its “head rowid” (hrid), and it knows nothing about the intermediate block where the row was briefly in transit. I’ve copied the length byte (in square brackets) of column 1 in the dumps so that you can see that the row stayed put as it shrank. We can then see on the last update that we are left with a deleted stub in this block as the row migrates back to its original location when we try to extend it beyond the free space in this block.

Migrated rows are only ever one step away from home. It’s not nice to have too many of them, but it’s not necessarily a disaster.

7 Comments »

  1. Migrated rows are causing still too many ORA-600 and unpredictable behavior. Maybe that’s more motivating point to get rid of them as any “legendary” performance degradation

    Comment by Pavol Babel — February 10, 2014 @ 9:59 pm BST Feb 10,2014 | Reply

    • Pavol,

      I don’t think I’ve come across any ORA-00600′s due to migrated rows – but possibly that’s simply a case of not working regularly in an environment that makes them more likely. I could imagine that things like CDC, materialized views, or active standby (for example) would be more likely to hit unexpected side effects than some other features.

      Do you have any indications of a particular mix of features where migration becomes a significant contributor of ORA-00600′s ? (I haven’t tried a generic search on MoS because the number of bugs you find under ANY heading is too worrying.)

      Comment by Jonathan Lewis — February 11, 2014 @ 9:58 am BST Feb 11,2014 | Reply

      • I have hit few of them recently, all SRs closed as “not a bug” since we were not able to reproduce issue. alter table move made it always…

        Comment by Pavol Babel — February 12, 2014 @ 7:12 pm BST Feb 12,2014 | Reply

      • Well, I remember hitting an ORA-600 bug way back on 7.3.4, involving a corner case with migrated rows, after I’d added a large VARCHAR2 column that nearly doubled the row sizes.

        I’ve managed to avoid similar problems since, but I remember thinking at the time, “Boy, Oracle did a good job checking for this corner case when inserting a new row into a block, but if it’s a migrated row from another block, they never bothered to check for that possibility here.”

        Oh, I think I might remember what it was… if it couldn’t get an ITL entry to insert a row, for a normal insert it does something sensible (going to another block IIRC), but if it’s migrating a row and it couldn’t get an ITL entry in the chosen destination, it would ORA-600. Or some similar scenario like that.

        Whatever it was was fixed in 8, and I had no hope of getting a patch out of them for 7.3.4, so I had to reorg to fix it (which I wanted to do anyway once I realized what an unholy mess of migrated rows I’d created). But that there was a fixed bug on it implies that I wasn’t the only one having trouble with migrated rows, that Oracle didn’t initially catch.

        But anyway, while I only have modest (and very old) corroboration from my own experience, it doesn’t surprise me in the least that migrated rows could still be, how do you say, dodgy…

        Comment by Jason Bucata — February 12, 2014 @ 11:17 pm BST Feb 12,2014 | Reply

        • Jason,
          A variant of your bug still existed long after 8i – thanks, in particular, to the arrival of ASSM. I wouldn’t be surprised if some of Pavol’s ORA-00600′s relate to that sort of area. Remember this puzzle from 5 years ago.

          Comment by Jonathan Lewis — February 13, 2014 @ 8:28 am BST Feb 13,2014

  2. If your only tool is a pointer, everything looks like a chain.

    Comment by jgarry — February 11, 2014 @ 12:30 am BST Feb 11,2014 | Reply

  3. Thanks for nipping a potential myth in the bud.

    Comment by Jared — February 13, 2014 @ 1:39 am BST Feb 13,2014 | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Rubric Theme Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 3,453 other followers