Home » Developer & Programmer » Designer » Estimating Tablespace Requirement
Estimating Tablespace Requirement [message #90752] Tue, 26 October 2004 00:50 Go to next message
T. Sri Sai Krishna
Messages: 17
Registered: May 2002
Junior Member
Hi,

We are developing a new application. The initial data for the application is being loaded into table before giving access to online users. Our tablespace is very small right now. Is there  a tool or some method of calculating the initial storage requirement based on this initial data. And also estimate the future requirement as the no. records grow in 2 , 3 or 6 months time.

I would prefer the calculation as we cannot afford tools right now. Where can I get scripts to measure performance of packages called from client applications.

Any help in this regard will be greatly appreciated.

Thanks in advance - Sri Sai krishna
Re: Estimating Tablespace Requirement [message #90757 is a reply to message #90752] Tue, 26 October 2004 13:36 Go to previous message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
Disk is cheap and space estimates are usually not very accurate - don't try to get too exact about it. The best place to start is by putting some sample data into a table and then seeing how much space that uses. vsize() will give you the bytes.

Try something like this:
select avg(vsize(col1)) +
avg(vsize(col2)) + ...,
count(*)
from my_table;

vsize reports actual storage space in bytes, then just multiply by the number of rows you'll have. Take that figure then add say 50% to account for the fact that not all data blocks will be full of data, indexes etc.

You might like to just categorize tables into sizes and then add those up. e.g.
small (up to 100k)
medium (100k to 1M)
big (1M to 10M) etc.

Try running this to see how much space is actually used too. Note that indexes appear before the table in the report:
REM	To see the actual space allocated and used for tables and 
REM corresponding indexes
REM
set serveroutput on format wrapped feedback off echo off verify off lines 80 trims on 
spool space.lst
declare
 v_tablepspace varchar2(32) := 'ABC';
-- v_owner varchar2(32) := 'MY_DATA_TS';
 
  cursor ctab is 
        select a.owner, a.table_name, b.extents
 		from dba_tables a, dba_segments b
		where a.TABLE_NAME = b.segment_name
		and a.OWNER = b.owner
		and a.tablespace_name = v_tablepspace 
		order by a.owner, a.table_name
 	;

-- cursor ctab is select owner, table_name
--		from dba_tables where owner = v_owner order by owner, table_name
--	;

 cursor cind (wowner in varchar2, wtab in varchar2) is 
        select a.owner, a.index_name, b.extents
		from dba_indexes a, dba_segments b
		where a.INDEX_NAME = b.segment_name
		and a.owner = b.owner
		and a.table_name = wtab 
		and a.table_owner = wowner;

 wtotal_blocks               number;
 wtotal_bytes                number;
 wunused_blocks              number;
 wunused_bytes               number;
 wlast_used_extent_file_id   number;
 wlast_used_extent_block_id  number;
 wlast_used_block            number;

 wtaballoc       number;
 wtabused        number;
 wtabfree        number;
 windalloc       number;
 windused        number;
 windfree        number;

 wtottaballoc       number := 0;
 wtottabused        number := 0;
 wtottabfree        number := 0;
 wtotindalloc       number := 0;
 wtotindused        number := 0;
 wtotindfree        number := 0;

begin

 dbms_output.enable (9999999);
 
 dbms_output.new_line ();
-- for rec in (select to_char(sysdate, 'dd Mon yyyy hh24:mi:ss' )||
-- 	 	 	 		' - User ('||v_owner||') on Database ('||global_name||')' Where_am_i 
--					from global_name)
 for rec in (select to_char(sysdate, 'dd Mon yyyy hh24:mi:ss' )||
 	 	 	 		' - Tabelspace ('||v_tablepspace||') on Database ('||global_name||')' Where_am_i 
					from global_name)
 loop
   dbms_output.put_line (rec.Where_am_i );
 end loop;
  dbms_output.new_line ();

  dbms_output.put_line ('Expanded space report for Table Space :'|| v_tablepspace );
  dbms_output.put_line ('---------------------------------------'|| rpad('-', length(v_tablepspace), '-'));
  dbms_output.put_line ('Table Owner.Name(extents)             Tab.   UsedM  FreeM  Ind.   UsedM  FreeM' );
  dbms_output.put_line ('------------------------------------  -----  -----  -----  -----  -----  -----');

 for rtab in ctab loop

   dbms_space.unused_space (rtab.owner, rtab.table_name, 'TABLE', 
                            wtotal_blocks,  wtotal_bytes,  wunused_blocks,
                            wunused_bytes, wlast_used_extent_file_id,
                            wlast_used_extent_block_id, wlast_used_block);

   wtaballoc := wtotal_bytes/1048576;
   wtabused  := (wtotal_bytes - wunused_bytes)/ 1048576;
   wtabfree  := wunused_bytes/1048576;
   windalloc := 0;
   windused  := 0;
   windfree  := 0;

   wtottaballoc := wtottaballoc + wtaballoc;
   wtottabused  := wtottabused  + wtabused;
   wtottabfree  := wtottabfree  + wtabfree;

   for rind in cind (rtab.owner, rtab.table_name) loop

     dbms_space.unused_space (rind.owner, rind.index_name, 'INDEX', 
                              wtotal_blocks,  wtotal_bytes,  wunused_blocks,
                              wunused_bytes, wlast_used_extent_file_id,
                              wlast_used_extent_block_id, wlast_used_block);
     -- print Index detail!
     dbms_output.put_line (rpad((  --rtab.owner || '.' || 
				  '  '||rind.index_name||'('||rind.extents||')'),34) || 
			   lpad(' ', 23) ||
			   lpad(to_char(wtotal_bytes/1048576, '9999.9'), 7) ||
			   lpad(to_char((wtotal_bytes - wunused_bytes)/ 1048576, '9999.9'), 7) ||
			   lpad(to_char(wunused_bytes/1048576, '9999.9'), 7) );
			 
     windalloc := windalloc + wtotal_bytes/1048576;
     windused  := windused + (wtotal_bytes - wunused_bytes)/ 1048576;
     windfree  := windfree + wunused_bytes/1048576;
  
   end loop;

   wtotindalloc := wtotindalloc + windalloc;
   wtotindused  := wtotindused  + windused;
   wtotindfree  := wtotindfree  + windfree;

   dbms_output.put_line (rpad(( -- rtab.owner || '.' || 
				rtab.table_name ||'('||rtab.extents||')' ),36) || 
			 lpad(to_char(wtaballoc, '9999.9'), 7) ||
			 lpad(to_char(wtabused, '9999.9'), 7) ||
			 lpad(to_char(wtabfree, '9999.9'), 7) ||
			 lpad(to_char(windalloc, '9999.9'), 7) ||
			 lpad(to_char(windused, '9999.9'), 7) ||
			 lpad(to_char(windfree, '9999.9'), 7) );

 end loop;

 dbms_output.put_line ('------------------------------------  -----  -----  -----  -----  -----  -----');

 dbms_output.put_line (rpad('TOTAL',36) || 
        		 lpad(to_char(wtottaballoc, '99999.9'), 7) ||
			 lpad(to_char(wtottabused, '99999.9'), 7) ||
			 lpad(to_char(wtottabfree, '99999.9'), 7) ||
			 lpad(to_char(wtotindalloc, '99999.9'), 7) ||
			 lpad(to_char(wtotindused, '99999.9'), 7) ||
			 lpad(to_char(wtotindfree, '99999.9'), 7) );

 dbms_output.put_line ('------------------------------------------------------------------------------');

     dbms_output.put_line ('Full segment type breakdown is:');
     dbms_output.put_line ('-------------------------------');
 for j in (select rpad(segment_type, 20) seg_type, sum(bytes) / 1048576 All_Seg_Types 
           from dba_segments 
	   where tablespace_name = v_tablepspace
	   group by rpad(segment_type, 20)) loop
     dbms_output.put_line (
     	j.seg_type||' '||
     	to_char(j.All_Seg_Types, '99999.99'));
 end loop;
     dbms_output.put_line ('                       --------');
 for k in (select sum(bytes) / 1048576 All_Seg_Types 
           from dba_segments 
	   where tablespace_name = v_tablepspace) loop
     dbms_output.put_line ('Total                '||to_char(k.All_Seg_Types, '99999.99'));
 end loop;
end;
/
set feedback on verify on 
spool off
Previous Topic: pctfree, pctused in locally managed tablespace
Next Topic: trab_item
Goto Forum:
  


Current Time: Thu Mar 28 17:12:49 CDT 2024