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.


Free space in Auto extensible tablespaces

For any database administrator monitoring the free space is one of the primary jobs. Even though enterprise manager and db console will help the DBA with its monitoring capabilities and manageability, there are few gaps which need to be managed by DBA manually. I had to face one of such situation.

Environment was as below:

  • 11g ASM, 10g Databases
  • Database with more than 300 tablespaces and 1500+ data files.
  • Most of the data files are in auto-extend mode.
  • Sizes of tablespaces vary across wide range with smallest tablespace being 200MB in size and largest being more than 200GB.

For this environment our challenge was to maintain at least 10% of free space on all tablespaces. Management needs to be alerted well in advance to initiate the process of allocating SAN luns for ASM. As most of the data files were in auto extend mode, GRID control was not the best fit. As an immediate solution we have developed below script which provides the exact free space in each tablespace after considering the growth possible via Auto-extend.

WITH my_ddf AS
    (
        SELECT file_id, tablespace_name, file_name,
               DECODE (autoextensible,
                       'YES', GREATEST (BYTES, maxbytes),
                       BYTES
                      ) mysize,
              DECODE (autoextensible,
                      'YES', CASE
                         WHEN (maxbytes > BYTES)
                            THEN (maxbytes - BYTES)
                         ELSE 0
                      END,
                      0
                     ) growth
         FROM dba_data_files)
SELECT   my_ddf.tablespace_name,
         ROUND (SUM (my_ddf.mysize) / (1024 * 1024)) totsize,
         ROUND (SUM (growth) / (1024 * 1024)) growth,
         ROUND ((SUM (NVL (freebytes, 0))) / (1024 * 1024)) dfs,
         ROUND ((SUM (NVL (freebytes, 0)) + SUM (growth)) / (1024 * 1024)
               ) totfree,
         ROUND (  (SUM (NVL (freebytes, 0)) + SUM (growth))
                 / SUM (my_ddf.mysize)
                 * 100
               ) perc
    FROM my_ddf, (SELECT   file_id, SUM (BYTES) freebytes
                      FROM dba_free_space
                  GROUP BY file_id) dfs
   WHERE my_ddf.file_id = dfs.file_id(+)
         AND my_ddf.tablespace_name NOT LIKE '%UNDOTB%'
GROUP BY my_ddf.tablespace_name
ORDER BY 6 DESC

Above script will report the free space percentage in each tablespace correctly after considering the growth possible by Auto-Extend. Thought of sharing this with community as I couldn’t find a similar script in the net.

Edit: It seems there are few hits for this requirement in the net. Some how I might have missed them. One of them was from Oracle Magazine.

Welcome

Hello All,

At last I’ve started blogging. Here I would like to share my day to day experiences with Oracle related technologies. There won’t be frequent updates though. Subscribe to this with your rss reader or check out once in a while.

-Ravi