Re: about pct_free avg_space

On Mar 31, 10:18 am, "paul" <nom..._at_nomail.com> wrote:
> >> 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?
>
> Well i've just made blocks*8192 / num_rows*avg_row_len
> And based on the fact that when pctfree is 0, there are only 6545 blocks
> and when pctfree 10 there are  11832 blocks
> I assumed that the block are roughly 50% full
> Maybe this is not the good way but this is not the only table in the
> database who is in this case.
>
> But if i understand your statement that "avg_space reports the
> AVERAGE free space over all of the populated data blocks"
> In my case, the avg_space is 3826/8192 for all used blocks and that means
> 46% free space for all used blocks
>
>
>
>
>
>
>
> >> 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?
>
> same as above
>
>
>
>
>
> >> ps: The segment management is manual, i have tested with auto and it's
> >> the
> >> same values
>
> >> thanks
>
> >David Fitzjarrell- Hide quoted text -
>
> - Show quoted text -- Hide quoted text -
>
> - Show quoted text -- Hide quoted text -
>
> - Show quoted text -

I misstated, I think, the nature of avg_space; avg_space reports the average free space of blocks allocated to the table, so this includes populated and non-populated blocks. Thus your assumption about your block population percentage is likely wrong as an empty block, allocated to the table, will increase the average free space per block value.

The total number of blocks allocated to the table is BLOCKS + EMPTY_BLOCKS, and it's this total which is used to calculate the average free space in a block. Additionally the AVG_ROW_LEN is just that, the AVERAGE row length; some will be longer, some shorter, and data block population depends upon the size of the row being inserted into that block and the space available in that block for inserted data. If a long row, when inserted into a block, will 'overrun' the PCTFREE allocation the row goes to the next available block, and thus your population percent may be less than the 90% you envision. I still believe this percentage to be much higher than you estimate. Given an 8192 byte block size one has, roughly, 8000 bytes available for data storage. Knowing that 10% of that must be reserved for updates reduces that total to 7200 bytes. Using the average row length calculated by Oracle the number of rows per populated block should be 7200/998 which would be 7 rows per block. The AVERAGE number of rows per block in your database is roughly 4, but you can't take that average and extrapolate the results you've attempted to report as some blocks will house more than 4 rows, and some less. You might want to see exactly how many rows are in each block:

select dbms_rowid.rowid_block_number(rowid) as datarow, count(*) rows_per_block
from procp
group by dbms_rowid.rowid_block_number(rowid);

You may be surprised as to how full many of those data blocks are.

David Fitzjarrell Received on Mon Mar 31 2008 - 10:58:35 CDT