Oracle table size estimation

October 18, 2009 at 8:21 pm (Oracle) (Oracle Capacity Planning, Oracle table size estimation)

The following is an excerpt from http://www.itland.pl/en/knowledge_base/oracle/ I do not claim to be the author of this article.

Estimating table size

Estimating table size – calculation
Table size estimation is performed in a few steps.
First calculate database block header size with formula:

header_size = KCBH + 4bytes overhead + KTBBH + (INITRANS-1)*KTBIT + KDBH

where
KCBH, KTBBH, KTBIT, KDBH are sizes of the internal oracle structures and the size of these structures can be retrieved from V$TYPE_SIZE view :
KCBH – block header (20bytes)
KTBBH – transaction fixed header (48bytes)
INITRANS – table parameter defining number of transaction slots
KTBIT – transaction variable header (24bytes)
KDBH – data header (14bytes)

The next step is to calculate the number of available space in database block. The space is calculated with formula :

block_available_space = (block_size – header_size)*(1-PCTFREE/100) – KDBT

where
block_size – database block size as defined in V$PARAMETERS
header_size – database block header size
PCFREE – free space left in database block for further updates
KDBT – table directory entry (4bytes)

The next step is to calculate the number of space occupied by every row.
Every row utilizes space needed by column value and an additional overhead.
The overhead equals 1 byte or 3 bytes if the column size exceed 250bytes.
Additionally you should take into account row header overhead of 3 bytes.

The average column size can be calculated with VSIZE function which returns column size in bytes.
For example, the most frequently used datatype occupies following space :
CHAR(1) – 1 byte + 1 byte data length overhead
NUMBER(1) – 2 bytes + 1 byte data length overhead
DATE – 7 bytes + 1 byte data length overhead
VARCHAR2(500) – number of chars + 3 bytes data length overhead

And finally the table size can be calculated with formula :

table_space = (number_of_rows/block_available_space/row_size)*block_size

(i.e) table_space = (number_of_rows*row_size/block_available_space)*block_size

Estimating table size – analysis
If the table is just created then you can generate statistics for it and get storage information from DBA_TABLES and then use it to estimate future table space usage