Estimating the Size of Objects

The next two sections will discuss in detail how to arrive at a fairly accurate estimate of the amount of space in bytes required to hold certain objects.

Table Sizing

The following calculations will help estimate the number of blocks and the total size in bytes required by table structures. The total size of the object as a result of these calculations is for initial sizing only.

Sizing the table object involves a series of calculations. Some of the data required will come from the table column specifications, some of the data will come from V$ tables, and the others will be from intermediate calculations. Table 19.1 is the Table Size Worksheet that can be used to aid in the collection of necessary information.

Calculating the average column size for each column in the table can be done via a query or by a manual calculation. Run the following query once for each column in the table: select avg(vsize(<COLUMN NAME>)) from <TABLE NAME>;. Record the sum of the returned values in the COLUMN SIZE area of the Table Size Worksheet (Table 19.1). A manual way to arrive at this total is to sum up the result of the following calculation: individual column size = column size + (1 unless the column size > 250, and then use 3). NUMBER field sizes are half the column size but not greater than 21. DATE fields take 7 positions. ROWID takes 10 positions. Use an average size for VARCHAR2 fields. Multiply any NULL fields by a percentage of the time they are expected to be NULL. All other fields use their referenced size value.

The next step is to populate the DB_BLOCK_SIZE, KCBH, KTBBH, KDBH, KTBIT, KDBT, SB2, UB1, and UB4 values. The following two queries will access this information from the Oracle V$ tables:

Select VALUE from V$PARAMETER where NAME = ‘db_block_size’;



select TYPE,TYPE_SIZE from V$TYPE_SIZE where TYPE in (‘ KCBH’, ‘KTBBH’,

‘KDBH’, ‘KTBIT’, ‘KDBT’, ‘SB2’, ‘UB1’,’UB4’);

Calculate

HEADER SIZE = (DB_BLOCK_SIZE–KTBBH–KCBH–UB4–(INITRANS -1))×(KTBIT–KDBH)

and record the result in the HEADER SIZE area of Table 19.1.

Calculate

AVAILABLE DATA SPACE = round(HEADER SIZE×(1–PCTFREE/100))–KDBT

and record the result in the AVAILABLE DATA SPACE area of Table 19.1.

Calculate

ROWSIZE = (UB1×3) + 10 + COLUMN SIZES

and record the result in the ROWSIZE area of Table 19.1.

Calculate

TOTAL ROWSIZE = (the lesser of ((UB1×3) + UB4 + SB2) or ROWSIZE) + SB2

and record the result in the TOTAL ROWSIZE area of Table 19.1.

---

Note:  
If TOTAL ROWSIZE is greater than DB_BLOCK_SIZE, each row of this object will chain. I recommend increasing the database block size.

---

Calculate

ROWS PER BLOCK = round(AVAILABLE DATA SPACE / TOTAL ROW SIZE)

and record your result in ROWS PER BLOCK area of Table 19.1.

Calculate

BLOCKS REQUIRED = Estimated Number of Rows / ROWS PER BLOCK

and record the result in the BLOCKS REQUIRED area of Table 19.1.

The last calculation is SIZE in bytes of TABLE = BLOCKS REQUIRED×DB_BLOCK_SIZE. Record this number in the last line of Table 19.1.

---

Tip:  
Make the initial extent size large enough to hold this table.

---

Index Sizing

The following calculations will help estimate the number of blocks and the total size in bytes required by index structures. The total size of the object as a result of these calculations is for initial sizing only.