本文主要是介绍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(*) ----------258683SQL> 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 277139SQL> select a.name, b.valuefrom v$statname a, v$mystat bwhere 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 99SQL> 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 calls13 db block gets81903 consistent gets8377 physical reads180 redo size56728523 bytes sent via SQL*Net to client7877096 bytes received via SQL*Net from client70963 SQL*Net roundtrips to/from client1 sorts (memory)0 sorts (disk)1064428 rows processedSQL> select a.name, b.valuefrom v$statname a, v$mystat bwhere a.statistic#=b.statistic#and name like 'table%';9 rows selected.Statistics ----------------------------------------------------------136 recursive calls0 db block gets34 consistent gets0 physical reads0 redo size1245 bytes sent via SQL*Net to client425 bytes received via SQL*Net from client2 SQL*Net roundtrips to/from client6 sorts (memory)0 sorts (disk)12 rows processedSQL> 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 999 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 twhere 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 tthen 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 noprintops$tkyte@ORA817DEV.US.ORACLE.COM> select * from t;X ----------123ops$tkyte@ORA817DEV.US.ORACLE.COM> @mystat continued ops$tkyte@ORA817DEV.US.ORACLE.COM> select a.name, b.value2 from v$statname a, v$mystat b3 where a.statistic# = b.statistic#4 and lower(a.name) like '%' || lower('&1')||'%'5 --and b.value > 06 /NAME VALUE ------------------------------ ---------- table fetch continued row 0Now 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 ----------321ops$tkyte@ORA817DEV.US.ORACLE.COM> @mystat continued ops$tkyte@ORA817DEV.US.ORACLE.COM> select a.name, b.value2 from v$statname a, v$mystat b3 where a.statistic# = b.statistic#4 and lower(a.name) like '%' || lower('&1')||'%'5 --and b.value > 06 /NAME VALUE ------------------------------ ---------- table fetch continued row 0Interesting, 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 ----------3ops$tkyte@ORA817DEV.US.ORACLE.COM> @mystat continued ops$tkyte@ORA817DEV.US.ORACLE.COM> select a.name, b.value2 from v$statname a, v$mystat b3 where a.statistic# = b.statistic#4 and lower(a.name) like '%' || lower('&1')||'%'5 --and b.value > 06 /NAME VALUE ------------------------------ ---------- table fetch continued row 0I'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 ----------1ops$tkyte@ORA817DEV.US.ORACLE.COM> @mystat continued ops$tkyte@ORA817DEV.US.ORACLE.COM> select a.name, b.value2 from v$statname a, v$mystat b3 where a.statistic# = b.statistic#4 and lower(a.name) like '%' || lower('&1')||'%'5 --and b.value > 06 /NAME VALUE ------------------------------ ---------- table fetch continued row 1but 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 ----------3ops$tkyte@ORA817DEV.US.ORACLE.COM> @mystat continued ops$tkyte@ORA817DEV.US.ORACLE.COM> select a.name, b.value2 from v$statname a, v$mystat b3 where a.statistic# = b.statistic#4 and lower(a.name) like '%' || lower('&1')||'%'5 --and b.value > 06 /NAME VALUE ------------------------------ ---------- table fetch continued row 1fetch 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 ----------3ops$tkyte@ORA817DEV.US.ORACLE.COM> @mystat continued ops$tkyte@ORA817DEV.US.ORACLE.COM> select a.name, b.value2 from v$statname a, v$mystat b3 where a.statistic# = b.statistic#4 and lower(a.name) like '%' || lower('&1')||'%'5 --and b.value > 06 /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 ----------321ops$tkyte@ORA817DEV.US.ORACLE.COM> @mystat continued ops$tkyte@ORA817DEV.US.ORACLE.COM> select a.name, b.value2 from v$statname a, v$mystat b3 where a.statistic# = b.statistic#4 and lower(a.name) like '%' || lower('&1')||'%'5 --and b.value > 06 /NAME VALUE ------------------------------ ---------- table fetch continued row 3the 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 ----------321ops$tkyte@ORA817DEV.US.ORACLE.COM> @mystat continued ops$tkyte@ORA817DEV.US.ORACLE.COM> select a.name, b.value2 from v$statname a, v$mystat b3 where a.statistic# = b.statistic#4 and lower(a.name) like '%' || lower('&1')||'%'5 --and b.value > 06 /NAME VALUE ------------------------------ ---------- table fetch continued row 3No TFCR since we didn't have to assemble row 3, we just needed the first two columnsops$tkyte@ORA817DEV.US.ORACLE.COM> select x, e from t;X ----------321ops$tkyte@ORA817DEV.US.ORACLE.COM> @mystat continued ops$tkyte@ORA817DEV.US.ORACLE.COM> select a.name, b.value2 from v$statname a, v$mystat b3 where a.statistic# = b.statistic#4 and lower(a.name) like '%' || lower('&1')||'%'5 --and b.value > 06 /NAME VALUE ------------------------------ ---------- table fetch continued row 4but 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) ----------1ops$tkyte@ORA817DEV.US.ORACLE.COM> @mystat continued ops$tkyte@ORA817DEV.US.ORACLE.COM> select a.name, b.value2 from v$statname a, v$mystat b3 where a.statistic# = b.statistic#4 and lower(a.name) like '%' || lower('&1')||'%'5 --and b.value > 06 /NAME VALUE ------------------------------ ---------- table fetch continued row 5Just 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 ----------3three 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;
这篇关于Ask Tom 'Chained Rows and Autotrace'的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！
