FW: Chained vs. migrated rows - Any easy way to tell the difference?

  • From: "Mark W. Farnham" <mwf@xxxxxxxx>
  • To: "'ORACLE-L'" <oracle-l@xxxxxxxxxxxxx>
  • Date: Wed, 29 Oct 2008 17:21:56 -0400

Steve (as usual) nailed it. As for the operation of the fix if you 
decide you need it I have some ideas. If you set up the destination 
table for the chained rows to be one row per block, then the 
difference between used blocks in and count(*) of the destination 
table is a ceiling on the number of chained rows. (It could overstate 
the number of chained rows if one or more chained rows take up more 
than 2 blocks. So if you have 1000 rows in the "chained row" 
destination table and 1000 used blocks they were all migrated. IF 1001 
then you know one was chained, but IF 1002 you aren't sure whether it 
was two chained rows or one multi-chained row from just the
count(*) and the used block data.
But since you're trying to see if there are enough migrated rows to 
bother fixing, that ceiling number subtracted from the count should let you
And I think you can identify the chained rows from the destination 
table as the ones that throw a continued row if you select the last 
column there. If there are enough total rows to bother differentiating 
between chained and migrated in your fix, you could use that 
information to skip the deletion/reinsertion of the chained rows and 
then use the destination table as the driver of the delete and 
reinsertion of the migrated rows. (Simply delete the rows identifed as 
truly chained in the destination table, leaving only the previously 
migrated rows there.)

-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Steve Adams
Sent: Tuesday, October 28, 2008 4:42 PM
To: Jay.Miller@xxxxxxxxxxxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx
Subject: Re: Chained vs. migrated rows - Any easy way to tell the

Hi Jay,

You can tell the difference between row migration and chaining by 
listing the chained rows with ANALYZE table LIST CHAINED ROWS and then 
fetching the first column from each "chained row" in a single query. The 
count of continued row fetches will be incremented for every migrated 
row, but not for most chained rows (unless the first cut point happens 
to fall with the first column, which should be rare).

@   Regards,
@   Steve Adams
@   http://www.ixora.com.au/         - For DBAs
@   http://www.christianity.net.au/  - For all

-----Original Message-----
Subject: Chained vs. migrated rows - Any easy way to tell the difference?
Date: Tue, 28 Oct 2008 15:27:51 -0400
From: <Jay.Miller@xxxxxxxxxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Jay.Miller@xxxxxxxxxxxxxxxx wrote:
> We have two databases that are showing very high number of/ table fetch 
> continued row/ in v$sysstat each day and before doing a move or 
> export/import or copying the rows off and reinserting them I was hoping 
> to find out if I'd really gain anything.
> All I found in the Oracle docs was the suggestion to assume they're 
> migrated and if the fix doesn't work then that means they were really 
> chained ( Note:122020.1).
> I'm considering using length() on all the columns and adding them 
> together to find any rows that wouldn't fit in a block but was wondering 
> if there was an easier way.  Besides, one of the tables (third party 
> app) has a long raw column so there's no easy way to get the column 
> length there.
> Thanks,
> Jay Miller


Other related posts:

  • » FW: Chained vs. migrated rows - Any easy way to tell the difference?