| Estimating DB Size of Prodution Database [message #65242] |
Wed, 30 June 2004 19:19  |
Anmolsakhi
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
DECLARE
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
where
upper(dt.table_name) = upper(et.tablename)
and et.avg_row_len is null
--and UPPER(et.tablename) NOT IN ('ENT_PNDNG_LCK_ORDR', 'ENT_ABC_SRVC_CHRG', 'ENT_IF_TXN_MSTR_RCRD')
group by tablename;
CURSOR get_cols (p_tab_name all_tables.table_name%TYPE
) IS
SELECT *
FROM all_tab_columns
WHERE table_name = p_tab_name
AND data_type NOT IN ( 'CLOB', 'BLOB', 'LOB', 'NCLOB');
BEGIN
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)
LOOP
c1 := dbms_sql.open_cursor;
l_sql := 'select round(avg(nvl(vsize(' || get_col_rec.column_name ||
'),0)))' || ' from ' ||
get_col_rec.owner||'.'||get_col_rec.table_name;
dbms_sql.parse(c1,l_sql,dbms_sql.v7);
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;
END LOOP;
ELSE
avgrow := tab_rec.avg_row_len;
END IF;
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)));
commit;
END LOOP;
END;
/
**********************************************
|
|
|
|
| Re: Estimating DB Size of Prodution Database [message #65253 is a reply to message #65242] |
Mon, 05 July 2004 11:33  |
croK
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.....
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
