Measuring Database Space Growth

Keeping track of database space growth in production environments is a must-to-do job of every DBA. Maintaining and reporting information of historical database growth is always a task that involves good amount of planning and scheduling various jobs in the database. To achieve this many DBA’s have used a common method which includes;

  1. Create a temporary staging area to store the space usage statistics of database at the required level of detail (segment / tablespace / database / user)
  2. Schedule a weekly/monthly job to generate a consolidated report
  3. Schedule a job to purge old information from the staging table to avoid excessive space usage.

While this approach works effectively after proper testing, it has good amount of manual work involved. Also creating objects in a production database and scheduling jobs to run against always requires approval from management.

Starting from 10g, Oracle has provided us with a feature to achieve this without much overhead. Most importantly, we can use this with out incurring any extra license cost. This technique is used by many DBA’s but not much documentation is available over the internet for easy use.

Database Space growth using DBA_HIST tables:

Oracle AWR by default collects the information about the segment growth periodically. This information can be queried using views DBA_HIST_SEG_STAT. Below is a small code snippet which can be used to view the database growth with 1hr intervals.

Declare
	v_BaselineSize	number(20);
	v_CurrentSize	number(20);
	v_TotalGrowth	number(20);
	v_Space		number(20);
	cursor usageHist is
        	select a.snap_id,
			SNAP_TIME,
			sum(TOTAL_SPACE_ALLOCATED_DELTA) over ( order by a.SNAP_ID) ProgSum
		from
			(select SNAP_ID,
				sum(SPACE_ALLOCATED_DELTA) TOTAL_SPACE_ALLOCATED_DELTA
			from DBA_HIST_SEG_STAT
			group by SNAP_ID
			having sum(SPACE_ALLOCATED_TOTAL) <> 0
			order by 1 ) a,
			(select distinct SNAP_ID,
				to_char(END_INTERVAL_TIME,'DD-Mon-YYYY HH24:Mi') SNAP_TIME
			from DBA_HIST_SNAPSHOT) b
		where a.snap_id=b.snap_id;
Begin
	select sum(SPACE_ALLOCATED_DELTA) into v_TotalGrowth from DBA_HIST_SEG_STAT;
	select sum(bytes) into v_CurrentSize from dba_segments;
	v_BaselineSize := v_CurrentSize - v_TotalGrowth ;

	dbms_output.put_line('SNAP_TIME           Database Size(MB)');

	for row in usageHist loop
        	v_Space := (v_BaselineSize + row.ProgSum)/(1024*1024);
		dbms_output.put_line(row.SNAP_TIME || '           ' || to_char(v_Space) );
	end loop;
end;

The above code extracts information from DBA_HIST tables. Most of the DBA_HIST tables are part of Diagnostics Pack and should be accessed only if you have the license. But Oracle documentation has excluded the above accessed views from the diagnostics pack and these can be accessed with out any special license.

Below is the snippet from the Oracle licensing document for reference:

All data dictionary views beginning with the prefix DBA_HIST_ are part of this pack, along with their underlying tables, except for these views that you can use without the Diagnostic Pack license: DBA_HIST_SNAPSHOT, DBA_HIST_DATABASE_INSTANCE, DBA_HIST_SNAP_ERROR, DBA_HIST_SEG_STAT, DBA_HIST_SEG_STAT_OBJ, and DBA_HIST_UNDOSTAT.

Segments with highest growth (Top n):

Below is a query which can be used to query segments with highest growth. This will also report the present size of the segment which is very useful in identifying the growth percecntage.

SELECT o.OWNER , o.OBJECT_NAME , o.SUBOBJECT_NAME , o.OBJECT_TYPE ,
	t.NAME "Tablespace Name", s.growth/(1024*1024) "Growth in MB",
	(SELECT sum(bytes)/(1024*1024)
	FROM dba_segments
	WHERE segment_name=o.object_name) "Total Size(MB)"
FROM DBA_OBJECTS o,
	( SELECT TS#,OBJ#,
		SUM(SPACE_USED_DELTA) growth
	FROM DBA_HIST_SEG_STAT
	GROUP BY TS#,OBJ#
	HAVING SUM(SPACE_USED_DELTA) > 0
	ORDER BY 2 DESC ) s,
	v$tablespace t
WHERE s.OBJ# = o.OBJECT_ID
AND s.TS#=t.TS#
AND rownum < 51
ORDER BY 6 DESC
/

The above query can be customized to find top segments in a specific tablespace or for a specific user as well.

By using DBA_HIST tables to find database growth has many advantages over the traditional method.

  • No need to schedule any jobs for data collection. AWR will do it for you automatically
  • No need to think about old data purging as it is done automatically.
  • No extra licensing cost as this feature is available automatically.


About these ads

8 thoughts on “Measuring Database Space Growth

  1. Nice scripts but I’m afraid it’s failing , not sure what we need to provide in )<> in the the first script (Database Space growth using DBA_HIST tables:
    )

    • Hi Dave,

      Sorry for the confusion. It seems word press is not letting me keep “” signs in the code. just replace &lt with and the script should work. Thanks for bringing it to my notice. Let me know if you are still facing issues and I’ll be glad to send you the script in an SQL file.

      Also please note that this is an approximation and not absolute values. There are few bugs related to UNDO segment handling as well. Do not rely on these values completely. Its just for an indicative growth analysis.

      Thanks,
      Ravi.M

  2. Hi!

    Thanks for the scripts! It was a great starting point for me as DBA_HIST_SEG_STAT is very verbose with similiar columns. However, when i executed the 1st script several times after some insertion transactions, i discovered that the figures change. i.e.

    when i executed the script on 21 feb 3pm, this was the result

    SNAP_TIME Database Size(MB)
    14-Feb-2011 12:00 308
    14-Feb-2011 13:00 314
    14-Feb-2011 14:00 317
    14-Feb-2011 15:00 320
    14-Feb-2011 16:00 3274
    14-Feb-2011 17:00 3280
    …..
    21-Feb-2011 13:00 3462
    21-Feb-2011 14:00 3463
    21-Feb-2011 15:00 3469

    PL/SQL procedure successfully completed.

    Later, i inserted a few million records into some tables and re-executed the query on the same day at 4pm and this was the result:

    SNAP_TIME Database Size(MB)
    14-Feb-2011 12:00 529
    14-Feb-2011 13:00 535
    14-Feb-2011 14:00 538
    14-Feb-2011 15:00 541
    14-Feb-2011 16:00 3495
    ……
    21-Feb-2011 14:00 3684
    21-Feb-2011 15:00 3690
    21-Feb-2011 16:00 3780

    PL/SQL procedure successfully completed.

    May I know why is this so? thanks!

    • As I mentioned in one of my previous comment, this script is just an approximation of AWR information. It has quite a few bugs as well. For example, I myself not completely confident about the way undo segments are handled in this code. However, it is really handy for me, when I want to have a quick look at past growth of database, which does not have any mechanism to store historical growth.
      In short this is not a replacement for traditional methods of tracking the DB growth. Its just a simple and quick approximation.
      I’ll work to make this script more accurate, but it might take time. Sorry about that.

      HTH.

  3. thank you ravi. this script gives more clarity. but you mention traditional methods of tracking the DB growth? can you give some hints on that?

    • Hi Vasant, Thanks for stopping by. This script should be used as an approximation, only when there is no other historical data available. I’ve seen some cases where the growth is not completely accurate.

      Regarding traditional methods, you can simply schedule a DBA_JOB to query dictionary tables at frequent intervals ( may be 1 day or week) and store it in a table. This table can be queried whenever required to get the history growth.

      Hope this information was helpful.

      Thanks,
      Ravi.M

  4. Hi Ravi

    I executed below query and I’m confused with the output

    select * from (SELECT o.OWNER , o.OBJECT_NAME , o.SUBOBJECT_NAME , o.OBJECT_TYPE ,
    t.NAME “Tablespace”, s.growth/(1024*1024) “Growth in MB”,
    (SELECT sum(bytes)/(1024*1024*1024)
    FROM dba_segments
    WHERE segment_name=o.object_name) “Total Size(GB)”
    FROM DBA_OBJECTS o,
    ( SELECT TS#,OBJ#,
    SUM(SPACE_USED_DELTA) growth
    FROM DBA_HIST_SEG_STAT
    GROUP BY TS#,OBJ#
    HAVING SUM(SPACE_USED_DELTA) > 0
    ORDER BY 2 DESC ) s,
    v$tablespace t
    WHERE s.OBJ# = o.OBJECT_ID
    AND s.TS#=t.TS# and o.owner=’XXXXXX’ and o.object_type=’TABLE’
    ORDER BY 6 DESC) where rownum<6;

    OUTPUT

    OWNER OBJECT_NAME SUBOBJECT_NAME OBJECT_TYPE Tablespace Growth in MB Total Size(GB)
    XXXXXX YYYY1 TABLE USERS 2,405.97 0.02
    XXXXXX YYYY2 TABLE USERS 597.20 0.01
    XXXXXX YYYY3 TABLE USERS 365.79 0.32
    XXXXXX YYYY4 TABLE USERS 360.48 0.05
    XXXXXX YYYY5 TABLE USERS 343.07 0.06

    Consider object "YYYY1" having growth of 2405 MB but its size is 0.02 GB (0.02*1024=20.48 MB)
    How is that possible?
    same goes for all the other objects, Please let me know what am I missing ?

    Regards
    Rishwinger

    • Hi Rishwinger,
      I’m logging back after a long break into this blog and hence the delay in responding.

      Above query collects segment growth information by looking at the segment allocations to specific object type. One reason I could think of in this specific scenario is that there are some truncate operations performed on this object. That specific scenario is not completely tested and has created some inconsistent results in the past. Let me try to reproduce this issue and get back to you on root cause. Could you confirm that there are no truncate operations performed on this object?

      Thanks,
      Ravi.M

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