Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.tools -> Re: Table Sizing Estimation

Re: Table Sizing Estimation

From: Mark D Powell <markp7832_at_my-deja.com>
Date: Fri, 10 Nov 2000 16:40:02 GMT
Message-ID: <8uh8d0$6r4$1@nnrp1.deja.com>

In article <3A0B6D89.757F00C4_at_hket.com>,   Rayfil Cheung <rayfilcheung_at_hket.com> wrote:
> Hi,
> How I estimate the table sizing when certain no. of rows are expected.
> Thanks.
>

There are several ways to do this. Here is a fairly quick formula.

Abbreviations

AVIL = Available space in block to hold rows
OBS  = Oracle block size
RS   = Row size
Ovhd = Fixed plus variable block overhead
TBR  = Total blocks required

Expected size = (( RS * number of rows) / AVIL ) * OBS) / K or M where K = 1024 and M = 1048576

Figure RS as
 for varchar2 expected number of characters for column  for number 1 + floor(num digits / 2) + 1  for date use 7
 + 1 byte per column in row
 + 3 byte row overhead per row

Figure number of bytes for block as
pctfree = decimal value of pctfree parameter * OBS

The variable area is mostly made up of 23 bytes per initran area and 2 bytes per row for the row table entry. For 1 to 4 initrans I have calculated row overhead of 86 to 156 bytes so I just use a constant for this value. Try 113 to start.

Figure AVIL as OBS - ovhd - pctfree

Total bytes = number of expected rows * RS TBR = Total Bytes / AVIL
Expected Size = TBR * OBS / 1024 [for K]

This is one way and it is fairly quick and works pretty well. The formula can be improved by adjusting the variable area size for the number of initrans and for the number of expected rows in the block, but using a constant works well for us.

--
Mark D. Powell  -- The only advice that counts is the advice that
 you follow so follow your own advice --


Sent via Deja.com http://www.deja.com/
Before you buy.
Received on Fri Nov 10 2000 - 10:40:02 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US