# Re: about pct_free avg_space

Date: Tue, 1 Apr 2008 13:26:43 -0700 (PDT)

Message-ID: <a29f660e-7c04-4b33-8dea-aad22fa0654c@d62g2000hsf.googlegroups.com>

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