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 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 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 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 48.

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.

INITRANS. The initial number of transaction entries allocated to the table.