Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Incorrect Migrated/Chained rows...

Re: Incorrect Migrated/Chained rows...

From: Anna C. Dent <anacedent_at_hotmail.com>
Date: Mon, 26 Apr 2004 17:53:31 -0700
Message-ID: <gaijc.2005$Jy3.1163@fed1read03>


MAK wrote:
> I am trying to reduce nos of migrated/chained rows from our database.
> I did following things to get them identified and corrected. The
> database is using 8K block size.
>
> 1) analyze table s_contact list chained rows into chained_rows;
> 2) select count(*) from chained_rows where table_name = 'S_CONTACT'
> count(*)
> ----------
> 488080
> 3) I got all the migrated/chained rows in following temp table.
>
> create table temp_s_contact_03
> pctfree 0 pctused 5
> inittrans 5
> unrecoverable
> as
> (
> select * from s_contact
> where rowid in ( select head_rowid from chained_rows where table_name
> = 'S_CONTACT'));
>
> Table created.
>
> Wanted to check , if this has reduced any migrated/chained rows. So
> ran following things.
>
> 4) analyze table temp_s_contact_03 list chained rows into
> chained_rows.
>
> 5) select count(*) from chained_rows where table_name =
> 'TEMP_S_CONTACT_03';
> count(*)
> ----------
> 6056
>
> Why I still got 6056 chained/migrated rows? I thought I might have
> some chained rows (rows bigger than the block size which is 8K). So I
> ran following command to get average row lengh.
>
> 6) analyze table temp_s_contact_03 compute statistics ;
> 7) select avg_row_len, chain_cnt from dba_tables where table_name =
> 'TEMP_S_CONTACT_03';
>
> AVG_ROW_LEN CHAIN_CNT
> ----------- ----------
> 565 6056
>
> Its shows average row length is < 8K so most likely all 6056 should
> be migrated rows[ Why they became migrated rows with no update
> activity??? ]. I ran following query to get the max , min, avg row
> length to confirm.
>
>
> 8) select max(ln),min(ln), avg(ln)
> from
> ( select nvl(vsize(col1),0)+nvl(vsize(col1),0)+...nvl(vsize(col_n),0)
> ln from temp_s_contact_03 );
>
>
> MAX(LN) MIN(LN) AVG(LN)
> ---------- ---------- ----------
> 715 154 258.20
>
> So maximum row length is 715bytes. So if this true then all the
> rows(6056) are migrated and not the chained rows. Why do I have
> migrated rows with no update activity? Can some one explain?
>
> I also noticed an interesting thing, If I create another table from
> temp_s_contact_03 and analyze that table, I got 0 chained rows . Any
> clue?
>
> Thanks in advance....

What do you get if you do the following:

create table temp_s_contact_03
pctfree 15 pctused 80 inittrans 5 unrecoverable as (select * from s_contact
where rowid in ( select head_rowid from chained_rows where table_name = 'S_CONTACT')); Received on Mon Apr 26 2004 - 19:53:31 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US