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: estimation of table size in oracle 9i

Re: estimation of table size in oracle 9i

From: Mark D Powell <Mark.Powell_at_eds.com>
Date: Sat, 23 Jun 2007 13:37:52 -0700
Message-ID: <1182631072.802366.30370@o61g2000hsh.googlegroups.com>


On Jun 23, 1:14 pm, DA Morgan <damor..._at_psoug.org> wrote:
> dkornyus..._at_gmail.com wrote:
> > Please advice how to estimate in advance phisycal size of table in 9i
> > or give a reference on dbf (oracle data file) format. I tried to test
> > some scenarios but I got quite confused cause even for number fields
> > size of table in bytes (measured by dba_extents) depends on values in
> > fields
> > For example if I create table
>
> > create table t_num (t number,t1 number,t2 number,t3 number)
>
> > and insert 1048576 of records
>
> > insert into t_num values (1078,2000,3001,238)
>
> > table size is 24 117 248
>
> > if I'm inserting same number of records (definitely I dropping table &
> > create it again before experiment)
>
> > insert into t_num values (1077,2001,3002,237)
>
> > table size is 25 427 968
>
> > how this can be explained?
>
> The calculation is in the docs athttp://tahiti.oracle.com:Look it up.
>
> Possibly in 9i but definitely in 10g and above use the DBMS_SPACE
> built-in package's CREATE_TABLE_COST procedure.
> --
> Daniel A. Morgan
> University of Washington
> damor..._at_x.washington.edu (replace x with u to respond)
> Puget Sound Oracle Users Groupwww.psoug.org- Hide quoted text -
>
> - Show quoted text -

Dkomyous, I would like to point out that dba_extents and dba_segments show the allocated size of an object so depending on the tablespace space allocation method chosen: locally managed with uniform extents, locally managed with auto-allocate extent size, or dictionary management.

In the absence of valid test data you can use the following logic to get reasonable estimates for the size of a table:
>>

Oracle Table Sizing Estimation 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 where p = number of ditits and s = 0 for positive and 1 for negative

     round((( length((p) + s) / 2)) + 1
 for date use 7
 + 1 byte per column in row
 + 3 byte row overhead per row
[Or use the dba_tables.avg_row_len value]

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

When you have valid test data or some real data and your are trying to estimate a future size then substitute the actual row length and overhead that dbms_stats show you via dba_tables.avg_row_len and use dbms_space as Daniel suggested to get your values.

HTH -- Mark D Powell -- Received on Sat Jun 23 2007 - 15:37:52 CDT

Original text of this message

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