Trend of data growth in Oracle Database

In real production world “Prediction” of data growth is an important aspects of DBA life because this will allow business not only to foresee the real position in terms of existing hardware but also enable to plan the future expenses which should be spent on hardware(s) & storage.

To generate data growth report, Oracle provides various means which will be explored here.  We can track growth for an object (i.e. table, indexes) or for tablespaces or for schema or even at database level using the views provided by Oracle.

There are few important views provided by Oracle which helps us track the growth.  These can be listed as:

1. DBA_SEGMENTS
2. DBA_TABLESPACES
3. DBA_HIST_SEG_STAT
4. DBA_HIST_TABLESPACE_STAT
5. DBA_HIST_TBSPC_SPACE_USAGE
6. DBA_HIST_SNAPSHOT
7. DBMS_SPACE

View which does not have HIST in its name provides “accumulated till date” information.  This means you can derive current size of an object using DBA_SEGMENTS & DBA_TABLESPACES.  This view stores data usages information in terms of BYTES.  To explain this, we can say here that when we create a table ( with segment allocation immediate option) Oracle allocates spaces for this object which is not utilised until we add a row.  In simple terms used space is “High water mark” where Oracle has used the allocated space “up to”.   This is brilliantly explained by “Ask The Oracle Masters (AskTOM)”

However, this view cannot give you a comparative data growth chart where you can see the past usages and forecast the future requirement of the storage.  To generate such a report should visit history views.

Take DBA_HIST_SEG_STAT first.  This view captures lot more information about the segments and obviously in historical form like logical reads, physical read, buffer busy wait, row lock waits, table scans, physical write besides space allocation and utilization both. When such historical view is combinedly red with DBA_HIST_SNAPSHOT we will get the required information in comparative form.  To find data growth rate for any object we can refer this view.

To find growth rate of a tablespace we can also utilize another history view DBA_HIST_TABLESPACE_STAT & DBA_HIST_TBSPC_SPACE_USAGE with DBA_HIST_SNAPSHOT.  By using these views, we can find the historical information of allocated spaces Vs. used storage.

However, I must highlight here that the historical information captured will remain be available in the database for those dates & for those snapshots only which is configured by DBA to be kept in the database.  Beyond which, we cannot get such information.  For example, if DBA has configured 15 days of snapshot to be kept, we can retrieve such comparative information for last 15 days only.

If we want such comparison report for a year or more, we should follow alternate ways.   We may create a table which can be populated with such information by a scheduled job and instead of querying DB supplied views we should query these tables.

If we do not have such information kept earlier, we cannot have comparison reports beyond snapshots available in the database.

The package DBMS_SPACE enables us to analyse segment growth and space requirements in Oracle with the help of its numerous method.  We can refer “Oracle standard Documentation”  for exploring this package in details.  This package helps DBAs to perform capacity planning activity which will be explained in my another blog.

We consider this PL/SQL package here because this package provides a function “OBJECT_GROWTH_TREND” use of which we can extrapolate the growth of a particular table/indexes.

Let’s see these in terms of queries.

Tablespace Wise Growth on Daily basis:  Daily growth report of a tablespace.

SELECT TO_DATE(TO_CHAR (snpshot.begin_interval_time,’DD-MM-YYYY’),’DD-MM-YYYY’) daywise
, dhts.tsname tablespacename
, max(round((dhtsu.tablespace_size* dtblspc.block_size )/(1024*1024*1024),2) ) maximum_allocsize_GB
, max(round((dhtsu.tablespace_usedsize* dtblspc.block_size )/(1024*1024*1024),2)) maximum_usedsize_GB
FROM DBA_HIST_TBSPC_SPACE_USAGE dhtsu
, DBA_HIST_TABLESPACE_STAT dhts
, DBA_HIST_SNAPSHOT snpshot
, DBA_TABLESPACES dtblspc
WHERE dhtsu.tablespace_id= dhts.ts#
AND dhtsu.snap_id = snpshot.snap_id
AND dhts.tsname = dtblspc.tablespace_name
AND dhts.tsname NOT IN (‘SYSAUX’,’SYSTEM’)
GROUP BY TO_DATE(TO_CHAR (snpshot.begin_interval_time,’DD-MM-YYYY’),’DD-MM-YYYY’)
, dhts.tsname
ORDER BY dhts.tsname
, daywise;

And the output would be:

Daily Object’s Growth with respect to Tablespace in last X days:

SELECT * FROM
(SELECT ds.tablespace_name
, ds.segment_name
, TO_DATE(TO_CHAR (snpshot.end_interval_time,’DD-MM-YYYY’),’DD-MM-YYYY’) daywise
, SUM(dhss.space_used_delta) / 1024 / 1024 “Space used (MB)”
, AVG(ds.bytes) / 1024 / 1024 “Total Object Size (MB)”
, ROUND(SUM(dhss.space_used_delta) / SUM(ds.bytes) * 100, 2) “Percent of Total Disk Usage”
FROM dba_hist_snapshot snpshot
, dba_hist_seg_stat dhss
, dba_objects dobj
, dba_segments ds
WHERE begin_interval_time > TRUNC(SYSDATE) – &pastdays
AND snpshot.snap_id = dhss.snap_id
AND dobj.object_id = dhss.obj#
AND dobj.owner = ds.owner
AND dobj.object_name = ds.segment_name
AND ds.segment_name = ‘TEST_TAB1′
GROUP BY ds.tablespace_name,ds.segment_name,TO_DATE(TO_CHAR (snpshot.end_interval_time,’DD-MM-YYYY’),’DD-MM-YYYY’)
ORDER BY ds.tablespace_name,ds.segment_name,TO_DATE(TO_CHAR (snpshot.end_interval_time,’DD-MM-YYYY’),’DD-MM-YYYY’));

And the output would be:

Object’s Growth in Last X days:: This is accumulated growth till last X days.

SELECT ds.tablespace_name
, ds.segment_name “object name”
, dobj.object_type
, ROUND(SUM(dhss.space_used_delta) / 1024 / 1024 / 1024,2) “Growth (GB)”
FROM dba_hist_snapshot snpshot
, dba_hist_seg_stat dhss
, dba_objects dobj
, dba_segments ds
WHERE begin_interval_time > TRUNC(SYSDATE) – &past_days
AND snpshot.snap_id = dhss.snap_id
AND dobj.object_id = dhss.obj#
AND dobj.owner = ds.owner
AND dobj.object_name = ds.segment_name
AND ds.owner =’TESTOWNER’
GROUP BY ds.tablespace_name,ds.segment_name,dobj.object_type
ORDER BY 3 ASC;

And the output would be:

And the last but not the least, DBMS_SPACE package.  Method OBJECT_GROWTH_TREND is the brilliant way to get the growth estimate for a object.  We can write query with this method using TABLE function.

select * from table(
DBMS_SPACE.OBJECT_GROWTH_TREND (
object_owner => ‘TESTOWNER’,
object_name => ‘TEST_TAB1’,
object_type => ‘TABLE PARTITION’,
partition_name => ‘T1’ ) );

And the output would be: