Re: Chained/migrated rows and PCTFREE

Hi Frederic,

The answer to your question is all blocks are potentially affected although your second option is kinda close except that the status of existing blocks can change (hence it's a bit ambiguous).

The question though if you have migrated rows and you change the pctfree (which all sounds very reasonable) is what is the current "life cycle" of your table ? What I mean by that is how many of all expected rows are currently in the table ? How many more of the current rows are expected to increase in length and hence potentially migrate ? Are new rows likely to increase in length from their initial size by the same degree as existing rows ?

Taking a couple of scenarios.

If many many of the existing rows are still likely to increase in length, then increasing pctfree isn't going to be much chop. Why, because most of the these rows are already packed in like sardines and so are likely to migrate in the future. Your best bet is possibly to recreate the table with a higher pctfree to allow the existing rows room to grow.

If most of the existing rows have already done their growing and you are expecting many new rows in the future that are also likely to need space to grow, then recreating the table with a low pctfree (to pack in tightly the existing rows) and then altering the table and upping the pctfree (to allow new rows sufficient free space) is possibly the way to go.

If most of the existing rows have already increased in length and you are not expecting many new rows, then fixing the migrated rows and decreasing the pctfree is possibly the way to go as there is no further need to have this space for growth.

They're just some scenarios illustrating that it entirely depends what's the best course of action.

One final point. I notice your subject heading mentions chained/migrated rows. Rows that are chained due to large rows sizes can only be "fixed" by increasing the block size so I've ignored them.

Cheers

Richard

<Frederic Payant> wrote in message
news:9383su86jl9o4j6sp1fhm8pn4slusgeqke_at_4ax.com...
> Hi,
>
> I'm running ORACLE 817 on Solaris 8
>
> In production in a big table (15M rows, 3GB) I have around 5% migrated
> rows because of a badly initialized PCTFREE parameter (actual value is
> 10 and should be 20.
>
> I will change this value (alter table xxx pctfree 20;)
> My question is : what will be the effect on actual table and existing
> or future blocks
> Which blocs will be affected :
> -> all blocs
> -> existing blocs whose actual free space is < 20% and new ones
> -> only new ones
> -> none ??
> In the last hypothese, this mean that I must rebuild my table. (in
> the third one too, because now this table is accessed quite only in
> update(very few deletes and inserts)
>
>
> And a correlated more question :
> how is it possible to know, bloc by bloc, the state of free space ?
>
>
> Thanks in advance
> Regards
> Frederic PAYANT - junior DBA ;-)
Received on Thu Oct 31 2002 - 23:25:25 CST