Prev: Client Connection Error (TNS-12518 TNS-12560 TNS-00530)
Next: Client problem
From: aprinsloo on 12 Dec 2006 16:35
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

From: joel garry on 12 Dec 2006 16:48

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

Perhaps the pct_free only applies to newly added rows but you are
having problems updating rows that are already there with the old
pct_free?

jg
--
@home.com is bogus.
http://www.tomyang.net/cars/ferrari.html?http://www.tomyang.net/cars/story1.htm

From: Matthias Hoys on 12 Dec 2006 16:53

<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


From: aprinsloo on 12 Dec 2006 17:19
Would a exp and and imp for the table achieve thhe same result as the
"alter move"?

Thx



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

From: Anurag Varma on 12 Dec 2006 17:23

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

Find out if its row chaining or row migration! If its row chaining,
then there is nothing
you can do about it assuming you don't want the drastic route of
increasing block size.
Row chaining refers to a row which does not fit in the existing block
size. Tweaking pctfree
is not going to do anything about it.
If its row migration then increasing pctfree might decrease incidences
of future row migrations.
For row migration in existing data, you might want to "alter table
move" followed by rebuilding
all indexes to fix the issue...

Anurag

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