Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: estimation of table size in oracle 9i
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