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:

 

 

 

DAYWISETABLESPACENAMEMAXIMUM_ALLOCSIZE_GBMAXIMUM_USEDSIZE_GB
9-Jul-17TEST_DATA158150.01
10-Jul-17TEST_DATA158149.19
11-Jul-17TEST_DATA158152.44
12-Jul-17TEST_DATA159153.39
13-Jul-17TEST_DATA181171.9
14-Jul-17TEST_DATA192182.19
15-Jul-17TEST_DATA192178.23
16-Jul-17TEST_DATA192177.9
17-Jul-17TEST_DATA192183.29
18-Jul-17TEST_DATA222214.76
19-Jul-17TEST_DATA249242.71
20-Jul-17TEST_DATA264254.59
21-Jul-17TEST_DATA279261.88
22-Jul-17TEST_DATA279264.31
23-Jul-17TEST_DATA279269.89
24-Jul-17TEST_DATA279271.94
25-Jul-17TEST_DATA279273.97
26-Jul-17TEST_DATA298278.62
27-Jul-17TEST_DATA298286.75
28-Jul-17TEST_DATA313297.25
29-Jul-17TEST_DATA313300.01
30-Jul-17TEST_DATA313300.96
31-Jul-17TEST_DATA316301.97
1-Aug-17TEST_DATA316304.58
2-Aug-17TEST_DATA316310.16
3-Aug-17TEST_DATA333320.15
4-Aug-17TEST_DATA333332.39
5-Aug-17TEST_DATA333275.86
6-Aug-17TEST_DATA333284.69
7-Aug-17TEST_DATA333287.04
8-Aug-17TEST_DATA333290.9
9-Aug-17TEST_DATA333293.4
10-Aug-17TEST_DATA333303.53
11-Aug-17TEST_DATA333313.73
12-Aug-17TEST_DATA333310.03
13-Aug-17TEST_DATA333309.44
14-Aug-17TEST_DATA337326.27
15-Aug-17TEST_DATA337326.58
16-Aug-17TEST_DATA337322.59
17-Aug-17TEST_DATA339335.14
18-Aug-17TEST_DATA347342.66
19-Aug-17TEST_DATA347338.71
20-Aug-17TEST_DATA365341.82
21-Aug-17TEST_DATA365344.18
22-Aug-17TEST_DATA365340.89
23-Aug-17TEST_DATA365344.29
24-Aug-17TEST_DATA385363.64
25-Aug-17TEST_DATA385366.42
26-Aug-17TEST_DATA385364.74
27-Aug-17TEST_DATA385373.13
28-Aug-17TEST_DATA386378.41
29-Aug-17TEST_DATA386379.34
30-Aug-17TEST_DATA388383.77
31-Aug-17TEST_DATA410393.69
1-Sep-17TEST_DATA411406.66
2-Sep-17TEST_DATA411399.87
3-Sep-17TEST_DATA411397.68
4-Sep-17TEST_DATA434405.79
5-Sep-17TEST_DATA434403.28
6-Sep-17TEST_DATA434405.77

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:

 

 

 

 

 

TABLESPACE_NAMESEGMENT_NAMEDAYWISESpace used (GB)Total Object Size (GB)Percent of Total Disk Usage
TEST_DATATEST_TAB18/28/2017 0:004.824.8319.97
TEST_DATATEST_TAB18/29/2017 0:004.724.8313.97
TEST_DATATEST_TAB18/30/2017 0:004.744.8349.05
TEST_DATATEST_TAB18/31/2017 0:004.744.8332.71
TEST_DATATEST_TAB19/1/2017 0:004.744.8316.37
TEST_DATATEST_TAB19/2/2017 0:004.754.8349.18
TEST_DATATEST_TAB19/3/2017 0:0004.830
TEST_DATATEST_TAB19/4/2017 0:004.784.8349.47
TEST_DATATEST_TAB19/5/2017 0:004.754.8319.65
TEST_DATATEST_TAB19/6/2017 0:004.84.8333.14
TEST_DATATEST_TAB19/7/2017 0:004.84.8349.71

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:

 

 

 

TABLESPACE_NAME object name OBJECT_TYPE growth (mb)
TEST_DATATEST_TAB1TABLE PARTITION47.64

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:

 

 

 

TIMEPOINTSPACE_USAGESPACE_ALLOCQUALITY
08-08-17 12:56:30.688879000 PM51638584295206179840INTERPOLATED
09-08-17 12:56:30.688879000 PM51638584295206179840INTERPOLATED
10-08-17 12:56:30.688879000 PM51638584295206179840INTERPOLATED
11-08-17 12:56:30.688879000 PM51638584295206179840INTERPOLATED
12-08-17 12:56:30.688879000 PM51638584295206179840INTERPOLATED
13-08-17 12:56:30.688879000 PM51638584295206179840INTERPOLATED
14-08-17 12:56:30.688879000 PM51638584295206179840INTERPOLATED
15-08-17 12:56:30.688879000 PM51638584295206179840INTERPOLATED
16-08-17 12:56:30.688879000 PM51638584295206179840INTERPOLATED
17-08-17 12:56:30.688879000 PM51638584295206179840INTERPOLATED
18-08-17 12:56:30.688879000 PM51638584295206179840INTERPOLATED
19-08-17 12:56:30.688879000 PM51638584295206179840INTERPOLATED
20-08-17 12:56:30.688879000 PM51638584295206179840INTERPOLATED
21-08-17 12:56:30.688879000 PM51638584295206179840INTERPOLATED
22-08-17 12:56:30.688879000 PM51638584295206179840INTERPOLATED
23-08-17 12:56:30.688879000 PM51638584295206179840INTERPOLATED
24-08-17 12:56:30.688879000 PM51638584295206179840INTERPOLATED
25-08-17 12:56:30.688879000 PM51638584295206179840INTERPOLATED
26-08-17 12:56:30.688879000 PM51638584295206179840INTERPOLATED
27-08-17 12:56:30.688879000 PM51638584295206179840INTERPOLATED
28-08-17 12:56:30.688879000 PM51638584295206179840INTERPOLATED
29-08-17 12:56:30.688879000 PM51638584295206179840INTERPOLATED
30-08-17 12:56:30.688879000 PM51638584295206179840INTERPOLATED
31-08-17 12:56:30.688879000 PM51638584295206179840INTERPOLATED
01-09-17 12:56:30.688879000 PM51638584295206179840INTERPOLATED
02-09-17 12:56:30.688879000 PM51638584295206179840INTERPOLATED
03-09-17 12:56:30.688879000 PM51638584295206179840INTERPOLATED
04-09-17 12:56:30.688879000 PM51638584295206179840INTERPOLATED
05-09-17 12:56:30.688879000 PM51638584295206179840INTERPOLATED
06-09-17 12:56:30.688879000 PM51638584295206179840INTERPOLATED
07-09-17 12:56:30.688879000 PM51638584295206179840INTERPOLATED
08-09-17 12:56:30.688879000 PM51638584295206179840PROJECTED
09-09-17 12:56:30.688879000 PM51638584295206179840PROJECTED
10-09-17 12:56:30.688879000 PM51638584295206179840PROJECTED
11-09-17 12:56:30.688879000 PM51638584295206179840PROJECTED
12-09-17 12:56:30.688879000 PM51638584295206179840PROJECTED

Leave a Reply

Your email address will not be published. Required fields are marked *