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

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

Re: Table Sizing Estimation

From: sp <sdpant_at_yahoo.com>
Date: Sat, 11 Nov 2000 02:29:20 GMT
Message-ID: <4a2P5.1623$A26.68380@sjc-read.news.verio.net>

Or you can use T.O.A.D.
HTH
Sp
"Mark D Powell" <markp7832_at_my-deja.com> wrote in message news:8uh8d0$6r4$1_at_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 - 20:29:20 CST

Original text of this message

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