Hi Bert & DA
Thanks for the reply. IT surely does help. Actually this estimate is
needed prior to database creation so I can not use Queries method. I
purely need math calculations/formulas. So method 2 is good. I was
really looking for some ORacle documentation for 8i or 9.2 which might
mention stepwise details just like one ORacle 8.0 Administrator's
guide.
Thanks,
TMR
"Bert Bear" <bertbear_at_NOSPAMbertbear.net> wrote in message news:<WRtR9.1024$6a5.567530589_at_newssvr12.news.prodigy.com>...
> TMR:
>
> Just some stuff I found via Google.
>
> http://www.extracon.com/office/orascript.htm#sizer
> Determine the Average Row Size of a Table
> The table sizing calculation detailed by Oracle in the Oracle 7 Server
> Administrator's Guide requires the average length, in bytes, of each row.
> This is not all that straightforward to work out as...
> a.. VARCHAR2 columns take up space according to the number of characters
> they actually contain, rather than their maximum length.
> b.. NUMBER columns consume space according to their binary value, and also
> seem to drop surplus bytes when they hold small numbers.
> However, if you can load a prototype table with a sample of the real data
> you can use the following routine to determine this value empirically:
>
> ACCEPT table PROMPT "Table name? "
>
> SET head OFF
> SET verify OFF
> SET feedback OFF
> SET newpage 0
> SET pagesize 0
>
> SPOOL temp.sql
> PROMPT SELECT AVG(
>
> SELECT 'NVL(VSIZE('||LOWER(column_name)||'),0) +'
> FROM user_tab_columns
> WHERE table_name = UPPER('&&table')
> /
> PROMPT 0) "Average Row Size"
> PROMPT FROM &&table
> PROMPT /
> SPOOL OFF
>
> SET head ON
> SET verify ON
> SET feedback ON
> SET newpage 1
> SET pagesize 15
> START temp.sql
> HOST rm temp.sql
> This value can be fed into the following formula to estimate an unclustered
> table's storage requirements:
> Oracle Block Size × No. of Rows × (Average Row Size + 5)
>
> (Oracle Block Size - 62 - (24 × INITTRANS)) × (1 - (PCTFREE/100))
>
>
> Alternatively, use the script shown in the next section to size the table
> more precisely.
>
> Back to the top
>
>
> ----------------------------------------------------------------------------
> ----
>
> Estimate the Storage Requirements of a Table
> This script uses various constants stored in V$TYPE_SIZE and information
> entered by the user to estimate the space required to store a particular
> (unclustered) table:
>
> ACCEPT rowsize PROMPT "Average row size(bytes): "
> ACCEPT rowct PROMPT "Number of rows: "
> ACCEPT inittrans PROMPT "INITTRANS [1]: " DEFAULT 1
> ACCEPT pctfree PROMPT "PCTFREE [10]: " DEFAULT 10
>
> SET verify OFF
>
> SELECT TO_NUMBER(db_block_size.value)
> * CEIL(&rowct
> / FLOOR(CEIL((TO_NUMBER(db_block_size.value)
> - (kcbh."SIZE" + ub4."SIZE" + ktbbh."SIZE" + kdbh."SIZE"
> + ((&inittrans - 1) * ktbit."SIZE"))) * (1 -
> (&pctfree/100)))
> / ((ub1."SIZE" * 3) + GREATEST(ub4."SIZE" +
> sb2."SIZE",&rowsize) + sb2."SIZE"))) SizeInBytes
> FROM v$type_size kcbh,
> v$type_size ub1,
> v$type_size ub4,
> v$type_size sb2,
> v$type_size ktbbh,
> v$type_size kdbh,
> v$type_size ktbit,
> v$parameter db_block_size
> WHERE kcbh.type = 'KCBH'
> AND ub1.type = 'UB1'
> AND ub4.type = 'UB4'
> AND sb2.type = 'SB2'
> AND ktbbh.type = 'KTBBH'
> AND kdbh.type = 'KDBH'
> AND ktbit.type = 'KTBIT'
> AND UPPER(db_block_size.name) = 'DB_BLOCK_SIZE'
> /
> http://www.dbaoncall.net/references/ht_estimate_table_size.html
>
> September 24, 2000
> How to Estimate Table Size
> By Alexander Geldutes
>
> Suppose, you have to create a new table and insert some data into it, or
> table already exists and you will make inserts. You want to estimate the
> final size of your table, for example you want to set correct storage
> parameters.
>
> You can do it in two ways:
>
>
> a.. Using internal statistics.
>
>
> b.. Math estimation.
> It's very rough estimation.
>
>
> I-st method - using statistics
>
>
> 1.. Analyze statitics for the table.
>
> From SQLPlus run the following command:
>
> ANALYZE TABLE <table_name> COMPUTE STATISTICS;
>
> or
>
> ANALYZE TABLE <table_name> ESTIMATE STATISTICS;
>
>
>
> 2.. Make select form DBA_TABLES, ALL_TABLES or USER_TABLES dictionary
> view.
>
> SELECT avg_row_len
> FROM dba_tables
> WHERE table_name = '<table_name>';
>
> where
>
> avg_row_len - is an average row length (in bytes), including row overhead;
>
>
> = avg_row_len * number_of_rows * (1 + PCTFREE/100) * 1.15
>
> where number_of_rows - expected number of rows in you table;
> 1.15 - just in case :).
>
> NOTE: If it's a new table or it's empty, you may insert some "demo" records
> before analyzing the table.
>
>
> II-nd method - using math estimation
> 1.. Calculate maximum row length of the table based on the following
> facts:
>
> CHAR( n ), VARCHAR2( n ) - n bytes
>
> DATA - 7 bytes;
>
> NUMBER( p, s ) = floor( (p+1)/2 ) + 1, if it's a negative number - add one
> byte (+1).
>
> For example, Oracle uses 3 bytes to store number: 512 (NUMBER(3)).
>
>
> 2.. <ESTIMATED_TABLE_SIZE> =
> MAX_ROW_LENGTH * (1 + PCTFREE/100) * number_of_rows * 1.15
>
> where 1.15 - just in case.
>
>
> "Trupti" <trajparia_at_hotmail.com> wrote in message
> news:7abe1789.0301031852.2e013448_at_posting.google.com...
> > Hi,
> > I am trying to get table size estimate based on number of rows. In
> > Oracle 8.0 DBA guide there was detailed formula/steps for schema
> > objects space requirements. Can anyone tell me if there is one for
> > Oracle 8i or 9.2. What article can I use as basis to calculate table
> > size estimates.
> >
> > Thanks,
> > TMR
>
>
> begin 666 dot.gif
> B1TE&.#EA`0`!`( ``````/___RP``````0`!```"`40`.P``
> `
> end
Received on Tue Jan 07 2003 - 11:24:31 CST
