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

Thanks for the correction, Paul.  I had a brain fart.  Pctfree is for reserving 
space in the blocks for growing rows.  If you change it on the fly it only 
applies to new blocks, it doesn't apply to existing blocks, but you can change 
it before a reorg since you are rebuilding the entire table.


Pat

-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On 
Behalf Of Baumgartel, Paul
Sent: Friday, October 31, 2008 8:09 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: RE: Chained vs. migrated rows - Any easy way to tell the difference?

Pat, did you mean pctfree?


Paul Baumgartel
CREDIT SUISSE
Information Technology
Prime Services Databases Americas
One Madison Avenue
New York, NY 10010
USA
Phone 212.538.1143
paul.baumgartel@xxxxxxxxxxxxxxxxx
www.credit-suisse.com


-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Elliott, Patrick
Sent: Thursday, October 30, 2008 5:08 PM
To: Joel.Patterson@xxxxxxxxxxx; mwf@xxxxxxxx; oracle-l@xxxxxxxxxxxxx
Subject: RE: Chained vs. migrated rows - Any easy way to tell the difference?

You might consider increasing your pctincrease on these tables to prevent the 
chained rows from coming back after the reorg.  This can happen if rows are 
inserted with null values and then populated afterwards.


Pat

-----Original Message-----
From: Joel.Patterson@xxxxxxxxxxx [mailto:Joel.Patterson@xxxxxxxxxxx]
Sent: Thursday, October 30, 2008 10:26 AM
To: Elliott, Patrick; mwf@xxxxxxxx; oracle-l@xxxxxxxxxxxxx
Subject: RE: Chained vs. migrated rows - Any easy way to tell the difference?

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


Joel Patterson
Database Administrator
joel.patterson@xxxxxxxxxxx
x72546
904  727-2546

-----Original Message-----
From: Elliott, Patrick [mailto:patrick.elliott@xxxxxxxxxxxxx]
Sent: Thursday, October 30, 2008 10:32 AM
To: Patterson, Joel; mwf@xxxxxxxx; oracle-l@xxxxxxxxxxxxx
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.


Pat

-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Joel.Patterson@xxxxxxxxxxx
Sent: Thursday, October 30, 2008 8:08 AM
To: mwf@xxxxxxxx; oracle-l@xxxxxxxxxxxxx
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

Joel Patterson
Database Administrator
joel.patterson@xxxxxxxxxxx
x72546
904  727-2546

-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] 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 you know.
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.)

Regards,

mwf

-----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 difference?

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
>
>
--
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-l




==============================================================================
Please access the attached hyperlink for an important electronic communications 
disclaimer:

http://www.credit-suisse.com/legal/en/disclaimer_email_ib.html
==============================================================================

--
http://www.freelists.org/webpage/oracle-l


--
http://www.freelists.org/webpage/oracle-l

Other related posts: