Re: Predicting Growth.

Ben wrote:
> Ent Ed. 9.2.0.5, DMT's on AIX5L
>
> There isn't really an accurate way to estimate growth of a database, is
> there? I have a history of six months of my data file sizes, but even
> with that there are too many variables envolved to determine that my
> database is going to continue to grow at a rate of 50 gig per year.
>
> Even if you assume a constant rate of growth, how would you base your
> projection?
>
> For example, lets say my datafiles grew 25G over the last six months.
> At the beginning of that six months my database was 100G that is 25%
> growth over the past six months.
> Now looking ahead for a year. My database is now 125G, 25% of that is
> 31.25G. Then six months down the road the database will be 156.25G, 25%
> of that is 39.06G, etc etc. But that original 25% figure was calculated
> from the 100G incarnation of the database. So that won't work.
> Do I then assume that my database will grow at a constant size instead
> of %? So my 125G db will be 175G by this time next year. This seems
> more accurate, but I still think there are just too many unknowns to
> actually come to an accurate answer.
>

Create the table per your needs and column lengths, then run this once a   day/week/month/quarter.

At the end of each (day/week/month/quarter), run a size compare. Graph it to impress management.

--
-- Purpose: Collect space utilization information for a database
--
-- Schedule:  Should be run regularly
--
-- Function:  Collects data from DBA_DATA_FILES and DBA_FREE_SPACE
--            and saves it in an accumulation table keyed by date
-- 
-- The following formatting is for interactive runs without
-- inserting into the table.
--
--   set pagesize 150 linesize 131
--   col TS_SIZE for 999,999,999
--   col FREE_BYTES  for 999,999,999
--   col free_blocks for 999,999
--   col largest for 999,999,999
--   col ext_factor for 999.999
--   col dt for 'MM/DD/YYYY'
--   col ts for a9
--   col PCT_FREE for 99.999
--   col fsfi for 999.999

insert into FIMS_SPACE (STAT_DATE,
                         TABLESPACE,
                         TOTAL_SIZE,
                         FREE_BYTES,
                         FREE_BLOCKS,
                         LARGEST_FREE_EXT,
                         EXT_FACTOR,
                         NUMBER_EXT,
                         NUMBER_FILES,
                         FREE_PCT,
                         FSFI)
select trunc(sysdate) dt,
        f.tablespace_name ts,
        d.total_bytes ts_size,
        f.bytes free_bytes,
        f.blocks free_blocks,
        f.big largest,
        (f.big/d.total_bytes)*100 ext_factor,
        f.num_ext,
        d.num_files,
        f.bytes/d.total_bytes*100 "PCT_FREE",
        f.fsfi fsfi
from (select tablespace_name,
        count(*) num_ext,
        max(bytes) big,
        sum(bytes) bytes,
        sum(blocks) blocks,
        sqrt(max(blocks)/sum(blocks)) *
            (100/sqrt(sqrt(count(blocks)))) fsfi
       from dba_free_space
       group by tablespace_name) f,
      (select sum(bytes) total_bytes,
              count(*) num_files,
              tablespace_name
       from dba_data_files
       group by tablespace_name) d
where f.tablespace_name = d.tablespace_name
-- order by f.tablespace_name
/