Estimating Space Required by
Oracle Tips by Burleson Consulting
The procedure given here shows how to estimate
the initial amount of space required by a clustered set of tables.
This procedure only estimates the initial amount of space required for
a cluster. When using these estimates, note that the following factors
can affect the accuracy of estimations:
* Trailing nulls are not stored, nor is a
length byte. Inserts, updates, and deletes of rows, as well as tables
containing rows or columns larger than a single data block, can cause
chained row pieces. Therefore, the estimates you derive using the
following procedure may tend to be lower than the actual space
required if significant row chaining occurs.
* Once you determine an estimate for a table’s
size using the following procedure, you should add about 10 to 20
percent additional space to calculate the initial extent size for a
To estimate the space required by clusters,
perform the following general steps:
1. Calculate total block
header size and space available for table data.
2. Calculate the combined
average column lengths of the rows per cluster key.
3. Calculate the average row
size of all clustered tables.
4. Calculate the average
5. Calculate the total
number of blocks required for the cluster.
Let’s step through a more detailed example.
Step 1: Calculate Total Block Header Size and
Space Available for Table Data
The following formula returns the amount of
available space in a block:
space left in block after headers (hspace)
BLOCKSIZE -( KCBH - UB4 - KTBBH - (KTBIT*(INITTRANS - 1)) - KDBH)
The sizes of KCBH, KTBBH, KTBIT, KDBH, and UB4
can be obtained by selecting * from v$type_size table.
Then use the following formula to calculate
the space available (s_avail) for table data:
s_avail = (hspace*(1
– PCTFREE/100)) – (4*(NTABLES + 1) * ROWSINBLOCK)
BLOCKSIZE. The size of a data block.
INITTRANS. The initial number of
transaction entries for the object.
PCTFREE. The percentage of space to
reserve in a block for updates.
NTABLES. The number of tables in the
ROWS IN BLOCK. The number of rows in a
Note: Several calculations are required
to obtain a final estimate, and several of the constants (indicated by
an asterisk (*)) provided are operating system-specific. Your
estimates should not significantly differ from actual values. See your
operating system–specific Oracle documentation for any substantial
deviations from the constants provided in the above procedure.
Step 2: Calculate Space Required by a Row
To calculate this number, use step 3 from the
five-step procedure for estimating the space required by nonclustered
tables from Chapter 5, Section 5.7, “Sizing an Oracle9i Nonclustered
Table,” in. Make note of the following caveats:
* Calculate the data space required by an
average row for each table in the cluster. For example, in a cluster
that contains tables t1 and t2, calculate the average row size for
* Do not include the space required by the
cluster key in any of the above data space calculations. Make note of
the space required to store an average cluster key value for use in
step 5. For example, calculate the data space required by an average
row in table t1, not including the space required to store the cluster
Do not include any space required by the row
header (that is, the length bytes for each column); this space is
accounted for in the next step. For example, assume two clustered
tables are created with the following statements:
t1 (a CHAR(6), b DATE, c NUMBER(9,2))
CLUSTER t1_t2 (c);
CREATE TABLE t2 (c NUMBER(9,2), d CHAR(6))
CLUSTER t1_t2 (c);
Notice that the cluster key is column C in
Considering these sample tables, the space
required for an average row (D1) of table t1 and the space required
for an average row (D2) of table t2 is:
(space/average row) = (a + b) = (9 + 6) bytes = 15
D2 (space/average row) = (d) = 9 bytes
Step 3: Calculate Total Average Row Size
You can calculate the minimum amount of space
required by a row in a clustered table according to the following
= row header + Fn + Vn + Dn
row header. Four bytes per row of a
Fn. Total length bytes of all 250 bytes
or less. The number of length bytes required by each column of this
type is 1 byte.
Vn. Total length bytes of all columns
in table n that store more than 250 bytes. The number of length bytes
required by each column of this type is 3 bytes.
Dn. Combined data space of all columns
in table n (from step 2).
Tip: Do not include the column length
for the cluster key in variables F or V for any table in the cluster.
This space is accounted for in step 5.
For example, the total average row size of the
clustered tables t1 and t2 is as follows:
S1 = (4 + (1 * 2) + (3 * 0) + 15) bytes = 21 bytes
S = (4 + (1 * 1) + (3 * 0) + 9) bytes = 14 bytes
Tip: The absolute minimum row size of a
clustered row is 10 bytes and is operating system specific. Therefore,
if your calculated value for a table’s total average row size is less
than these absolute minimum row sizes, use the minimum value as the
average row size in subsequent calculations.
Step 4: Calculate Average Cluster Blocksize
To calculate the average cluster blocksize,
first estimate the average number of rows (for all tables) per cluster
key. Once this is known, use the following formula to calculate
average cluster block size:
block size (bytes) = ((R1*S1) + (R2*S2) + . . . + (Rn*Sn)) + key
+ Ck + Sk + 2Rt
Rn. The average number of rows in table
n associated with a cluster key.
Sn. The average row size in table n
(see step 3).
key header. This is 19.
Ck. The column length for the cluster
Sk. The space required to store average
cluster key value.
Rt. The total number of rows associated
with an average cluster key (R1 + R2 . . . + Rn). This accounts for
the space required in the data block header for each row in the block.
For example, consider the cluster that
contains tables t1 and t2. An average cluster key has one row per
table t1 and 20 rows per table t2. Also, the cluster key is of
datatype NUMBER (column length is 1 byte), and the average number is 4
digits (3 bytes). Considering this information and the previous
results, the average cluster key size is:
SIZE = ((1 *
21) + (20 * 14) + 19 + 1 + 3 + (2 * 21)) bytes = 368 bytes
Specify the estimated SIZE in the SIZE option
when you create the cluster with the CREATE CLUSTER command. This
specifies the space required to hold an average cluster key and its
associated rows; Oracle uses the value of SIZE to limit the number of
cluster keys that can be assigned to any given data block. After
estimating an average cluster key SIZE, choose a SIZE somewhat larger
than the average expected size to account for the space required for
cluster keys on the high side of the estimate.
To estimate the number of cluster keys that
will fit in a database block, use the following formula, which uses
the value you calculated in step 2 for available data space, the
number of rows associated with an average cluster key (Rt), and SIZE:
keys per block = FLOOR(available data space + 2R / SIZE + 2Rt)
For example, with SIZE previously calculated
as 400 bytes (calculated as 368 earlier in this step and rounded up),
Rt estimated at 21, and available space per data block (from step 2)
calculated as 1,742 – 2R bytes, the result is as follows:
keys per block = FLOOR((1936 – 2R + 2R) / (400 + 2 * 21))
= FLOOR(1936 / 442) = FLOOR(4.4) = 4
Note: FLOOR means round down.
Step 5: Calculate Total Number of Blocks
To calculate the total number of blocks for
the cluster, you must estimate the number of cluster keys in the
cluster. Once this is estimated, use the following formula to
calculate the total number of blocks required for the cluster:
# blocks =
CEIL(# cluster keys / # cluster keys per block)
Note: CEIL means round up.
If you have a test database, you can use
statistics generated by the ANALYZE command to determine the number of
key values in a cluster key. For example, assume that there are
approximately 500 cluster keys in the T1_T2 cluster:
T1_T2 = CEIL(500/3) = CEIL(166.7) = 167
To convert the number of blocks to bytes,
multiply the number of blocks by the data block size.
This procedure provides a reasonable
estimation of a cluster’s size, but not an exact number of blocks or
bytes. Once you have estimated the space for a cluster, you can use
this information when specifying the INITIAL storage parameter (the
size of the cluster’s initial extent) in your corresponding CREATE
Space Requirements for Clustered Tables in
Once clustered tables have been created and
are in use, the space required by the tables is usually higher than
the estimate derived by the procedure given in the previous section.
More space is required because of the method Oracle uses to manage
freespace in the database.
Code Depot for Full Scripts
||This is an excerpt
from Mike Ault, bestselling author of "Oracle
10g Grid and Real Application Clusters".
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot of Oracle tuning scripts.
Expert Remote DBA
BC is America's oldest and largest Remote DBA Oracle support
provider. Get real Remote DBA experts, call
BC Remote DBA today.