Oracle Tips by Burleson
Sizing an Oracle9i
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
No allowance is made here for changes to
PCTFREE or PCTUSED, due to insert, delete, or update activity. Thus,
this reflects a best-case scenario, that is, when users insert rows
without performing deletes or updates.
Calculating space required by
nonclustered tables is a five-step process:
1. Calculate the total block header
2. Calculate the available data
space per data block.
3. Calculate the space used per
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
A Simple Sizing Example
Let’s take a more detailed look at the steps
using a simple example.
INITRANS. The initial number of transaction entries allocated to the
Step 1: Calculate the Total Block Header
The space required by the data block
header is the result of the following formula:
See Code Depot
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
KCBH is the block common
header; on NT with a 4-KB blocksize, this is 20.
UB4 is “either byte 4”; on NT
with a 4-KB blocksize ,this is 4.
KTBBH is the transaction
fixed-header length; on NT with a 4-KB blocksize, this is
KTBIT is transaction variable
header; on NT with a 4-KB blocksize, this is 24.
KDBH is the data header; on NT
with a 4-KB blocksize, this is 14.