Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question.

Asked: October 14, 2002 - 10:40 am UTC

Last updated: March 09, 2010 - 11:11 am UTC

Version: 8.1.7

Viewed 1000+ times

You Asked

Hi Tom,

I asked this questions to my coleagues/friends and not convinced with their answers.

What is the advantage of having forward address of a row that is migrated, in one block and row data in another block. I know that it is due to the index entry. why cant the server be built such that it updates the index entry once a row is migrated because it is one time update and there will be no double block read while retrieving the data.

If a table dont have an index at all and row migration takes place for a row, how will you support having forward address in one block and data in another block.

i am asking this question out of curiousity and nothing else. If i am wrong please excuse.

Regards,
Vijaya Chander V.S






and Tom said...

You could but -- it would seriously damage the index structure.

In an index, you have the rowid as part of the key. An update to a row might have to update 6 indexes (cause the row moved) and update the indexes -- MOVING the rows in the index, perhaps splitting that up - but definitely cause tons of extra work.

An occasional extra IO on an indexed read was deemed preferrable to having to reach out and touch tons of index data.

Additionally -- with the exception of:

o index organized tables when you update a primary key
o partitioned tables with "enable row movement" specifically turned on and an update to the partition key

a rowid is immutable -- it is assigned to a row upon insert and will never change. Many tools count on this fact (replication used to but still can, indexes do, Oracle Forms does by default for row locking, and so on)




Rating

  (14 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

thank you

Vijaya Chander V.S, October 14, 2002 - 11:25 pm UTC

Hi Tom,

Thank you for your explanation. Why cant we think to delete that row and insert into the table again if a row has to be migrated due to the unavailability of space. Actually we are doing this operation on behalf of the server to get rid of the migrated rows. why cant this feature be implemented in the server itself.

Tom Kyte
October 15, 2002 - 8:05 am UTC

It could be, there is not reason TECHNICALLY it could be however

o the rowid would change (bad)
o ALL of the indexes would have to be updated (very bad)
o it is not frequent (so why worry)

I can count on one hand the number of times I've felt compelled to lose sleep over migrated rows. People worry way too much about it. Until MOST of the table is migrated, it is not an issue (and points to the fact that you've set pctfree way too high for that table perhaps)

Does this apply to chained rows also ?

Arun Gupta, October 15, 2002 - 9:20 am UTC

I read in the Oracle manual that to Oracle chained rows and migrated rows are same. Does your explanation apply to chained as well as migrated rows. I ask since I am being made to lose sleep over chained/migrated rows.
Thanks
Arun


Tom Kyte
October 15, 2002 - 9:52 am UTC

A migrated row is a special case of a chained row.

A migrated row can be "fixed".

A 'truly' chained row is a row that spans more then one block cause it doesn't fit on a block. They cannot be "fixed"

check out:
</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:4423420997870 <code>





A third possibility ?

Connor McDonald, October 16, 2002 - 4:46 am UTC

With regard to

"o index organized tables when you update a primary key
o partitioned tables with enable row movement ..."

could you also get a rowid change if you updated a row in a compressed block (9.2) ?

Cheers
Connor

Tom Kyte
October 16, 2002 - 1:04 pm UTC

it might migrate, but won't change the rowid:

ops$tkyte@ORA920.LOCALHOST> create table t compress
  2  as
  3  select * from all_objects order by owner, object_type
  4  /

Table created.

ops$tkyte@ORA920.LOCALHOST> create table t2 as select object_id, rowid rid from t;

Table created.

ops$tkyte@ORA920.LOCALHOST>
ops$tkyte@ORA920.LOCALHOST> analyze table t compute statistics;

Table analyzed.

ops$tkyte@ORA920.LOCALHOST> select CHAIN_CNT from user_tables where table_name = 'T';

 CHAIN_CNT
----------
         0

ops$tkyte@ORA920.LOCALHOST>
ops$tkyte@ORA920.LOCALHOST> update t set owner = rownum;

29313 rows updated.

ops$tkyte@ORA920.LOCALHOST>
ops$tkyte@ORA920.LOCALHOST> analyze table t compute statistics;

Table analyzed.

ops$tkyte@ORA920.LOCALHOST> select CHAIN_CNT from user_tables where table_name = 'T';

 CHAIN_CNT
----------
     29311

ops$tkyte@ORA920.LOCALHOST>
ops$tkyte@ORA920.LOCALHOST> select * from t2 minus select object_id, rowid from t;

no rows selected

ops$tkyte@ORA920.LOCALHOST> select object_id, rowid from t minus select * from t2;

no rows selected



see they all migrate, the rowids are the same for each row...

 

Vipin, July 07, 2003 - 2:54 pm UTC

Hi Tom,

Reading this article, now the difference between Row chaining and Row migration is very clear.

But I am left with a doubt that when Row chaining results and when Row migration results.

From your above example Updation of row where x=3 resulted in migration of row where x=2 row, but at the same time updation of row where x=3 later resulted in row chaining. Now please tell me what leads to what? Hope I am clear.

One more doubt I am having is , in one another article you said that Row migration is fixable but Row chaining is not.

</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:5968286426175 <code>

But with export/import or alter table move row chaining is also fixable right?

Please explain.


Tom Kyte
July 07, 2003 - 3:51 pm UTC

A row migrates when an update causes it to not fit on the block with the other rows that are there. So, the row moves to a block where it can fit

A row CHAINS over multiple blocks when it is too big to fit on a block. If you have a table:

create table t ( x varchar2(4000) );

and you use a 2k block size
and you put 4000 characters in there

you will have a chained row -- that is not "fixable" by any means short of putting it into a database with a larger block size. export and import or alter table move will not *fix* this condition.

Vipin, July 07, 2003 - 3:05 pm UTC

Hi Tom,

I am sorry I asked this question as the followup of another question. Actually I wanted to ask this question as a followup of this question:-

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

row migration

Reader, October 15, 2003 - 3:51 pm UTC

If i have a 4kb block and after update of a row the row size is larger than 4kb, how does oracle handle this? Will it handle as migration or chaining? Thanks.

Tom Kyte
October 16, 2003 - 9:55 am UTC

chained -- the row doesn't fit on a block. it'll be chained over more then 1 block.

Row movement enabled

Neil, October 17, 2003 - 10:47 am UTC

What happens to the space left when a row is moved as a result of "enable row movement" in a partitioned table?

Tom Kyte
October 17, 2003 - 11:07 am UTC

it is just as if you had "deleted" and "inserted" the row. it is available for reuse by subsequently inserted rows.

Row movement enabled on a partition or partitions

Neil, October 27, 2003 - 5:52 am UTC

I want to use row movement to archive data. Each target table has two partitions, a live and an archive. When a record is marked for archival in the driving table, a process will run to take the foreign key and go and mark all the other related records in the other target tables.
If I have the archived partitions in their own tablespaces, I can mark them as read only. However, row movement implies that I'll need to change this to get the newly archived data into the archive partition. I would then have to change the tablespace back to read only. To do this, I need to know if the row movement has finished. What's the best way to achieve that?

Tom Kyte
October 27, 2003 - 7:51 am UTC

you are running the "archive process" are you not?

you would know when it is done by the fact the archive process completed??

not sure what you mean by "when the row movement is done", it is done when the transaction commits?

Row movement

Neil, October 27, 2003 - 10:02 am UTC

So the row movement isn't done in the background but as part of the transaction updating the row. That's fine.

dbms_stats and chain count

Shankar, November 05, 2003 - 3:36 pm UTC

Tom, If I used dbms_stats package to collect statistics for a table, it does not update chain_cnt column in dba_tables view. If I used analyze command, only then it updates the chain_cnt column. It was strange to me because Oracle is pushing dbms_stats package to collect statistics. Why shouldn't dbms_stats package generate this very important statistics. Thanks.

Tom Kyte
November 06, 2003 - 7:16 am UTC

chain_cnt isn't used by the optimizer so dbms_stats doesn't bother collecting it. to the optimizer, chain_cnt doesn't exist.

dbms_stats collects only that which the optimizer uses

Table fetch continued

David, May 08, 2004 - 9:34 am UTC

If I see some percentage of rows for the table fetch continued stats in the statspack report, only If I use ANALYZE command I can find which table has migrated or chained rows. The stats only tell me these many rows are chanined or migrated but does not tell me which table it is. How do I approach this. Do I have to ANALYZE all of the tables to find out which table is the culprit? Any other way? Thanks. You are the best.

Tom Kyte
May 10, 2004 - 6:44 am UTC

I'd first ask "am i seeing a large percentage, like a whole bunch".

if not, don't worry.

if so, i'd ask "am i using long/long raw -- or do i have tables with rowsizes > blocksize -- or rows with more than 255 columns". If so, don't worry, nothing you can do to fix those (other than increase blocksize, decrease number of columns or stop using long/long raw and use LOBS instead)


if neither of those pan out, you might use the session statistics to see which sessions are incurring this, and review the application they are using. That'll generally help you pinpoint where this is (which tables could be culprits). You should notice that their sql is consuming more LIO's then it should be as well (if it isn't, it isn't a problem)

Performance Impact due to Row Migration

Vivek Sharma, April 29, 2005 - 5:53 am UTC

Hi Tom,

I have a book written by Richmond Lee, Kirtikumar and K. Gopalakrishnan. The name of the Book is "Oracle Wait Interface".

In Chapter 5, page 117 of this book under the heading "Why Does db file sequential read event show up in a Full Table Scan Operation", the authors say that apart from various reasons, one reason a full table scan will show waiting for db file sequential read wait event is "Chained and Migrated Rows". I don't agree with this Statement. For Chained Rows, it can be the case but as far as Migrated Rows, FTS should never show db file sequential read.

As per my understanding, if a block is read in the buffer cache and if that block contains some header detail of the migrated rows, it should ignore these details and fetch other rows contained in that block. For example, assuming my db_file_multiblock_read_count is 8. My FTS query on a table will fetch block nos.4 to 11. If block 5 contains a header detail of a row which is migrated to block no 100, it should not fetch block # 100 rather it should ignore that row during this fetch and read other rows from block 5. Whereas the row which was migrated to block 100 and had a header detail in 5 will only be fetched when block 100 was read based on db_file_multiblock_read_count algorithm. Am I correct ?

I think, you are the best person to validate the Statement contained in that book.

Thanks and Regards
Vivek


Tom Kyte
April 29, 2005 - 8:54 am UTC

Well, you can and will see some db file sequential reads in a normal full scan. they are normally the result of:

we need to read file 42
we need blocks 9 thru 16
block 42.15 is in the buffer cache

multiblock read 9..14
singleblock read 16
buffer get 15

but yes, as demonstrated here
</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:35336203098853 <code>

when we full scan and hit the head row piece, we ignore it -- knowing full well we'll pick up the tail later (or we might already have hit it!! the tail could be in front of the head)

I would concurr after reading the text that it should only talk of chained rows in the context of a full scan, migrated rows are not a "single block io" issue in the context of a full scan.

I re-ran the tests on the above referenced link. A one million row table with in this run, 984,840 migrated rows:

select * from t

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 66668 5.30 5.17 18002 84668 0 1000000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 66670 5.30 5.17 18002 84668 0 1000000

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 276

Rows Row Source Operation
------- ---------------------------------------------------
1000000 TABLE ACCESS FULL OBJ#(40909) (cr=84668 r=18002 w=0 time=1926673 us)


Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 66668 0.00 0.08
db file sequential read 1 0.00 0.00
db file scattered read 2272 0.00 0.16
SQL*Net message from client 66668 15.56 31.43




Idetifying Tables Having Migrated Rows

Rob, August 11, 2006 - 4:12 pm UTC

Tom,

I saw your update saying do it at application level. But is there any other way (as it used to be there with "Analyze") to find tables having migrated rows in the database? I want to find out all the tables that either have this kind of issue or may have in future, this way I can take a proactive approach to resolve future issues.

Rob


Tom Kyte
August 11, 2006 - 6:07 pm UTC

analyze table T list chained rows;

row migration n row chaining

Prasad, March 08, 2010 - 6:39 am UTC

How will you identify the no. Of Row migration/ Row Chaining happened in the database. How will you resolve it.
Tom Kyte
March 09, 2010 - 11:11 am UTC

analyze table list chained rows will identify them.

then you can see if
a) they have more than 255 columns, if so, they will be chained, nothing to fix, nothing can fix this.

b) they are longer than can fit on a block. they will be chained, nothing to fix.

c) they are neither (a) nor (b), they are migrated. You could:

create table temp as select * from t where rowid in (select from the chained table);
delete from t where rowid in (select from the chained table);
insert into t select * from temp;
drop table temp;

If you are really bothered by this, it would be an extreme case whereby the extra logical IO caused by a migrated row was a quantifiable performance issue.