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

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'));

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

Crikey.

> 3) I got all the migrated/chained rows in following temp table.
>
> create table temp_s_contact_03
> pctfree 0

Well, that explains a lot. It isn't merely the fact that a table is
subject to updates that causes row migration, but the fact that updated
rows have nowhere to grow into. And you provide no room for row growth
when you set pctfree to zero. If you're looking to cure row migration in
the long term you need to increase this setting as well as move the
affected rows.

>pctused 5
> inittrans 5
> unrecoverable

Why are you using Oracle 7 terminology? If this *is* Oracle 7, then fair
enough. But if it isn't, stick to 8.0+ terms, which in this case would
be "nologging".

For the rest of your post, please bump pctfree to something more
sensible and try again.

Regards
HJR

Anna C. Dent wrote:


> 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'));
>

What he'll get is a performance nightmare: the table is obviously
subject to many updates, and you've just suggested setting pctfree and
pctused so close together that blocks from this table are going to be
bouncing on and off the freelist like no-one's business. (15% free = 85%
full, therefore 85%-80% = merely a 5% gap between them).

I smell free list contention in the offing.

I agree with your 15 pctfree, but in that case he should set pctused to
35. That way, a 50% gap is maintained between the two settings... which
is, after all, the default in any case (and hence, presumably, generally
recommended).

Regards
HJR

You don't quote an Oracle version - and oddities
like this can be highly version dependent.

Was there any difference between the table
parameters you used for the first copy and
the second copy ? Was the pctfree zero,
was it unrecoverable ?

It is possible that the CTAS did something
that overestimated the number of rows that
could get into a block, and then had to correct
on the fly. (direct path load had a similar problem
a long time ago).

One minor detail - in your SQL to check row
lengths nvl(vsize(col),1) would probably be a
better approximation, as most columns will have
an extra one byte for the length. (trailing nulls
being one exception, and columns longer than
about 250 bytes which use a 3-byte length).


--
Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

April 2004 Iceland http://www.index.is/oracleday.php
June 2004 UK - Optimising Oracle Seminar
July 2004 USA West Coast, Optimising Oracle Seminar
August 2004 Charlotte NC, Optimising Oracle Seminar
September 2004 USA East Coast, Optimising Oracle Seminar
September2004 UK - Optimising Oracle Seminar

"MAK" <maks70@comcast.net> wrote in message
news:b7178504.0404261631.76c73a1d@posting.google.c om...
> 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....

Mr. HJR,

Thanks for all your suggestions and recommendations reg. pctfree and
unrecoverable. But that was not something I was looking for.


If you have read my post carefully, I was looking for an explaination
as to why I have migrated rows when there was no "UPDATE" activity ( I
just created table using CTAS and ran analyze to count
chained/migrated rows). I also mentioned that there was no row that
had row length more than the block size so chaining of rows can be
ruled out.

Can some one explain this?

FYI, I am using Oracle 9.2 database.

Thanks

> 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'));


More migrated rows!! 7240 rows to be precise.

Thanks

MAK wrote:

> Mr. HJR,
>
> Thanks for all your suggestions and recommendations reg. pctfree and
> unrecoverable. But that was not something I was looking for.
>
>
> If you have read my post carefully, I was looking for an explaination
> as to why I have migrated rows when there was no "UPDATE" activity ( I
> just created table using CTAS and ran analyze to count
> chained/migrated rows). I also mentioned that there was no row that
> had row length more than the block size so chaining of rows can be
> ruled out.
>
> Can some one explain this?


If *you* read *my* post carefully, you will hopefully get the idea that
anyone who sets PCTFREE to something stupid and uses a piece of syntax
that has been deprecated for more than 8 Oracle releases is just asking
for weird things to happen. You might not have been "looking for" such
comments, but they are true nonetheless and relate to your original
question.

Explanation? Who knows. Maybe it's a transaction slot issue.

Maybe it's a bug.

Does it really matter? Delete the rows from the original table and
re-insert them, and set pctfree properly, and the problem will be fixed
now and into the future.

HJR

Thanks JL for your reply.

> You don't quote an Oracle version - and oddities
> like this can be highly version dependent.
>
Its 9.2.0.3.

> Was there any difference between the table
> parameters you used for the first copy and
> the second copy ? Was the pctfree zero,
> was it unrecoverable ?

No! I was using unrecoverable both the times. I have tried without
unrecoverable too but to no avail.

> It is possible that the CTAS did something
> that overestimated the number of rows that
> could get into a block, and then had to correct
> on the fly. (direct path load had a similar problem
> a long time ago).

The table in question has 305 columns ( > 255) , should it make any
difference?
>

> One minor detail - in your SQL to check row
> lengths nvl(vsize(col),1) would probably be a
> better approximation, as most columns will have
> an extra one byte for the length. (trailing nulls
> being one exception, and columns longer than
> about 250 bytes which use a 3-byte length).
>

Exellent suggestion! Thanks.

The number of columns is the most relevant
point. A row defined with more than 250-ish
columns is stored as up to 4 separate row pieces,
and is therefore chained - even if all 4 pieces are
in the same block.

If most of your rows are NULL after column 250(-ish),
then the piece with all the nulls would not be required,
which is why you would be able to see just a few
thousand rows apparently chained after copying out
400,000 rows.

BUT - I discounted the 'long row' theory when I read
your post, because when you re-copied the table, you
had no chained rows. It seems like from your description,
though, that Oracle may have changed the way it handles
chaining when it is intra-block chaining - a couple of the things
it did in 8.1 needed refinement.

--
Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

April 2004 Iceland http://www.index.is/oracleday.php
June 2004 UK - Optimising Oracle Seminar
July 2004 USA West Coast, Optimising Oracle Seminar
August 2004 Charlotte NC, Optimising Oracle Seminar
September 2004 USA East Coast, Optimising Oracle Seminar
September2004 UK - Optimising Oracle Seminar

"MAK" <maks70@comcast.net> wrote in message
news:b7178504.0404271638.40945edc@posting.google.c om...
> Thanks JL for your reply.
>
> > You don't quote an Oracle version - and oddities
> > like this can be highly version dependent.
> >
> Its 9.2.0.3.
>
> The table in question has 305 columns ( > 255) , should it make any
> difference?
> >
>