PCT_FREE & DBMS_REPAIR.SEGMENT_FIX_STATUS

Today while discussing with friend about PCT_FREE and its impact on the size of table, we did a small test on a table and below are the results of it.

Created a table test with pct_free 50

SQL> create table test pctfree 50 as select * from dba_objects where rownum<=1000;
Table created.

SQL> analyze table test compute statistics;
Table analyzed.

SQL> select table_name,blocks,pct_free,num_rows from user_tables where table_name='TEST';

TABLE_NAME                    BLOCKS      PCT_FREE   NUM_ROWS
------------------------------      ----------   --------------    ---------------
TEST                                       26                    50                  1000


SQL> select segment_name,block_id,blocks,BYTES from dba_extents where segment_name='TEST';

SEGMENT_NA   BLOCK_ID     BLOCKS    BYTES
----------                  ----------      ----------    ----------
TEST                        137                   8      65536
TEST                        145                   8      65536
TEST                        153                   8      65536
TEST                        161                   8      65536

////// block_id is the first block of the allocated 8 blocks in a extent.
////// 8 blocks  allocated on each extent
////// 65536 bytest is total size of 8 blocks i.e 65536 / 8 = 8192 bytes i.e.8kb size of each block.
////// Now we will check number of rows in each block for the test table.

SQL> select dbms_rowid.rowid_block_number(rowid) blk_num,count(*) from test group by
    dbms_rowid.rowid_block_number(rowid);

BLK_NUM   COUNT(*)
----------        ----------
       151             47
       152             42
       144             48
       147             48
       155             45
       157             44
       141             50
       148             48
       149             46
       143             48
       161             45
       162             32
       140             51
       142             48
       145             45
       146             46
       154             41
       158             49
       150             44
       156             43
       159             46
       160             44
22 rows selected.

//// Now we can see on a average around 40 rows are present in each block.
//// Now question is can we increase the number of rows in each block, so that number of used blocks and the size of table comes down.
///// Yes, remember we put pct_free as 50. Lets try to alter this number to 10 and then check the result.

SQL> alter table test pctfree 10;
Table altered.

SQL> conn /as sysdba
Connected.
SQL> exec dbms_repair.segment_fix_status('SCOTT','TEST');
PL/SQL procedure successfully completed.

////// It is important to execute the above procedure after changing the pct_free with sysdba privilege

SQL> conn scott/tiger
Connected.

SQL> alter table test enable row movement;
Table altered.

SQL> alter table test shrink space;
Table altered.

SQL> select dbms_rowid.rowid_block_number(rowid) blk_num,count(*) from test group by
    dbms_rowid.rowid_block_number(rowid);

BLK_NUM   COUNT(*)
    ----------     ----------
       144             94
       147             93
       141             95
       148             90
       149             91
       143             96
       140             97
       142             94
       145             91
       146             89
       150             70
11 rows selected.

////  On average around 90 rows are present in each block and used blocks  came down to 11 from 22.
//// Lets analyze the test table again to see the updated statistics.

SQL> analyze table test compute statistics;
Table analyzed.

SQL> select table_name,blocks,pct_free,num_rows from user_tables where table_name='TEST';

TABLE_NAME                         BLOCKS   PCT_FREE   NUM_ROWS
------------------------------       ----------  ------------    ---------------
TEST                                           11                10                   1000

///// 11 blocks compared to 26 earlier

SQL> select segment_name,block_id,blocks,BYTES from dba_extents where segment_name='TEST';

SEGMENT_NA   BLOCK_ID     BLOCKS      BYTES
-----------------   -------------     ------------    ----------
TEST                         137                     8      65536
TEST                         145                     8      65536

///// Our test was on a small table with 1000 rows, imagine a table with billion rows with pct_free 50%.  Careful with this parameter, there should be good reason for putting pct_free to high percentage.

*** Hope this will help someone.