

ss
Oracle Tips by Burleson 
Sizing an Oracle9i
Nonclustered Table
The procedures in this section describe how
to estimate the total number of data blocks necessary to hold data
inserted to a nonclustered table. Typically, the space required to
store a set of rows that experience updates, deletes, and inserts
will exceed this calculated value. The actual space required for
complex workloads is best determined by analyzing an existing table;
it is then scaled by the projected number of future rows in the
production table. In general, increasing amounts of concurrent
activity on the same data block results in additional overhead (for
transaction records), so it is important that you take into account
such activity when scaling empirical results. (Spreadsheets are
available at the Wiley Web site for calculating table and index
size.)
Note
No allowance is made here for changes to
PCTFREE or PCTUSED, due to insert, delete, or update activity. Thus,
this reflects a bestcase scenario, that is, when users insert rows
without performing deletes or updates.
Calculating space required by
nonclustered tables is a fivestep process:
1. Calculate the total block header
size.
2. Calculate the available data
space per data block.
3. Calculate the space used per
row.
4. Calculate the total number of
rows that will fit in a data block.
5. With the rows/block data,
calculate the total number of data blocks and convert to kilo or
megabytes.
A Simple Sizing Example
Let’s take a more detailed look at the steps
using a simple example.
Step 1: Calculate the Total Block Header
Size
The space required by the data block
header is the result of the following formula:
See Code Depot
where:
DB_BLOCK_ SIZE. The database
blocksize with which the database was created. It can be viewed
in the V$PARAMETER view by selecting:
See Code Depot
KCBH, UB4, KTBBH, KTBIT, KDBH. Constants
whose sizes you can obtain by selecting from entries in the
V$TYPE_SIZE view.

KCBH is the block common
header; on NT with a 4KB blocksize, this is 20.

UB4 is “either byte 4”; on NT
with a 4KB blocksize ,this is 4.

KTBBH is the transaction
fixedheader length; on NT with a 4KB blocksize, this is
48.

KTBIT is transaction variable
header; on NT with a 4KB blocksize, this is 24.

KDBH is the data header; on NT
with a 4KB blocksize, this is 14.
INITRANS. The initial number of transaction entries allocated to the
table.
www.oraclescript.com 