Ask Tom 'Chained Rows and Autotrace'

一篇解释的非常好的关于行迁移(row migrated)和行连接(row chained)的文章.

Dear Tom,

I have a table with 25% chained rows.

I am trying to determine the impact of this chaining and rebuild it if needed.

To determine, whether I need to reorg this table I did the following test (Logically it 
seems, what I am doing is correct).

Though the statistics shows there are lot of chained rows, the "table fetch continued 
row" of autotrace doesn't validate it. 

Could you pl, give me an explanation on this behaviour? Am I doing anything wrong here?

Note: Though I didn't choose the "explain" of autotrace, OEM showed that it was doing a 
FTS.

Thanks,

SQL> select count(*) from chained_rows where table_name = 'MYTABLE';

  COUNT(*)
----------
    258683

SQL> select table_name, last_analyzed, avg_row_len, num_rows, chain_cnt from dba_tables 
where owner = 'DB_USER' and table_name = 'MYTABLE'

TABLE_NAME                     LAST_ANALYZED    AVG_ROW_LEN   NUM_ROWS  CHAIN_CNT
------------------------------ ---------------- ----------- ---------- ----------
MYTABLE                        2002-06-25 11:35          35    1061399     277139


SQL> select     a.name, b.value
    from     v$statname a, v$mystat b
    where     a.statistic#=b.statistic#
        and name like 'table%';


NAME                                                                   VALUE
----------------------------------------------------------------- ----------
table scans (short tables)                                                28
table scans (long tables)                                                  7
table scans (rowid ranges)                                                 0
table scans (cache partitions)                                             0
table scans (direct read)                                                  0
table scan rows gotten                                               2329430
table scan blocks gotten                                              104510
table fetch by rowid                                                    1573
table fetch continued row                                                 99


SQL> set autotrace traceonly stat ;
/* there is index only on the CNY# col */
SQL> select /*+ full */ CNY#, PAYMENTDATE  from DB_USER.MYTABLE ;

1064428 rows selected.


Statistics
----------------------------------------------------------
          0  recursive calls
         13  db block gets
      81903  consistent gets
       8377  physical reads
        180  redo size
   56728523  bytes sent via SQL*Net to client
    7877096  bytes received via SQL*Net from client
      70963  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
    1064428  rows processed


SQL> select  a.name, b.value
    from    v$statname a, v$mystat b
    where   a.statistic#=b.statistic#
        and     name like 'table%';

9 rows selected.


Statistics
----------------------------------------------------------
        136  recursive calls
          0  db block gets
         34  consistent gets
          0  physical reads
          0  redo size
       1245  bytes sent via SQL*Net to client
        425  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          6  sorts (memory)
          0  sorts (disk)
         12  rows processed

SQL> set autotrace off
SQL> /

NAME                                                                   VALUE
----------------------------------------------------------------- ----------
table scans (short tables)                                                30
table scans (long tables)                                                  8
table scans (rowid ranges)                                                 0
table scans (cache partitions)                                             0
table scans (direct read)                                                  0
table scan rows gotten                                               3393860
table scan blocks gotten                                              186411
table fetch by rowid                                                    1583
table fetch continued row                                                 99

9 rows selected.

 

and we said...

This is a cool question actually - lots to be learned from this one aobut how the data is 
processed.

I'll guess that 100% of your rows that are chained are actually MIGRATED, which is a 
special kind of "chaining".  (if you have my book, I go into great detail on this -- with 
pictures and everything).

My other guess would be that CNY#, PAYMENTDATE  are near the "front" of your table (they 
come first in the create table statement).

Now lets see why.... We need a little background first.

We will migrate a row when an update to that row would cause it to not fit on the 
block anymore (with all of the other data that exists there currently).  A migration 
means that the entire row will move and we just leave behind the "forwarding address".  
So, the original block just has the rowid of the new block and the entire row is moved.
(行迁移是指当update数据时,原来的行所在的block没有足够的空间容纳新数据,从而整行迁移到新的block,
并且在原来的block留下一个转发地址,这个转发地址指向新行)

Then there is the true "chained row" (well, a migrated row is a specialized case of a 
chained row...)  With a true "chained row", part of the DATA of a row is on one block and 
part of it is on another block.  So, instead of just having a forwarding address on one 
block -- and the data on another (thats a migrated row), we have data on two or more 
blocks....
(行连接是指一行数据分布在两个或以上的block上,前一个block的尾部指向下一个block)

When we FULL SCAN a table, we actually *ignore* the forwarding addresses (the head 
rowpiece we call it for a row).  We know that as we continue the full scan, we'll 
eventually get to that row so we can ignore the forwarding address and just process the 
row when we get there.  Hence, in a full scan migrated rows don't cause us to really do 
any extra work -- they are meaningless.  Oh sure, the forwarding address is consuming a 
couple of bytes on the block -- it is overhead -- but frankly, it is meaningless.
(当进行全表扫描时,我们可以忽略转发地址带来的影响,因为全表扫描会读取所有行,所以没必要根据转发地址去定位被迁移过的行)

When we INDEX READ into a table -- then a migrated row will cause additional IO's.  That 
is because the index will tell us "goto file X, block Y, slot Z to find this row".  But 
when we get there we find a message that says "well, really goto file A, block B, slot C 
to find this row".  We have to do another IO (logical or physical) to find the row.
(通过索引来读取数据时行迁移会导致额外的IO,因为我们要先通过索引定位转发地址,在通过转发地址定位真实数据)

"truly" chained rows affect us differently.  Here, it depends on the data we need.  If we 
had a row with two columns that was spread over two blocks -- the query:

select column1 from t

where column1 is the "first" column -- would not cause any table fetch continued row.  It 
would not actually have to get column2 -- it would not follow the chained row all of the 
way out.  On the other hand, if we ask for 

select column2 from t

then you would in fact see a table fetch continued row.
(行连接的影响要看数据的分布,假如t表有两个列column1和column2分布在两个不同的block上,且column1是t表的第一个列,
这时当我们只查询column1时,行连接不会造成任何影响,但是当我们查询column2时,就会看到关于行连接的统计事件)


This behavior is so predicable that setting up an illustrative example is easy.  That is 
what I'll do here.  I am using an 8k block size so if you use something different, you'll 
have to adjust the column sizes appropriately (or add more columns as the case may be).

ops$tkyte@ORA817DEV.US.ORACLE.COM> create table t ( x int primary key, a char(2000), b 
char(2000), c char(2000), d char(2000), e char(2000) );

Table created.

that is our table.  The char(2000)'s will let us easily cause rows to migrate or chain. 
 I used 5 columns a,b,c,d,e so that the total rowsize can grow to about 10k -- bigger 
then my block, ensuring I can truly chain a row...

ops$tkyte@ORA817DEV.US.ORACLE.COM> insert into t(x) values ( 1 );
1 row created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> insert into t(x) values ( 2 );
1 row created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> insert into t(x) values ( 3 );
1 row created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> commit;
Commit complete.

we don't care about seeing a,b,c,d,e -- just fetching them.  they are really wide so 
we'll surpress their display....

ops$tkyte@ORA817DEV.US.ORACLE.COM> column a noprint
ops$tkyte@ORA817DEV.US.ORACLE.COM> column b noprint
ops$tkyte@ORA817DEV.US.ORACLE.COM> column c noprint
ops$tkyte@ORA817DEV.US.ORACLE.COM> column d noprint
ops$tkyte@ORA817DEV.US.ORACLE.COM> column e noprint

ops$tkyte@ORA817DEV.US.ORACLE.COM> select * from t;

         X
----------
         1
         2
         3

ops$tkyte@ORA817DEV.US.ORACLE.COM> @mystat continued
ops$tkyte@ORA817DEV.US.ORACLE.COM> select a.name, b.value
  2  from v$statname a, v$mystat b
  3  where a.statistic# = b.statistic#
  4  and lower(a.name) like '%' || lower('&1')||'%'
  5  --and b.value > 0
  6  /

NAME                                VALUE
------------------------------ ----------
table fetch continued row               0

Now that is to be expected -- the rows came out in the order we put them in (Oracle 
full scanned this query, it processed the data as it found it).  Also expected is the 
table fetch continued row (TFCR from now on) is zero.  This data is so small right now, 
we know that all three rows fit on a single block.  No chaining.  

Now, lets do some updates in a specific way.  I want to demonstrate the MIGRATION issue 
and how it affects (or rather DOESN'T affect) the full scan:


ops$tkyte@ORA817DEV.US.ORACLE.COM> update t set a = 'x', b = 'x', c = 'x' where x = 3;
1 row updated.

ops$tkyte@ORA817DEV.US.ORACLE.COM> commit;
Commit complete.

ops$tkyte@ORA817DEV.US.ORACLE.COM> update t set a = 'x', b = 'x', c = 'x' where x = 2;
1 row updated.

ops$tkyte@ORA817DEV.US.ORACLE.COM> commit;
Commit complete.

ops$tkyte@ORA817DEV.US.ORACLE.COM> update t set a = 'x', b = 'x', c = 'x' where x = 1;
1 row updated.

ops$tkyte@ORA817DEV.US.ORACLE.COM> commit;
Commit complete.

Note the order of updates -- I did last row first, first row last...

ops$tkyte@ORA817DEV.US.ORACLE.COM> select * from t;

         X
----------
         3
         2
         1

ops$tkyte@ORA817DEV.US.ORACLE.COM> @mystat continued
ops$tkyte@ORA817DEV.US.ORACLE.COM> select a.name, b.value
  2  from v$statname a, v$mystat b
  3  where a.statistic# = b.statistic#
  4  and lower(a.name) like '%' || lower('&1')||'%'
  5  --and b.value > 0
  6  /

NAME                                VALUE
------------------------------ ----------
table fetch continued row               0

Interesting, the rows came out "backwards" now (yet further proof that rows do NOT come 
out in the order of insertion generally -- but thats another question).  That is because 
we updated row 3 first.  It did not have to migrate, but it filled up block 1.  We then 
updated row 2.  It migrated to block 2 -- with row 3 hogging all of the space, it had to. 
 We then updated row 1, it migrated to block 3.  We migrated rows 2 and 1, leaving 3 
where it started (you can analyze the table to confirm this yourself).

So, when Oracle full scanned the table, it found row 3 on block 1 first, row 2 on block 2 
second and row 1 on block 3 third.  It IGNORED the head rowid piece on block 1 for rows 1 
and 2 and just found the rows as it scanned the table.  That is why the TFCR = 0 still.

So, lets see a migrated row affecting the TFCR...

ops$tkyte@ORA817DEV.US.ORACLE.COM> select * from t where x = 3;

         X
----------
         3

ops$tkyte@ORA817DEV.US.ORACLE.COM> @mystat continued
ops$tkyte@ORA817DEV.US.ORACLE.COM> select a.name, b.value
  2  from v$statname a, v$mystat b
  3  where a.statistic# = b.statistic#
  4  and lower(a.name) like '%' || lower('&1')||'%'
  5  --and b.value > 0
  6  /

NAME                                VALUE
------------------------------ ----------
table fetch continued row               0

I'm using RBO and RBO is notorious for being index happy -- hence, this was an index 
range scan / table access by rowid.  We didn't increment the TFCR yet since row 3 isn't 
migrated...

ops$tkyte@ORA817DEV.US.ORACLE.COM> select * from t where x = 1;

         X
----------
         1

ops$tkyte@ORA817DEV.US.ORACLE.COM> @mystat continued
ops$tkyte@ORA817DEV.US.ORACLE.COM> select a.name, b.value
  2  from v$statname a, v$mystat b
  3  where a.statistic# = b.statistic#
  4  and lower(a.name) like '%' || lower('&1')||'%'
  5  --and b.value > 0
  6  /

NAME                                VALUE
------------------------------ ----------
table fetch continued row               1

but row 1 is!  and there it is.  Using the index, we forced a TFCR...

Now, lets see the effect of a CHAINED row:

ops$tkyte@ORA817DEV.US.ORACLE.COM> update t set d = 'x', e = 'x' where x = 3;
1 row updated.

ops$tkyte@ORA817DEV.US.ORACLE.COM> commit;
Commit complete.


row 3 no longer fits on block 1.  With d and e set, the rowsize is about 10k.  It is 
truly chained...

ops$tkyte@ORA817DEV.US.ORACLE.COM> select x, a from t where x = 3;

         X
----------
         3

ops$tkyte@ORA817DEV.US.ORACLE.COM> @mystat continued
ops$tkyte@ORA817DEV.US.ORACLE.COM> select a.name, b.value
  2  from v$statname a, v$mystat b
  3  where a.statistic# = b.statistic#
  4  and lower(a.name) like '%' || lower('&1')||'%'
  5  --and b.value > 0
  6  /

NAME                                VALUE
------------------------------ ----------
table fetch continued row               1

fetch something from the "head" of the table and lo and behold -- it will NOT cause a 
TFCR.  Column A is on the "head rowpiece" block.  No extra IO to get it....


ops$tkyte@ORA817DEV.US.ORACLE.COM> select x, d, e from t where x = 3;

         X
----------
         3

ops$tkyte@ORA817DEV.US.ORACLE.COM> @mystat continued
ops$tkyte@ORA817DEV.US.ORACLE.COM> select a.name, b.value
  2  from v$statname a, v$mystat b
  3  where a.statistic# = b.statistic#
  4  and lower(a.name) like '%' || lower('&1')||'%'
  5  --and b.value > 0
  6  /

NAME                                VALUE
------------------------------ ----------
table fetch continued row               2
(注意这里的VALUE为2,并不是指这一次产生了2个TFCR,实际上是这次产生了1个,然后加上上次的1个,
因为v$mystat收集的是当前session的 *统计* 信息)

But going after columns D and E via the index do increment the TFCR.  We had to put the 
row back together from its head to its tail to get that data.

Interestingly the full scan is now affected as well:

ops$tkyte@ORA817DEV.US.ORACLE.COM> select * from t;

         X
----------
         3
         2
         1

ops$tkyte@ORA817DEV.US.ORACLE.COM> @mystat continued
ops$tkyte@ORA817DEV.US.ORACLE.COM> select a.name, b.value
  2  from v$statname a, v$mystat b
  3  where a.statistic# = b.statistic#
  4  and lower(a.name) like '%' || lower('&1')||'%'
  5  --and b.value > 0
  6  /

NAME                                VALUE
------------------------------ ----------
table fetch continued row               3

the TFCR was incremented here because of row 3 -- we had to assemble it in its entirety 
to get the trailing columns.  Rows 1 and 2, even though they are "chained" -- migrated 
really -- don't add to the TFCR since we full scanned.

continuing on:


ops$tkyte@ORA817DEV.US.ORACLE.COM> select x, a from t;

         X
----------
         3
         2
         1

ops$tkyte@ORA817DEV.US.ORACLE.COM> @mystat continued
ops$tkyte@ORA817DEV.US.ORACLE.COM> select a.name, b.value
  2  from v$statname a, v$mystat b
  3  where a.statistic# = b.statistic#
  4  and lower(a.name) like '%' || lower('&1')||'%'
  5  --and b.value > 0
  6  /

NAME                                VALUE
------------------------------ ----------
table fetch continued row               3


No TFCR since we didn't have to assemble row 3, we just needed the first two columns

ops$tkyte@ORA817DEV.US.ORACLE.COM> select x, e from t;

         X
----------
         3
         2
         1

ops$tkyte@ORA817DEV.US.ORACLE.COM> @mystat continued
ops$tkyte@ORA817DEV.US.ORACLE.COM> select a.name, b.value
  2  from v$statname a, v$mystat b
  3  where a.statistic# = b.statistic#
  4  and lower(a.name) like '%' || lower('&1')||'%'
  5  --and b.value > 0
  6  /

NAME                                VALUE
------------------------------ ----------
table fetch continued row               4

but by asking for d and e -- we did.....

there you go.  You most likely have only MIGRATED ROWS but even if they are truly 
chained, the columns you are selecting are at the front of the table.

Migrated rows affect OLTP systems which use indexed reads to read singleton rows.  In the 
worst case, you can add an extra IO to all reads which would be really bad.

Truly chained rows -- well, they affect everyone.

So, how can you decide if you have migrated or truly chained?  glad you asked...

ops$tkyte@ORA817DEV.US.ORACLE.COM> select count(e) from t;

  COUNT(E)
----------
         1

ops$tkyte@ORA817DEV.US.ORACLE.COM> @mystat continued
ops$tkyte@ORA817DEV.US.ORACLE.COM> select a.name, b.value
  2  from v$statname a, v$mystat b
  3  where a.statistic# = b.statistic#
  4  and lower(a.name) like '%' || lower('&1')||'%'
  5  --and b.value > 0
  6  /

NAME                                VALUE
------------------------------ ----------
table fetch continued row               5


Just count the LAST column in that table -- That'll force us to construct the entire 
row.  Now, we can see that the count did 1 TFCR but we have:

ops$tkyte@ORA817DEV.US.ORACLE.COM> analyze table t compute statistics;

Table analyzed.

ops$tkyte@ORA817DEV.US.ORACLE.COM> select chain_cnt from user_tables 
  2  where table_name = 'T';

 CHAIN_CNT
----------
         3

three rows that are chained.  Apparently, 2 of them are MIGRATED (rows 1 and 2) and one 
is truly chained (row 3)
(用 *select count(e) from t/count最后一列* 的方式得出的TFCR即是这个表所有的行连接数目，
对表进行analyze table t compute statistics后得出该表所有行连接与行迁移的数目的和，
最后用后者减去前者便得到了行迁移的数目)
 

From: http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:4423420997870

补充自己实验时用到的一些SQL：

SELECT t.*
      ,DBMS_ROWID.rowid_row_number (t.ROWID) rn
      ,DBMS_ROWID.rowid_block_number (t.ROWID) blk
      ,DBMS_ROWID.rowid_relative_fno (t.ROWID) fno
      ,DBMS_ROWID.rowid_object (t.ROWID) obj
      ,t.rowid rid
FROM   t;

analyze table t compute statistics;

select * from user_tables where table_name = 'T';

select * from user_extents where segment_name = 'T';

analyze table t list chained rows into chained_rows;

select * from chained_rows;

set autotrace on;

set autotrace off;