How Does Oracle accurately calculate the row size?

From: http://space.itpub.net /? Uid-7656893-action-viewspace-itemid-664954

Step 1: Calculate the size of the entire block Header

The size of the data block header space is calculated by the following formula:

Space after headers (hsize) = db_block_size-kcbh-ub4-ktbbh-(initrans-1) * ktbit)-kdbh

Some parameters are described as follows:

Db_block_size: Database block size, which can be obtained from the V $ parameter view.

Kcbh, ub4, ktbbh, ktbit, and kdbh are constants. The values of these definitions can be obtained from the V $ type_size view.

Initrans is the number of initialization transactions allocated to the table, which can be obtained from the ini_trans field in the user_tables table.

Step 2: Calculate the available data space of each data block

The space reserved for each data block is specified by the pctfree parameter. Therefore, the formula is as follows:

Available data space (availspace) = Ceil (hsize * (1-pctfree/100 ))

-Kdbt

Some parameters are described as follows:

Ceil is the smallest integer greater than or equal to n.

Pctfree is the space reserved in the table for update operations. It can be obtained from the pct_free field in the user_tables table.

Kdbt is a constant. The defined size can be obtained from the V $ type_size view. If you cannot find the kdbt size, use the size defined by ub4 instead.

Step 3: Calculate the space used by each row

The space used by each row is calculated in multiple steps.

First, calculate the column size, including the length of the byte:

Column size including byte length = column size + (1, if column size <250, else 3)

You can use experience to determine the size of a column or use statements to calculate the size of each column:

Select AVG (vsize (colname) from table_name;

Next, calculate the row size:

Rowsize = row header (3 * ub1) + sum of column sizes including length bytes

Finally, calculate the space used by each row:

Space used per row (rowspace) = max (ub1*3 + ub4 + sb2, rowsize) + sb2

Ub1, ub4, and sb2 are constants. The defined values can be obtained from the V $ type_size view.

When the space occupied by each row exceeds the available space of one data block, but it is still smaller than the space reserved by each data block for the update operation (for example, pctfree = 0 ), each row of records will still be stored in their own block.

When the size of each row exceeds the available space of each data block and no space is reserved for the update operation, this row of records will be linked to another block or more, therefore, the storage load is relatively high at this time.

Step 4: calculate the maximum number of records in a block

You can use the following formula to calculate the number of records that can be accommodated in a data block:

Number of rows in block = floor (availspace/rowspace)

Floor is the maximum integer smaller than N.

This process only provides a rough estimation of the size of a table, rather than accurate calculation. After roughly estimating the size of a table, we can use this information to determine the storage parameter initial when creating the table. When a table is used after it is created, the need for space is usually greater than the estimated value we calculated.