about pct_free avg_space

From: paul <nomail_at_nomail.com>
Date: Mon, 31 Mar 2008 15:19:54 +0200
Message-ID: <C18A75549EB5DA11A92900805F0DA7FA049D208B@dun-com-news02.nord.usinor.com>

Hi,
Oracle 9.2.0.6 on AIX 5.3

I have a problem that i don't understand

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 ?

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 ?

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

thanks Received on Mon Mar 31 2008 - 08:19:54 CDT