Re: about pct_free avg_space

Comments embedded.
On Mar 31, 8:19 am, "paul" <nom..._at_nomail.com> wrote:
> Hi,
> Oracle 9.2.0.6 on AIX 5.3
>
> I have a problem that i don't understand
>

I can't see the 'problem'.

> I have a table with these values :
> PCTFREE 10
> PCTUSED 40
>
> And after alter table move and analyse compute stats
>
> Name : PROCP
> blocks : 11832
> num_rows : 50008
> avg_row_len : 998
> avg_space : 3826
> size (blocks*8) : 92.44Mo
> size (num_rows*avg_row_len) : 47.6Mo
>
> So why the blocks are not more used, they are ~50% Full ?
>

How did you arrive at this determination? avg_space reports the AVERAGE free space over all of the populated data blocks, and the number of blocks the table consumes is a function of many things, including the initial and next extent sizes as well as the PCTFREE and PCTUSED values. How have you determined, for each block, it's percent population?

> I have created a copy of this table with pctfree 0, here it seems correct to
> me, the block are full
> Name : PROCP_pctfree0
> blocks : 6545
> num_rows : 50008
> avg_row_len : 998
> avg_space : 410
> size (blocks*8) : 51.13Mo
> size (num_rows*avg_row_len) : 47.6Mo
>
> and another one with pctfree 30
> Name : PROCP_pctfree30
> blocks : 12379
> num_rows : 50008
> avg_row_len : 998
> avg_space : 4012
> size (blocks*8) : 96.71Mo
> size (num_rows*avg_row_len) : 47.6Mo
>
> Any idea ?
>

Again, how did you determine, for each block, the percent data population?

> ps: The segment management is manual, i have tested with auto and it's the
> same values
>
> thanks

David Fitzjarrell Received on Mon Mar 31 2008 - 09:36:54 CDT