Oracle Worklog

Just another WordPress.com weblog

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

February 26, 2010 - Posted by | Database Monitoring | ,

6 Comments »

  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:
    )

    Comment by Dave | January 14, 2011 | Reply

    • 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

      Comment by Ravi Madabhushanam | January 14, 2011 | Reply

  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!

    Comment by lovecraft | February 22, 2011 | Reply

    • 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.

      Comment by Ravi Madabhushanam | February 25, 2011 | Reply

  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?

    Comment by Vasant | June 28, 2012 | Reply

    • 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

      Comment by Ravi Madabhushanam | June 28, 2012 | 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

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: