Skip to Main Content
  • Questions
  • Questions about the upper limit of records in one database block

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Joe.

Asked: August 16, 2016 - 3:44 am UTC

Last updated: August 22, 2016 - 10:26 am UTC

Version: 11.2.0.4

Viewed 1000+ times

You Asked

Hi, team
I was told that the last 3 characteristics of ROWID stands for the row number of database block. Theoretically, every database block could store as many as 65536 rows.
I was wondering how many records could stores in one block (with the size of 8K) practically. Therefore, I did a testment this morning:

SQL> create table CH1(c char(1)) pctfree 0;

Table created.

SQL> insert into CH1 select 'c' from dual connect by level<3000;

2999 rows created.

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

BN COUNT(*)
---------- ----------
140574 733
140571 67
140572 733
140573 733
140575 733

SQL> create table CH5(c char(5)) pctfree 0;

Table created.

SQL> insert into CH5 select 'ccccc' from dual connect by level<3000;

2999 rows created.

SQL> select dbms_rowid.rowid_block_number(rowid) bn,count(*) from CH5 group by d
bms_rowid.rowid_block_number(rowid);

BN COUNT(*)
---------- ----------
140579 67
140582 733
140580 733
140581 733
140583 733


SQL> create table CH6(c char(6)) pctfree 0;

Table created.

SQL> insert into CH6 select 'ccccc' from dual connect by level<3000;

2999 rows created.

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

BN COUNT(*)
---------- ----------
140589 672
140588 672
140587 311
140591 672
140590 672

SQL>

According to my testment, it shows that only 733 records could be stored in one 8K block at most.
Here's my question:
1) Is it true that 733 records is the upper limit?
2) I wanna know where the rest space (8K - 5 byte*733 = 4527 byte) goes away?
3) Should the block HEADER(common and variable header, table directory, row directory and ITL slots) consumes all the rest space in the block? If not, could you please figure out what else factors could consume the rest space?
4) Suppose that the size of block is 32K, could the maximum of records in one block up to 65536?

and Chris said...

MOS note 10640.1 discusses block calculation in detail. Here's a brief summary.

First you need to calculate the space for the block header. This is:

block header = fixed header + variable transaction header + 
                 table directory + row directory 
 
     where: 
        fixed header = 57 bytes  (the 4 bytes at the end of the block have  
                                  already been taken account of in the 24 bytes 
                                  for the cache header) 
 
        variable transaction header = 23 * i  where i is the value of INITRANS 
                                              for the table, or can grow 
                                              dynamically up to MAXTRANS. 
 
        table directory = 4 * n    where n is the number of tables. 
                                   n = 1 for non-clustered tables. 
 
        row directory = 2 * x    where x is the number of rows in the block. 


So for a default table this is 84 bytes (57 + (23 * 1) + (4 * 1)) plus 2 * number of rows.

How many rows fit in the remaining space is dependent on the length of each row. This is:

- 3 bytes for the row header
- Plus the column length overhead. This is 1 byte for numbers, dates and char/varchar columns up to 250 bytes long. Otherwise it's three bytes.
- The length of the data itself

As you calculated, this gives 5 bytes for each row in your table. But you also need to add an extra 2 bytes for the row directory. So each row is 7 bytes long. This gives the formula:

Rows/block = (8192 - 84) / 7 = 1,158 (rounding down).

You're missing about 400 rows! Something must be wrong...

Indeed, we've overlooked something:

Note: The minimum row size for a non-clustered table is 9 bytes. Therefore, if 
      the calculated value for an average row size is less than this absolute
      minimum row size, use the minimum value as the average row size. 


Thus the formula is really:

Rows/block = (8192 - 84) / 11 = 737 (rounding down).

In theory you could fit 737 rows in an 8k block. This is close to the 733 you've observed. For a 32k block in theory you could have:

Rows/block = (32,768 - 84) / 11 = 2,971

Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Joe Huang, August 16, 2016 - 9:42 am UTC

Thanks Chris,
This is helpful, thanks a lot.

Compression?

A reader, August 19, 2016 - 4:26 pm UTC

How does Advanced compression affect this calculation?
Connor McDonald
August 20, 2016 - 3:20 am UTC

SQL> create table CH1(c char(1)) pctfree 0 compress;

Table created.

SQL> insert /*+ APPEND */ into CH1 select 'c' from dual connect by level<30000;

29999 rows created.

SQL> commit;

Commit complete.

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

        BN   COUNT(*)
---------- ----------
    528578        730
    528580        730
    528581        730
...



Something about ITL slots?

Joe Huang, August 22, 2016 - 7:52 am UTC

SQL> create table CH1(c char(1)) pctfree 0 compress;

表已创建。

SQL> create table CH2(c char(1)) pctfree 0 compress;

表已创建。

SQL> create table CH3(c char(1)) pctfree 0 NOcompress;

表已创建。

SQL> insert /*+ APPEND */ into CH1 select 'c' from dual connect by level<3000;

已创建 2999 行。

SQL> insert /*+ NO_APPEND */ into CH2 select 'c' from dual connect by level<3000;

已创建 2999 行。

SQL> insert /*+ APPEND */ into CH3 select 'c' from dual connect by level<3000;

已创建 2999 行。

SQL> commit;

提交完成。

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

        BN   COUNT(*)
---------- ----------
      9685        730
      9684        730
      9687         79
      9683        730
      9686        730


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

        BN   COUNT(*)
---------- ----------
      9691         67
      9695        733
      9693        733
      9694        733
      9692        733

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

        BN   COUNT(*)
---------- ----------
    140630        730
    140629        730
    140627        730
    140628        730
    140631         79



It probably matters about APPEND, rather than COMPRESS.
I was curious about this too, and tried to dump the blocks out to trace, then compared the differences between the trace files. And the ITL just caught my eyes on it.

SQL> show user
USER 为 "Z"
SQL> select segment_name,file_id from dba_extents where owner='Z' and segment_name in ('CH1','CH2');

SEGMENT_NA    FILE_ID
---------- ----------
CH1                 6
CH2                 6

SQL> alter system dump datafile 6 block 9686;

系统已更改。

SQL> exit

....

SQL> alter system dump datafile 6 block 9692;

系统已更改。

SQL> exit


The APPEND consumes 3 ITL slots (which means it takes 3*24 bytes=72 bytes), as shown bellow:

Block header dump: 0x018025d5
Object id on Block? Y
seg/obj: 0x1adc6 csc: 0x01.ee3de15 itc: 3 flg: E typ: 1 - DATA
brn: 0 bdba: 0x18025d0 ver: 0x01 opc: 0
inc: 0 exflg: 0

Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0005.00e.000010fc 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x03 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000

bdba: 0x018025d5
data_block_dump,data header at 0xbb52e7c
===============
tsiz: 0x1f80
hsiz: 0x5c6
pbl: 0x0bb52e7c
76543210
flag=--------
ntab=1
nrow=730


While the NOAPPEND consumes 2 ITL slots (which means it takes 2*24 bytes=48 bytes)

Block header dump: 0x018025dc
Object id on Block? Y
seg/obj: 0x1adc7 csc: 0x01.ee3de4b itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x18025d8 ver: 0x01 opc: 0
inc: 0 exflg: 0

Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x000a.017.00000e6e 0x01400443.03fc.1c --U- 733 fsc 0x0000.0ee3de4d
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000

bdba: 0x018025dc
data_block_dump,data header at 0xbb55064
===============
tsiz: 0x1f98
hsiz: 0x5cc
pbl: 0x0bb55064
76543210
flag=--------
ntab=1
nrow=733

"The minimum row size for a non-clustered table is 9 bytes. Therefore, if the calculated value for an average row size is less than this absolute minimum row size, use the minimum value as the average row size".
I am not quite good at this, but plz allow me to guess this boldly: the ITL(24 bytes) could be the reason(at least one of the reasons) why the APPEND stores 3 rows(3*9bytes ~3*11 bytes) less than the NOAPPEND one.

Connor McDonald
August 22, 2016 - 10:26 am UTC

Nice investigation.

I dont have access currently to a HCC-enabled system, but that would also be an interesting area to explore.

More to Explore

DBMS_ROWID

More on PL/SQL routine DBMS_ROWID here