Re: How to estimate database growth

From: Cam White <info_at_revealnet.com>
Date: 1997/10/27
Message-ID: <01bce2f2$77436600$5b73b1cd@Preveal2>#1/1

Craig Air <craigair_at_ozemail.com.au> wrote in article <631jtt$pli$1_at_reader1.reader.news.ozemail.net>...
> I need to produce some growth estimates for a large Oracle Data
 Warehouse.
>
> My approach was going to be (i) calculate the size of each database table
> and index currently in the database, (ii) divide this by the number of
> months data stored in the database, then (iii) multiply this figure by
 the
> number of months in advance that I need to estimate.
>
> Sounds simple? Well it probably is! However, I am still stuck on point 1.
> How do I find out how much space is currently taken by each table and
 index?
>
> If anyone has a better suggestion for estimating database growth then I
> would love to hear it.
>
> Thanks in advance.
>
> CRAIG
RevealNet's Oracle Administration Knowledge Base includes scripts for estimating index and table sizes, as well as attached spreadsheets. Full information and demo copies can be downloaded from http://www.revealnet.com

Here is a sample script:

rem*************** RevealNet Oracle Administration ***********************

rem
rem File: in_es_sz.sql
rem
rem This is a part of the RevealNet Oracle Administration library. rem Copyright (C) 1996-97 RevealNet, Inc. rem All rights reserved.
rem
rem For more information, call RevealNet at 1-800-REVEAL4 rem or check out our Web page: www.revealnet.com rem
rem Modifications (Date, Who, Description) rem
rem FUNCTION: Calculate index space requirements rem

rem***********************************************************************
rem
column dum1 	noprint
column isize 	format 99,999.99
column rcount 	format 999,999,999 newline 
accept tname  	prompt 'Enter table name: '
accept towner 	prompt 'Enter table owner name: '
accept clist  	prompt 'Enter column list: '
accept cfile  	prompt 'Enter name for output SQL script file: '

set pagesize 999 heading off verify off termout off feedback off sqlcase upper

        set newpage 3
ttitle off
spool &cfile..sql
select -1 dum1,
'select '''Proposed Index on table ''||' from dual union
select 0,

       '''&towner..&tname'||' has '',count(*) rcount,'' entries of '',('   from dual
union
select column_id,

      'sum(nvl(vsize('||column_name||'),0)) + 1 +'   from dba_tab_columns
 where table_name = '&tname' and owner = '&towner'    and column_name in (&clist)

  	   and column_id <> (select max(column_id)
                     from dba_tab_columns
	    where table_name = '&tname' and owner = '&towner'
                      and column_name in (&clist)) 

union
select column_id,

      'sum(nvl(vsize('||column_name||'),0)) + 1)'   from dba_tab_columns
 where table_name = '&tname'
   and owner = '&towner' and column_name in (&clist)    and column_id = (select max(column_id)

                     from dba_tab_columns
		    where table_name = '&tname'  
		    and owner = '&towner'
                      and column_name in (&clist)) 

union
select 997, '/ count(*) + 11 isize, '' bytes each.''' from dual union
select 999,

       'from &towner..&tname.;' from dual; spool off
set termout on feedback 15 pagesize 20 sqlcase mixed newpage 1 start title80 "Estimated Index Column Size" set heading off
ttitle on
start &cfile
clear columns

Best wishes,

Cam White
RevealNet, Inc.
(202) 234-8557 Received on Mon Oct 27 1997 - 00:00:00 CST