Re: Chained Rows

Hello Mark!

The average rowlength is about 1K.
The blocksize is 8K.
By now we are trying several storage parameter-setups, robably this helps.

Thanks for your help!

Bye!
Oli
"Mark D Powell" <Mark.Powell_at_eds.com> schrieb im Newsbeitrag news:2687bb95.0403030615.17075004_at_posting.google.com...
> Daniel Morgan <damorgan_at_x.washington.edu> wrote in message
news:<1078266089.98353_at_yasure>...
> > Oliver Stratmann wrote:
> > > Hello All,
> > >
> > > we have a table which has about 600.000 rows and the size of about 700
> > > MByte.
> > > This table was initialized first with not all columns being filled and
> > > afterwards calculation-results
> > > were added into empty columns.
> > > Though before the table was reorganized by a MOVE and the
PCTFREE-parameter
> > > was set to 40
> > > after the calculation 100% of the rows seem to be chained (result of
> > > "ANALYZE TABLE T LIST CHAINED ROWS IN CHAINED_ROWS").
> > > I read a few FAQs and Docs about this problem but probably i've
misread them
> > > or overread the important parts of them.
> > > I couldn't find an answer.
> > > Are there any hints you could give?
> > > Thanks in advance!
> > >
> > > Bye!
> > > Oli
> > >
> > > PS:
> > > The Storage-Clause in more detail:
> > > PCTFREE 40
> > > PCTUSED 40
> > > INITRANS 1
> > > MAXTRANS 255
> > > TABLESPACE tbspace_name
> > > STORAGE (
> > > INITIAL 314572800
> > > NEXT 104857600
> > > PCTINCREASE 0
> > > MINEXTENTS 1
> > > MAXEXTENTS 2000000
> >
> > For now ... rebuild the table. Then recalculate PCTFREE and PCTUSED
> > so that they work. I can't understand why you would use 40 for both
> > parameters.
> >
> > For more information on them go to:
> > http://www.psoug.org/reference/tables.html
>
> Oliver, what is the average row size for the rows in this table? Also
> what is your Oracle block size? Remember that the available space to
> hold rows is the Oracle Block size minus the overhead minus the
> pctfree. If you average row length exceeded this amount then results
> like you saw are likely.
>
> If the rows are small enought to fit more than one to a block then
> since most of the existing rows are probably filled out what you
> really want to do is re-create the table with a very low pctfree and
> load the older filled out rows. Then modify the pctfree larger and
> load the non-filled out rows. This will give you the most compacted
> format of the table with space for growth where it is needed.
>
> If the rows are long then chaining is just a fact of life and not a
> problem though it could be one additional reason to migrate to an 8k
> block if you are using 4k.
>
> HTH -- Mark D Powell --
Received on Thu Mar 04 2004 - 04:28:14 CST