Monday, September 17, 2012

PCTFREE and Rows per Block

For tablespaces with ASSM only the pctfree parameter would have any affect on the data storage. From Oracle database concept document "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. Thus, PCTFREE is important for preventing row migration and avoiding wasted space."
Amount of rows that could be stored in a block is affected by the value set for pctfree. If the pctfree is high the row count in a block goes down (useful in reducing hot blocks) and when pctfree is low the row count inside the block goes high (useful for full table scans). Size of the block and the length of the row also plays a part in the row count inside the block.
Following is a simple test to illustrate this point. Two tables are created identical in all aspects except the pctfree value. Both reside in a tablespace which has a block size of 2K.
create table pct0 (id varchar2(100)) tablespace tbs2k pctfree 0;
create table pct99 (id varchar2(100)) tablespace tbs2k pctfree 99;

SQL> select table_name,pct_free from user_tables where table_name in ('PCT0','PCT99');

TABLE_NAME                       PCT_FREE
------------------------------ ----------
PCT99                                  99
PCT0                                    0
Following PL/SQL code block will insert 100 rows to each table
declare
x varchar2(40);
begin
  for i in 1 .. 100
    loop
    x := dbms_random.string('x',36);
    insert into pct0 values (x);
    insert into pct99 values (x);
    commit;
  end loop;
end;
/
There are two ways to get the row count inside a block either by using dbms_rowid package or decoding the rowid.
Using dbms_rowid package on pct0 table
SQL> select DBMS_ROWID.ROWID_OBJECT(rowid) "OBJECT",
  2            DBMS_ROWID.ROWID_RELATIVE_FNO(rowid) "FILE",
  3             DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid) "BLOCK",count(*) from pct0
  4             group by DBMS_ROWID.ROWID_OBJECT(rowid), DBMS_ROWID.ROWID_RELATIVE_FNO(rowid), DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid);

    OBJECT       FILE      BLOCK   COUNT(*)
---------- ---------- ---------- ----------
    103409         14     206983        100
which tells that all the rows are in one block. Querying the pct99 table
SQL> select DBMS_ROWID.ROWID_OBJECT(rowid) "OBJECT",
  2            DBMS_ROWID.ROWID_RELATIVE_FNO(rowid) "FILE",
  3             DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid) "BLOCK",count(*) from pct99
  4             group by DBMS_ROWID.ROWID_OBJECT(rowid), DBMS_ROWID.ROWID_RELATIVE_FNO(rowid), DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid);

    OBJECT       FILE      BLOCK   COUNT(*)
---------- ---------- ---------- ----------
    103411         14     206998          1
    103411         14     207007          1
    103411         14     207014          1
    103411         14     207017          1
    103411         14     207041          1
    103411         14     207049          1
..
..
..
    OBJECT       FILE      BLOCK   COUNT(*)
---------- ---------- ---------- ----------
    103411         14     207098          1

100 rows selected.
which tells that each row is stored in single block and 100 blocks are needed to store all the rows.
select rows_per_block,count(*) from (select substr(rowid,10,6) as block,count(*) as rows_per_block 
from pct0 group by substr(rowid,10,6)) group by rows_per_block order by 1;

ROWS_PER_BLOCK   COUNT(*)
-------------- ----------
           100          1


select rows_per_block,count(*) from (select substr(rowid,10,6) as block,count(*) as rows_per_block 
from pct99 group by substr(rowid,10,6)) group by rows_per_block order by 1;

ROWS_PER_BLOCK   COUNT(*)
-------------- ----------
             1        100
As more blocks are used the space usage will also increase compared to pct0 table above.
SQL> select segment_name as table_name, bytes/1024 as "Size KB" from user_segments where segment_name IN ('PCT0','PCT99');

TABLE_NAME    Size KB
---------- ----------
PCT0               64
PCT99             896
Multiple factors must be considered when setting the pctfree value such as the trade-offs between increase in storage vs reduction in IO (in FTS), the nature of access (FTS vs single block access) to the segment.