Impact of Row Length on actual PCTFREE

The PCTFREE storage parameter is essential to how the database manages free space. This SQL parameter sets the minimum percentage of a data reserved as free space for updates to existing rows. Oracle will not insert new rows into existing blocks if INSERTs would cause free space to fall below PCTFREE threshold.

It's important to note that Row Length has impact on actual PCTFREE and quite serious impact on actual PCTFREE for very large tables. If you have a table with row length comparable to the space reserved by PCTFREE, then your actual PCTFREE may be twice as  much and you could end up wasting lots of space, and impact performance of full table scans.

For example, let's say that you have a table with row length of 600 bytes. Block size is 8K. PCTFREE is 10. You may end up with approximately 800 + 600 = 1,400 bytes of free space within the data blocks. You would have expected 800 bytes of free space.

Let's prove the point with a table rebuild. In this example, table takes up more space after the rebuilds due to actual PCTFREE larger than designated PCTFREE.

Case I - Before Rebuild

JMEHTA HRP> SELECT PCT_FREE, BLOCKS, EMPTY_BLOCKS, AVG_ROW_LEN

  2  FROM DBA_TABLES

  3  WHERE TABLE_NAME = 'TAB1';

  PCT_FREE     BLOCKS EMPTY_BLOCKS AVG_ROW_LEN 

---------- ---------- ------------ -----------

        10      93997          378         613      

Case II - After the Rebuild with the same PCTFREE

JMEHTA HRP> SELECT PCT_FREE, BLOCKS, EMPTY_BLOCKS, AVG_ROW_LEN

  2  FROM DBA_TABLES

  3  WHERE TABLE_NAME = TAB1';

  PCT_FREE     BLOCKS EMPTY_BLOCKS AVG_ROW_LEN  

 ---------- ---------- ------------ ----------- 

        10     102589         1090         613       

Case II - After the Rebuild with the PCTFREE = 2

JMEHTA HRP> SELECT PCT_FREE, BLOCKS, EMPTY_BLOCKS, AVG_ROW_LEN

  2  FROM DBA_TABLES

  3  WHERE TABLE_NAME = TAB1';

  PCT_FREE     BLOCKS EMPTY_BLOCKS AVG_ROW_LEN 

---------- ---------- ------------ -----------

         2      93924            0         613         

As you could see, the same table with same PCTFREE after the rebuild took approximately 8,000 more blocks! You would have expected the table to take up less space.

It's advisable to use lower values of PCTFREE for tables with larger row lengths.