Checking Database Growth Trend

One of the simple way to find the growth of the database is using v$datafile view. following is the simple query and its output which gives the growth trend in month and year

</code>

SQL>select to_char(CREATION_TIME,'RRRR') year, to_char(CREATION_TIME,'MM') month, round(sum(bytes)/1024/1024/1024) GB
 2  from   v$datafile
 3  group by  to_char(CREATION_TIME,'RRRR'),   to_char(CREATION_TIME,'MM')
 4  order by   1, 2;

YEAR MO         GB
---- -- ----------
2004 10         14
2004 11         66
2004 12         47
2005 01         15
2005 02         14
2005 03         34
2005 04         14
2005 05         30
2005 06          5
2005 07          3
2005 08          9

YEAR MO         GB
---- -- ----------
2005 10          4
2005 12         20
2006 01         11
2006 02         15
2006 03         14
2006 04         16
2006 05         13
2006 06          0
2006 07          4
2006 08         17
2006 09          5

YEAR MO         GB
---- -- ----------
2006 10          6
2006 11          0
2006 12         30
2007 01          4
2007 02          4
2007 03          6
2007 04          6
2007 05         12
2007 06         15
2007 07         62
2007 08         29

YEAR MO         GB
---- -- ----------
2007 09         18
2007 11         20
2007 12          8
2008 03          8
2008 05          4
2008 08         11
2008 09         14
2008 10          4
2008 11         36
2008 12         49
2009 01          8

YEAR MO         GB
---- -- ----------
2009 02         38
2009 03         35
2009 04         54
2009 05         73
2009 08          1
2009 09         12
2009 10         78
2009 11        105
2009 12        113
2010 01        109
2010 02         79

YEAR MO         GB
---- -- ----------
2010 03         84
2010 05        243
2010 06        225
2010 07        244
2010 08        152
2010 09         40
2010 10        176
2010 11         76
2010 12        107

64 rows selected.

<code>

8 Comments

  1. Hi Advait

    I can see that that might well be an appropriate query for an Amazon database – assuming that you use fixed datafile sizes for ease of allocation/automation etc (and if you are that controlled I’d probably be recording growth in a management system elsewhere anyway :)). But in general that gives you the “current” size in GB of datafiles added in each of those months. As soon as you allow datafiles to autoextend then the result of that query isn’t really meaningful as a measure of monthly database growth.

    Reply

  2. Hi Niall, you are right. This works only if autoextend is off. Do you have any better suggestion for finding the exact growth trend of database.

    Reply

  3. OEM grid control has a Reports tab which gives the details about growth of the database.

    Here are the steps to know Database growth pattern for last one month/year using OEM

    1) Login to OEM and Click on the Reports Tab
    2) Navigate to Reports–>Storage–>Oracle Database Space Usage path and Click on Oracle Database Space Usage link.
    3) Select the Target database and here we are getting Oracle Database space usage for last one Month.
    4) Also we can get one year Database growth by setting Set Time Period Button.
    5) Also we can find Oracle Database Tablespace Monthly Space Usage by Navigating Reports–>Storage–>Oracle Database Space Usage path and click on Oracle Database Tablespace Monthly Space Usage link.

    Need to check how this can be done using a query

    Reply

  4. Without the cost of grid control, if one runs Statspack for a reasonable period of time ( I have 3 production years of it stashed away in a monitor instance), it’s very simple to extract that information from its tables. And a whole lot more.

    Reply

  5. Hi,
    The information in this site very useful.

    Noon – Could you share the scripts to monitor the database growth from statstpack/AWR please?

    Regards,
    Richard

    Reply

    1. Richard
      You maye try running below query to get the overall DB growth (summarization of all the TBS growth) over the period of time (depending on your retention AWR snaps). I use it to monitor DB growth along with TBS

      with
      a as (
      select a.name,a.ts# TBS_ID,
      MIN(snap_id) Begin_snap_ID,
      max(snap_id) End_Snap_ID,
      min(trunc(to_date(rtime, ‘MM/DD/YYYY HH24:MI:SS’))) begin_time,
      max(trunc(to_date(rtime, ‘MM/DD/YYYY HH24:MI:SS’))) End_time
      from dba_hist_tbspc_space_usage,
      (select name, ts#, block_size
      from v$tablespace, dba_tablespaces
      where name = tablespace_name) a
      where tablespace_id = a.ts#
      group by a.name, a.ts#,trunc(to_date(rtime, ‘MM/DD/YYYY HH24:MI:SS’))
      order by 4,5
      ),
      a1 as (
      select a.name,a.ts# TBS_ID,
      MIN(snap_id) Begin_snap_ID,
      max(snap_id) End_Snap_ID,
      min(trunc(to_date(rtime, ‘MM/DD/YYYY HH24:MI:SS’))) begin_time,
      max(trunc(to_date(rtime, ‘MM/DD/YYYY HH24:MI:SS’))) End_time
      from dba_hist_tbspc_space_usage,
      (select name, ts#, block_size
      from v$tablespace, dba_tablespaces
      where name = tablespace_name ) a
      where tablespace_id = a.ts#
      group by a.name,a.ts#, trunc(to_date(rtime, ‘MM/DD/YYYY HH24:MI:SS’))
      order by 4,5
      ),
      b as (
      select name,
      TBS_ID,
      min(a.Begin_snap_ID) Begin_Snap_ID_1,
      max(a.End_Snap_ID) End_Snap_ID_2
      from a
      group by name, TBS_ID
      ),
      c as (
      select b.name,
      b.TBS_ID,
      b.Begin_Snap_ID_1 begin_snap_ID,
      a.End_time Begin_time,
      b.End_Snap_ID_2 End_snap_ID,
      a1.End_time End_time
      from a, b, a1
      where b.Begin_Snap_ID_1 = a.Begin_snap_ID and
      b.End_Snap_ID_2 = a1.End_snap_ID and a.name = b.name and
      a1.name = b.name
      ),
      d as (
      select c.name,
      dh.snap_id,
      round((dh.tablespace_usedsize * 8192) / 1024 / 1024, 2) begin_Used_space
      from dba_hist_tbspc_space_usage dh, c
      where dh.snap_id = c.Begin_snap_ID and c.TBS_ID = dh.tablespace_id
      ),
      e as (
      select c.name,
      dh.snap_id,
      round((dh.tablespace_usedsize * 8192) / 1024 / 1024, 2) End_Used_space
      from dba_hist_tbspc_space_usage dh, c
      where dh.snap_id = c.End_Snap_ID and c.TBS_ID = dh.tablespace_id
      )
      select e.name,
      to_char(c.begin_time, ‘DD-MON-YYYY’) Begin_time,
      d.begin_Used_space,
      to_char(c.End_time, ‘DD-MON-YYYY’) End_Time,
      e.End_Used_space,
      (e.End_Used_space – d.begin_Used_space) “Growth in MB”
      from e, d, c
      where e.name = d.name and e.name = c.name and d.snap_id = c.Begin_snap_ID and
      e.snap_id = c.End_Snap_ID and
      (e.End_Used_space – d.begin_Used_space) > 0
      ORDER BY 1, 2 desc

      Reply

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s