Re: Estimate Oracle Table Size

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 Fri Jan 03 2003 - 22:58:30 CST