Prev: Client Connection Error (TNS-12518 TNS-12560 TNS-00530)
Next: Client problem
From: joel garry on 14 Dec 2006 12:58

aprinsloo(a)sagetelecom.net wrote:
> Would a exp and and imp for the table achieve thhe same result as the
> "alter move"?
>

Please don't top post (that means, either interleave your answers with
what you are responding to, or scroll down to the bottom before you
begin typing).

More or less. You might consider the distribution of future updates
among your current data. If there won't be many, you might waste a lot
of space with a high percent free, you might consider importing data
that won't be updated later with a low percent free to fix the problem,
then set it up higher for data that will be updated, if you can
determine that about your data and the query option of exp is suitable.

Also, Anurag's comment about row chaining v. migration is something to
investigate. It is important that you understand your data's
attributes and constraints, it's not a good thing to just imp/exp and
hope for the best. If it doesn't fix your problem and you've wasted a
lot of space, you might decrease performance as Oracle scans more empty
space in some situations.

>
>
> Matthias Hoys wrote:
> > <aprinsloo(a)sagetelecom.net> wrote in message
> > news:1165959325.251501.186110(a)80g2000cwy.googlegroups.com...
> > > Oracle 9i standard.
> > >
> > > Ive noticed by looking at DBA_TABLES that some tables have excessive
> > > CHAIN_CNT, the highest been 3266085 on a 45 million row table.
> > > I've set the pct_free to 35 up from 10 but the CHAIN_CNT continues to
> > > climb, is there anything else I can try ?
> > >
> > > Thx in advance
> > >
> >
> > The pct_free change will only affect new blocks. The increase you are seeing
> > is for existing blocks that get updated. Permanent solution : rebuild the
> > whole table with a higher pct_free with ALTER TABLE ... MOVE ... However,
> > you will need enough temporary space for this operation + you will need to
> > rebuild the table indexes and recalculate the stats afterwards. Good luck
> > :-)
> >
> >
> > Matthias

jg
--
@home.com is bogus.
'Check the box' auditing:
http://www.signonsandiego.com/uniontrib/20061213/news_1b13fannie.html

From: fitzjarrell on 14 Dec 2006 15:22

aprinsloo(a)sagetelecom.net wrote:
> Oracle 9i standard.
>
> Ive noticed by looking at DBA_TABLES that some tables have excessive
> CHAIN_CNT, the highest been 3266085 on a 45 million row table.
> I've set the pct_free to 35 up from 10 but the CHAIN_CNT continues to
> climb, is there anything else I can try ?
>
> Thx in advance

Altering the pctfree in an existing table only affects extents created
after the pctfree was adjusted, it does not affect currently populated
blocks. The only way you can affect the entire table is either with an
alter table ... move tablespace ... pctfree xx; or by exporting the
table, dropping it and recreating it with the desired pctfree value.
The CHAIN_CNT will continue to climb because you haven't affected
existing data blocks.


David Fitzjarrell

First  |  Prev  | 
Pages: 1 2
Prev: Client Connection Error (TNS-12518 TNS-12560 TNS-00530)
Next: Client problem