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;
- Create a temporary staging area to store the space usage statistics of database at the required level of detail (segment / tablespace / database / user)
- Schedule a weekly/monthly job to generate a consolidated report
- 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.