Re: Chained vs. migrated rows - Any easy way to tell the difference?
- From: "Zhu,Chao" <zhuchao@xxxxxxxxx>
- To: patrick.elliott@xxxxxxxxxxxxx
- Date: Thu, 30 Oct 2008 23:07:34 +0800
avg_row_leng should also be a good indication of the row migration vs
chaining for many tables ..
Or do a sample based CTAS and see whether you still have CHAIN_CNT in
user_tables? For long raw usually rebuild does not work unless you know the
avg row legnth and setup a different block_size for that?
On Thu, Oct 30, 2008 at 10:32 PM, Elliott, Patrick <
patrick.elliott@xxxxxxxxxxxxx> wrote:
> 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
> >
> >
> --
> //www.freelists.org/webpage/oracle-l
>
>
>
>
> --
> //www.freelists.org/webpage/oracle-l
>
>
> --
> //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
> --
> //www.freelists.org/webpage/oracle-l
>
>
>
--
Regards
Zhu Chao
www.cnoug.org
Other related posts:
