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 tabledeclare
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 tableSQL> 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.
