RE: Chained vs. migrated rows - Any easy way to tell the difference?
Date: Thu, 30 Oct 2008 11:25:40 -0400
I believe you are right... If I analyze all about 270 tables in both databases, the count of records in the chained_rows tables are almost identical: Since there is only about a dozen tables with chained rows I can look into them individually.
So I can reset the numbers by rebooting the production database. Yes, they are v$ views so makes sense and I should have seen it. The 'table fetch continued row' doesn't seem to be of much use as they accumulate constantly -- or should I say it's all relative. This below number represents one months activity. The chained row count of represents years.
CHAINED ROWS -- count by table.
PEGADMIN @ pegaccp>
CHASSIS 1635 CONTRACT 4310 CONTRACT_REF_NUM 1110 GENSET 115 INTERFACE_ERROR 4284 LOCATION_CONTACT 14 LOCATION_NICKNAME 6 MOVE 105270 RAIL_MOVE 4370 STOP 160862 TRAILER 5673 VS_MOVE 105 PEGADMIN @ pegprod> CHASSIS 1636 CONTRACT 4406 CONTRACT_REF_NUM 1110 GENSET 115 INTERFACE_ERROR 4286 LOCATION_CONTACT 14 LOCATION_NICKNAME 6 MOVE 106973 RAIL_MOVE 4484 STOP 163596 TRAILER 5676 VS_MOVE 106
From: Elliott, Patrick [mailto:patrick.elliott_at_medtronic.com] Sent: Thursday, October 30, 2008 10:32 AM To: Patterson, Joel; mwf_at_rsiz.com; oracle-l_at_freelists.org Subject: RE: Chained vs. migrated rows - Any easy way to tell the difference?
The actual chained rows did not go down in the database. Your duplicate just restarted the database. The v$sysstat numbers are reset to zero when you bounce the database. It looks to me like you are badly in need of a database reorg. An rman duplicate is the same as a recovery of the datafiles, so there is no way that the chained rows could go down.
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Joel.Patterson_at_crowley.com
Sent: Thursday, October 30, 2008 8:08 AM To: mwf_at_rsiz.com; oracle-l_at_freelists.org Subject: RE: Chained vs. migrated rows - Any easy way to tell the difference?
I would like to ask a question peripheral to the discussion. I used RMAN duplicate to create the acceptance database. It appears the duplicate command significantly reduced the continued row count.
Can someone verify that RMAN would do that?
It brings up an option: Rather than do the usual fixes. I could (not counting FTP) duplicate acceptance and then duplicate back to production within two to three hours..... if it was a complete mess. (30 columns are still LONG columns).
(Or I could restore production from it's own backup in about half the time).
DBMON @ pegaccp> SELECT name, value FROM v$sysstat WHERE name = 'table fetch continued row';
NAME VALUE ------------------------------ ---------- table fetch continued row 2,156,586
DBMON @ pegprod> SELECT name, value FROM v$sysstat WHERE name = 'table fetch continued row';
NAME VALUE ------------------------------ ----------------- table fetch continued row 1,114,764,125
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Mark W. Farnham Sent: Wednesday, October 29, 2008 5:22 PM To: 'ORACLE-L'
Subject: FW: Chained vs. migrated rows - Any easy way to tell the difference?
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
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.)
On Behalf Of Steve Adams
Sent: Tuesday, October 28, 2008 4:42 PM
Subject: Re: Chained vs. migrated rows - Any easy way to tell the difference?
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
Subject: Chained vs. migrated rows - Any easy way to tell the difference?
Date: Tue, 28 Oct 2008 15:27:51 -0400
> We have two databases that are showing very high number of/ table
> 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
> 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
> 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.
> Jay Miller
-- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-l [CONFIDENTIALITY AND PRIVACY NOTICE] Information transmitted by this email is proprietary to Medtronic and is intended for use only by the individual or entity to which it is addressed, and may contain information that is private, privileged, confidential or exempt from disclosure under applicable law. If you are not the intended recipient or it appears that this mail has been forwarded to you without proper authority, you are notified that any use or dissemination of this information in any manner is strictly prohibited. In such cases, please delete this mail from your records. To view this notice in other languages you can either select the following link or manually copy and paste the link into the address bar of a web browser: http://emaildisclaimer.medtronic.com -- http://www.freelists.org/webpage/oracle-lReceived on Thu Oct 30 2008 - 10:25:40 CDT