Re: chained rows

From: Kevin A Lewis <KevinALewis_at_Hotmail.com>
Date: Thu, 8 Jul 1999 10:30:50 +0100
Message-ID: <U7_g3.7719$1F3.1131@newreader.ukcore.bt.net>

Firstly if chained rows are only just more than 5 I would not worry about them. if however there are many then look into it more closely. Only worry if the proportion of the table rows chained is high. Or worry if the Rows chained are the ones frequently accessed (i.e. recent rows currently being worked on untill they get old and not of interest.)

you need to look at the DB_BLOCK_SIZE and the actual or average row lengths for each table and compare the two. For instance if the actual row lengths are 4k and the block size is 2 then every row will chain.

To get rid of chained rows once found I use the following sqlplus routine which makes use of the system table 'chained_rows':

Cut from here
-- Analyze the number of chained rows in the table in question analyze table &&x_owner..&&x_table list chained rows into chained_rows;

• Make a copy of the chained rows in a holding table create table add_chained as select * from &x_owner..&x_table where rowid in (select head_rowid from chained_rows where table_name = '&x_table');
• Delete chained rows from &x_table delete &x_owner..&x_table where rowid in (select head_rowid from chained_rows where table_name = '&x_table');
• Move rows back in to &x_table insert into &x_owner..&x_table select * from add_chained;
• Drop the intermediate table drop table add_chained;
• Clear chained row entries from the analysis table delete chained_rows where table_name = '&x_table';

undefine x_owner;
undefine x_table;
Cut to here

Once you have run this the rows affected by chaining will have been extracted from the table and returned, hopefully in one piece. Then you should reanalyze the system regularly for reoccurance. If some tables rpoduce many chained rows, frequently you should consider adjusting the PCTFREE figure for the table affected.

The official way to deal with this is to export/import, with optional adjustment of PCTFREE

Regards

--
Kevin A Lewis (BOCM PAULS LTD - Animal Feed Manufacturer - Ipswich England)

                        <KevinALewis_at_HotMail.com>

The views expressed herein by the author of this document are not necessarily those of BOCM PAULS Ltd. <farrellyr_at_my-deja.com> wrote in message news:7m01be$i9u$1_at_nnrp1.deja.com...
> I have done the dbms_utility.analyze_schema and have come up with a few
> tables that have chain_cnt > 5 in the dba_tables. When I look in the
> chained_row table I don't have any information there, no head_rowid in
> order to delete and re-insert the rows. I have used the analyze table
> 'table_name' list chained rows and compute statistics, but I still
> don't have anything show up when I query the chained_rows table. Does
> this mean chaining isn't a problem? Should there be some rows showing
> up in the chained_rows table to correspond with the dba_tables? Or am
> I doing something wrong?

>

> Thanks again.

>
>

> In article <9NFg3.4397$nw2.1038_at_newreader.ukcore.bt.net>,
> "Kevin A Lewis" <KevinALewis_at_Hotmail.com> wrote:
> > The simplest way that I have found is to analyze statistics for a
> whole
> > schema
> >
> > execute sys.dbms_utility.analyze_schema
> ('SCHEMA_NAME','estimate',null,'20')
> >
> > This estimates the table to 20 % of its data content and this can be
> > adjusted to suit your environment.
> >
> > Then when complete statistics on the tables and indexes will exist in
> the
> > administration views of the database. For instance DBA_TABLES.
> >
> > If you query DBA_TABLES for CHAIN_CNT > 0 you will have most of the
> affected
> > tables and an approximation of the extent of the chaining.
> >
> > Regards
> >
> > --
> > Kevin A Lewis (BOCM PAULS LTD - Animal Feed Manufacturer - Ipswich
> England)
> > <KevinALewis_at_HotMail.com>
> >
> > The views expressed herein by the author of this document
> > are not necessarily those of BOCM PAULS Ltd.
> > <farrellyr_at_my-deja.com> wrote in message news:7ltrcu$pe8
> $1_at_nnrp1.deja.com...
> > > I want to start checking for chained rows. I have run the
> utlchain.sql
> > > to set up table for information. What I am wondering is, do I have
> to
> > > go through and analyze table 'table_name' list chained rows for
> every
> > > table? Or is there a way I can narrow it down to a certain number
> of
> > > tables? Like maybe finding the most used or something like that,
> if so
> > > how would I go about that?
> > >
> > > Thanks.
> > >
> > >
> > > Sent via Deja.com http://www.deja.com/
> > > Share what you know. Learn what you don't.
> >
> >

>
>

> Sent via Deja.com http://www.deja.com/
> Share what you know. Learn what you don't.
Received on Thu Jul 08 1999 - 04:30:50 CDT