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

Home -> Community -> Usenet -> c.d.o.misc -> Re: Estimate Oracle Table Size

Re: Estimate Oracle Table Size

From: DA Morgan <damorgan_at_exesolutions.com>
Date: Wed, 08 Jan 2003 08:15:29 -0800
Message-ID: <3E1C4EA0.2E89A5C2@exesolutions.com>


Trupti wrote:

> 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

Go to http://tahiti.oracle.com and search for information on the subject. You will find it in great volume and detail.

Daniel Morgan Received on Wed Jan 08 2003 - 10:15:29 CST

Original text of this message

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