Re: about pct_free avg_space

>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

ok thank you for this detailed explanation, i think i understand better what's happening

ROWS_PER_BLOCK COUNT(ROWS_PER_BLOCK) for a total of 6545 blocks pctfree 0

-------------- ---------------------
             2                  5505
             3                   178

ROWS_PER_BLOCK COUNT(ROWS_PER_BLOCK) for a total of 11832 blocks pctfree 10

-------------- ---------------------
             1                 10285
             2                   484

I am in a case where the rows are 4k average and the avg_row_len is biased because there must be several small records

So base on a fact that these tables are mainly for inserts (very few updates), is it better to set the pctfree to 0 ? Received on Tue Apr 01 2008 - 10:12:18 CDT