Database Capacity Planning

Hello friends,
One of the tasks a DBA has to do, not always though, but application team might come around and ask for a database capacity planning for the storage addition Year on Year(YoY). I have found this script from some site(don't remember, but credit goes to the author of the script) which I used recently to provide the capacity planning to the application/storage team.

select
  to_char(CREATION_TIME,'RRRR') year, to_char(CREATION_TIME,'MM') month,
  sum(bytes/1024/1024/1024) GigaBytes
from v$datafile
group by
  to_char(CREATION_TIME,'RRRR'), to_char(CREATION_TIME,'MM')
order by 1, 2;

Output below:

YEAR    MO  GIGABYTES
----       --     ----------
2010     09         21.0048828
2013     12         82.515625
2014     03         42
2014     04         12

Please note, the datafiles created in year 2010 are system, sysaux, undo and users tablespaces, but the database creation date is as below.

Now comes the real number crunching.

-- Compute how many months has gone since the database was created using the below query:

SQL> select created from v$database;

CREATED
---------
28-DEC-13

SQL> select ceil(months_between(sysdate, created)) tot_mon from v$database;

   TOT_MON
----------
        22

-- Compute the maximum number of times a file created in 'autoextend' mode has grown using the below query:

select max(round((d.bytes - d.create_bytes) / f.inc / d.block_size)) maxinc
from sys.file$ f, v$datafile d where f.inc > 0 and f.file# = d.file# and d.bytes > d.create_bytes;

    MAXINC
----------
      1229

Run the below query and pass the input from the above queries:

Enter value for max_inc: 1229

Enter value for range:22

col GB format 9999990.00

col volume format A80

with extended_files as

            (select file#,

                    nvl(lag(file_size, 1) over (partition by file#

                                                order by file_size), 0)

prior_size,

                    file_size,

                    block_size

             from (select f.file#,

                          f.create_blocks + x.rn * f.inc file_size,

                          f.block_size     

                   from (select f.file#,

                                d.create_bytes / d.block_size create_blocks,

                                f.inc,

                                d.bytes / d.block_size blocks,

                                d.block_size

                         from sys.file$ f,

                              v$datafile d    

                         where f.inc > 0

                           and f.file# = d.file#

                           and d.bytes > d.create_bytes

                           and rownum > 0) f,

                        (select rownum - 1 rn

                         from dual

                         connect by level <= &max_inc + 1) x

                   where (f.create_blocks + x.rn * f.inc) <= f.blocks))

select "MONTH",

       round(cumul/1024, 2) GB,

       -- Draw a histogram

       rpad('=', round(60 * cumul / current_M), '=') volume

from 

(select to_char(cal.mon, 'MON-YYYY') "MONTH",

             sum(nvl(evt.M, 0)) over (order by cal.mon range unbounded

preceding) cumul,

             tot.curr_M current_M,

             cal.mon

      from -- current database size (data size)

           (select round(sum(bytes)/1024/1024) curr_M

            from v$datafile) tot,

           -- all the months since the database was created

           (select add_months(trunc(sysdate, 'MONTH'), -rn) mon

            from (select rownum - 1 rn

                  from dual

                  connect by level <= &range)) cal,

           -- all the months when the size of the database changed

           (select size_date,

                   round(sum(bytes)/1024/1024) M

            from (-- files in autoextend mode

                  select file#, max(bytes) bytes, size_date

                  from (select file#, bytes, trunc(min(ctime), 'MONTH')

size_date

                        -- Get the oldest creation date of tables or indexes

                        -- that are located in extensions.

                        -- Other segment types are ignored.

                        from (select s.file#,

                                     f.file_size * f.block_size bytes,

                                     o.ctime

                              from sys.seg$ s,

                                   extended_files f,

                                   sys.tab$ t,

                                   sys.obj$ o

                              where s.file# = f.file#

                                and s.type# = 5

                                and s.block# between f.prior_size and

f.file_size

                                and s.file# = t.file#

                                and s.block# = t.block#

                                and t.obj# = o.obj#

                              union all

                              select s.file#,

                                     f.file_size * f.block_size bytes,

                                     o.ctime

                              from sys.seg$ s,

                                   extended_files f,

                                   sys.ind$ i,

                                   sys.obj$ o

                              where s.file# = f.file#

                                and s.type# = 6

                                and s.block# between f.prior_size and

f.file_size

                                and s.file# = i.file#

                                and s.block# = i.block#

                                and i.obj# = o.obj#)

                        group by file#, bytes)

                  group by file#, size_date

                  union all

                  -- files that are not in autoextend mode

                  select d.file#,

                         d.create_bytes bytes,

                         trunc(d.creation_time, 'MONTH') size_date

                  from v$datafile d,

                       sys.file$ f

                  where nvl(f.inc, 0) = 0

                    and f.file# = d.file#)

            group by size_date) evt

      where evt.size_date (+) = cal.mon)

order by mon

/

You get a neat output as below:

Note that this is a cumulative database growth Month on Month till date from the inception of the database.

If you want to make a YoY growth, calculate the Yearly growth and apply the YoY formula in a excel sheet and provide it to the application/storage team for analysis.

Below is the excel data I made and provided to an application team last week:

Growth YoY

2012 minus 2011 equals to 3.75

2013 minus 2012 equals to 1

2014 minus 2013 equals to 8.44

2015 minus 2014 equals to 13.36

%YoY

2012 - YoY of 2012 divided by 2011 database size multiplied by 100 - (3.75/14.64)*100 = 25.61%

2013 - YoY of 2013 divided by 2012 database size multiplied by 100 - (1/18.39)*100 = 5.44%

2014 - YoY of 2014 divided by 2013 database size multiplied by 100 - (8.44/19.39)*100 = 43.53%

2015 - YoY of 2015 divided by 2014 database size multiplied by 100 - (13.36/27.83)*100 = 48.01%

From the above growth analysis, we inferred that there would be an average growth of 50% in the database size.