Re: about pct_free avg_space

On Apr 1, 10:12 am, "paul" <nom..._at_nomail.com> wrote:
> >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 ?- Hide quoted text -
>
> - Show quoted text -

No, since you still have updates which could occur. I believe your current setting of 10 for PCTFREE is sufficient.

David Fitzjarrell Received on Tue Apr 01 2008 - 15:26:43 CDT