Home » RDBMS Server » Performance Tuning » Estimating DB Size of Prodution Database
Estimating DB Size of Prodution Database [message #65242] Wed, 30 June 2004 19:19 Go to next message
Messages: 2
Registered: March 2004
Junior Member
Hi All,

  So we were trying to estimate the size of production database using two appraoches:

1) Analyze the schema and use the avg_row_len column

2) use the vsize function on each table column

3) Using a vsize on table only

(Select avg(vsize(table_name)) from dba_tables

But each of these three tables is giving drastically different statistics.

So I wanted to know:

1) which approach is most accurate coz it is very important to know this size coz that wud govern the hardware purchae of the organization.

I am enclosing the script which we used for calculating:


set serveroutput on size 100000
set verify off


c1 integer;
rows_processed integer;
colsize number DEFAULT 0;
avgrow number DEFAULT 0;
ind_avgrow number DEFAULT 0;
l_sql varchar2(2000);

UB1 CONSTANT number :=  1; /* Get from v$type_size for your platform */
UB4 CONSTANT number :=  4; /* Get from v$type_size for your platform */
SB2 CONSTANT number :=  2; /* Get from v$type_size for your platform */

CURSOR get_tabs IS SELECT distinct tablename, max(dt.avg_row_len) avg_row_len from
     ent_table et, dba_tables dt
                     upper(dt.table_name) = upper(et.tablename)
     and et.avg_row_len is null
     group by tablename;

CURSOR get_cols (p_tab_name all_tables.table_name%TYPE
  ) IS
                FROM all_tab_columns
                WHERE table_name = p_tab_name
                AND data_type NOT IN ( 'CLOB', 'BLOB', 'LOB', 'NCLOB');


FOR tab_rec IN get_tabs LOOP


avgrow := UB1*3; /* row header */

IF tab_rec.avg_row_len IS NULL OR tab_rec.avg_row_len  = 0 THEN

   -- Try and calculate the average row length using VSIZE

   FOR get_col_rec IN get_cols (tab_rec.tablename)

     c1 := dbms_sql.open_cursor;
     l_sql := 'select round(avg(nvl(vsize(' || get_col_rec.column_name ||
               '),0)))' || ' from ' ||
     dbms_sql.define_column(c1, 1, colsize);

     rows_processed := dbms_sql.execute_and_fetch (c1);

     dbms_sql.column_value(c1, 1, colsize);

--dbms_output.put_line (' Column '||get_col_rec.column_name
  --                            ||' Col Size = '||to_char(colsize));

     dbms_sql.close_cursor (c1);
     avgrow := avgrow + colsize + SB2;


   avgrow := tab_rec.avg_row_len;


UPDATE ent_table
set avg_row_len = avgrow
where trim(upper(tablename)) = trim(upper(tab_rec.tablename));
   dbms_output.put_line ('Table '||tab_rec.tablename
                                ||' Avg Row Size = '||CEIL(to_char(avgrow)));


Re: Estimating DB Size of Prodution Database [message #65253 is a reply to message #65242] Mon, 05 July 2004 11:33 Go to previous message
Messages: 170
Registered: April 2002
Senior Member
I'm afraid is not correct whta you understood about VSIZE function.
Documentation states:
VSIZE returns the number of bytes in the internal representation of expr.

Ifo you try this:
select vsize('ORDER') FROM DBA_TABLES

the result will be 5
because it returns the lengh of the string 'ORDER'.
so, your query above

'select round(avg(nvl(vsize(' || get_col_rec.column_name ||
'),0)))' || ' from '

will return the lenght of every column of every table you select. Am I clear?...you are not geting the column data type lenght, just the lenght of the string name.

Best regards.....
Previous Topic: redo log contetnion
Next Topic: What is Faster? Bulk Inserts/or Direct Path Insert?
Goto Forum:

Current Time: Wed Feb 21 12:49:58 CST 2024