Skip to Main Content
  • Questions
  • Predict tablespace growth for next 30 days

Breadcrumb

Easter

Question and Answer

Connor McDonald

Thanks for the question.

Asked: June 12, 2016 - 6:39 am UTC

Answered by: Connor McDonald - Last updated: March 23, 2020 - 4:07 pm UTC

Category: Database - Version: 11g

Viewed 1000+ times

You Asked

How to Predict tablespace growth for next 30 days need to configure using oem any possible solutions

and we said...

You could use DBA_HIST_TBSPC_SPACE_USAGE to get a plot of how the tablespaces have grown in the past.

But it's a bit like those TV advertisements when people want you to invest in the stock market, when they say:

"Past behaviour is not a guaranteed predictor of future behaviour"

Just because a tablespace grows by (say) 1G each day, might miss the fact that at the end of month, it will suddenly grow by 100G due to (say) a month-end batch process.

If you application data growth has reached a steady predictable pattern, then fine. Otherwise, you need to know about and factor in sporadic events.

Once you have the data, you could use some simple regresion for a prediction, eg I'll start with 60 rows of "sizing" data for my tablespace


SQL> drop table t purge;

Table dropped.

SQL> create table t ( dte date, sz int);

Table created.

SQL>
SQL> insert into t
  2  select
  3    trunc(sysdate,'MM')-30+rownum,
  4    rownum*10 + dbms_random.value(-5,5)
  5  from dual
  6  connect by level <= 60;

60 rows created.

SQL>
SQL> select * from t order by 1;

DTE               SZ
--------- ----------
03-MAY-16          6
04-MAY-16         20
05-MAY-16         30
06-MAY-16         43
07-MAY-16         47
08-MAY-16         65
09-MAY-16         66
10-MAY-16         76
11-MAY-16         91
12-MAY-16        100
13-MAY-16        114
14-MAY-16        124
15-MAY-16        126
16-MAY-16        136
17-MAY-16        151
18-MAY-16        159
19-MAY-16        168
20-MAY-16        179
21-MAY-16        193
22-MAY-16        200
23-MAY-16        206
24-MAY-16        218
25-MAY-16        227
26-MAY-16        241
27-MAY-16        254
28-MAY-16        256
29-MAY-16        275
30-MAY-16        275
31-MAY-16        286
01-JUN-16        298
02-JUN-16        314
03-JUN-16        322
04-JUN-16        329
05-JUN-16        339
06-JUN-16        348
07-JUN-16        358
08-JUN-16        369
09-JUN-16        384
10-JUN-16        389
11-JUN-16        404
12-JUN-16        406
13-JUN-16        415
14-JUN-16        431
15-JUN-16        444
16-JUN-16        451
17-JUN-16        465
18-JUN-16        469
19-JUN-16        482
20-JUN-16        493
21-JUN-16        498
22-JUN-16        511
23-JUN-16        519
24-JUN-16        534
25-JUN-16        541
26-JUN-16        545
27-JUN-16        564
28-JUN-16        571
29-JUN-16        580
30-JUN-16        594
01-JUL-16        598

60 rows selected.

SQL>
SQL> with reg as (
  2    select regr_slope(sz, to_number(to_char(dte,'j'))) slope,
  3           regr_intercept(sz, to_number(to_char(dte,'j'))) intcpt,
  4           max(dte) nxt
  5  from t
  6  )
  7  select t.*, 'actual' tag
  8  from   t
  9  union all
 10  select nxt+r dte,
 11         trunc(intcpt+slope*to_number(to_char(nxt+r,'j')))
 12         ,'predicted'
 13  from reg,
 14       ( select rownum r
 15         from   dual
 16         connect by level <= 30 )
 17  order by 1;

DTE               SZ TAG
--------- ---------- ---------
03-MAY-16          6 actual
04-MAY-16         20 actual
05-MAY-16         30 actual
06-MAY-16         43 actual
07-MAY-16         47 actual
08-MAY-16         65 actual
09-MAY-16         66 actual
10-MAY-16         76 actual
11-MAY-16         91 actual
12-MAY-16        100 actual
13-MAY-16        114 actual
14-MAY-16        124 actual
15-MAY-16        126 actual
16-MAY-16        136 actual
17-MAY-16        151 actual
18-MAY-16        159 actual
19-MAY-16        168 actual
20-MAY-16        179 actual
21-MAY-16        193 actual
22-MAY-16        200 actual
23-MAY-16        206 actual
24-MAY-16        218 actual
25-MAY-16        227 actual
26-MAY-16        241 actual
27-MAY-16        254 actual
28-MAY-16        256 actual
29-MAY-16        275 actual
30-MAY-16        275 actual
31-MAY-16        286 actual
01-JUN-16        298 actual
02-JUN-16        314 actual
03-JUN-16        322 actual
04-JUN-16        329 actual
05-JUN-16        339 actual
06-JUN-16        348 actual
07-JUN-16        358 actual
08-JUN-16        369 actual
09-JUN-16        384 actual
10-JUN-16        389 actual
11-JUN-16        404 actual
12-JUN-16        406 actual
13-JUN-16        415 actual
14-JUN-16        431 actual
15-JUN-16        444 actual
16-JUN-16        451 actual
17-JUN-16        465 actual
18-JUN-16        469 actual
19-JUN-16        482 actual
20-JUN-16        493 actual
21-JUN-16        498 actual
22-JUN-16        511 actual
23-JUN-16        519 actual
24-JUN-16        534 actual
25-JUN-16        541 actual
26-JUN-16        545 actual
27-JUN-16        564 actual
28-JUN-16        571 actual
29-JUN-16        580 actual
30-JUN-16        594 actual
01-JUL-16        598 actual
02-JUL-16        610 predicted
03-JUL-16        620 predicted
04-JUL-16        630 predicted
05-JUL-16        640 predicted
06-JUL-16        650 predicted
07-JUL-16        660 predicted
08-JUL-16        670 predicted
09-JUL-16        681 predicted
10-JUL-16        691 predicted
11-JUL-16        701 predicted
12-JUL-16        711 predicted
13-JUL-16        721 predicted
14-JUL-16        731 predicted
15-JUL-16        741 predicted
16-JUL-16        751 predicted
17-JUL-16        761 predicted
18-JUL-16        771 predicted
19-JUL-16        781 predicted
20-JUL-16        791 predicted
21-JUL-16        801 predicted
22-JUL-16        811 predicted
23-JUL-16        821 predicted
24-JUL-16        831 predicted
25-JUL-16        841 predicted
26-JUL-16        851 predicted
27-JUL-16        861 predicted
28-JUL-16        871 predicted
29-JUL-16        881 predicted
30-JUL-16        891 predicted
31-JUL-16        901 predicted

90 rows selected.

SQL>
SQL>


Hope this helps.

and you rated our response

  (7 ratings)

We're not taking reviews currently, so please try again later if you want to add a review.

Reviews

I can see myself applying this elsewhere

June 15, 2016 - 2:11 pm UTC

Reviewer: Scott Wesley from Perth, Australia.

I plotted this information here, using APEX 5.1. See it in visual format
http://www.grassroots-oracle.com/2016/06/charting-predictions-asktom-apexea-51.html
Chris Saxon

Followup  

June 16, 2016 - 2:53 am UTC

Nice touch

Oracle SQL Query to perform Linear regression with various curve fitting techniques

March 19, 2019 - 4:37 am UTC

Reviewer: A reader from USA

I'm trying to perform a Time Series forecast on a sample data set as below using SQL linear regression, but wanted to leverage various forms of curve fitting techniques.

Cust_id Year Revnue
1 2016 579862
1 2017 605365
1 2018 ?
2 2016 61074
2 2017 60611
2 2018 ?
3 2016 170706
3 2017 70393
3 2018 ?
4 2016 31649
4 2017 19566
4 2018 ?

Below are the linear regression curve fittings, I want to achieve using the Oracle SQL. Appreciate your valuable guidance/time

Polynomial Fit: Fits data that fluctuates with a rise and a drop (x'=log(x); y'=log(y)).
Exponential Fit: Fits data points that rise or drop at an increasingly faster rate (x'=x; y'=ln(y)).
Logarithmic Fit: Fits data points that rise or drop quickly and then level off (x'=log(x); y'=y).
Asymptotic Fit: Fits data points that rise or drop until they approach a fixed value and then level off (x'=1/x; y'=1/y).
Exponential Asymptotic Fit: Fits data points that rise or drop at an increasingly faster rate until they approach a fixed value and then level off (x'=x; y'=ln(y/(K-y))).
Connor McDonald

Followup  

March 19, 2019 - 8:16 am UTC

GOOD_BAD

DBMS_SPACE

March 20, 2019 - 9:56 am UTC

Reviewer: David D.


Hello,

Is it possible to use DBMS_SPACE.OBJECT_GROWTH_TREND function to answer this question? I thought it was created especially for this kind of question.

David D.


Connor McDonald

Followup  

March 25, 2019 - 1:44 pm UTC

Possibly, but this is an object level forecast. The question was about tablespace level.

If I wanted to know the date my tablespace runs out of space??

December 16, 2019 - 7:55 pm UTC

Reviewer: Federico from Argentina

Hello,
I've adapted this example to OMS 13c metrics views for database storage usage, and I was able to draw with grafana a graph where I can see when I'm gonna run out of space (allocated) for a certain database.

But what if I don't want the forecast, but only the actual date I'll get to a certain value? For example to create a rule or alert that will tell me "hey you should expand your storage in the next X days or you will run out of space"? To make it easy using the same example than before.

Regards,
Connor McDonald

Followup  

December 18, 2019 - 6:44 am UTC

Let's say "800" is your threshold. You can write it like this

SQL> with reg as (
  2        select regr_slope(sz, to_number(to_char(dte,'j'))) slope,
  3               regr_intercept(sz, to_number(to_char(dte,'j'))) intcpt,
  4               max(dte) nxt
  5      from t
  6      )
  7     select nxt+r dte,
  8            trunc(intcpt+slope*to_number(to_char(nxt+r,'j')))
  9     from ( select slope, intcpt, nxt, rownum r
 10            from   reg
 11            connect by trunc(intcpt+slope*to_number(to_char(nxt+level,'j'))) <= 800 )
 12     order by 2 desc
 13     fetch first row only;

DTE       TRUNC(INTCPT+SLOPE*TO_NUMBER(TO_CHAR(NXT+R,'J')))
--------- -------------------------------------------------
20-JAN-20                                               799


December 18, 2019 - 8:43 pm UTC

Reviewer: A reader

Good thought

March 19, 2020 - 6:20 pm UTC

Reviewer: Nimish Garg from India

When I tried to write a SQL with similar logic, the output of liner growth was less than the max values I provided, can you please check what I have missed here

with data as
(
select to_date('20200302','yyyymmdd') dt, 6    val from dual union all
select to_date('20200303','yyyymmdd') dt, 9    val from dual union all
select to_date('20200304','yyyymmdd') dt, 32   val from dual union all
select to_date('20200305','yyyymmdd') dt, 33   val from dual union all
select to_date('20200306','yyyymmdd') dt, 34   val from dual union all
select to_date('20200307','yyyymmdd') dt, 37   val from dual union all
select to_date('20200308','yyyymmdd') dt, 43   val from dual union all
select to_date('20200309','yyyymmdd') dt, 50   val from dual union all
select to_date('20200310','yyyymmdd') dt, 65   val from dual union all
select to_date('20200311','yyyymmdd') dt, 65   val from dual union all
select to_date('20200312','yyyymmdd') dt, 77   val from dual union all
select to_date('20200313','yyyymmdd') dt, 85   val from dual union all
select to_date('20200314','yyyymmdd') dt, 100  val from dual union all
select to_date('20200305','yyyymmdd') dt, 110  val from dual union all
select to_date('20200316','yyyymmdd') dt, 114  val from dual union all
select to_date('20200317','yyyymmdd') dt, 140  val from dual union all
select to_date('20200318','yyyymmdd') dt, 170  val from dual union all
select to_date('20200319','yyyymmdd') dt, 187  val from dual
),
regresion as
(
select
regr_slope(val, to_number(to_char(dt,'j'))) slope,
regr_intercept(val, to_number(to_char(dt,'j'))) intcpt,
max(dt) nxt
from data
)
select nxt+r dt,
       trunc(intcpt+slope*to_number(to_char(nxt+r,'j')))
from regresion,
     ( select rownum r
       from   dual
       connect by level <= 7 )
order by 1;

DT        TRUNC(INTCPT+SLOPE*TO_NUMBER(TO_CHAR(NXT+R,'J')))
--------- -------------------------------------------------
20-MAR-20                                               162
21-MAR-20                                               171
22-MAR-20                                               180
23-MAR-20                                               189
24-MAR-20                                               197
25-MAR-20                                               206
26-MAR-20                                               215

Connor McDonald

Followup  

March 20, 2020 - 6:09 am UTC

This should explain why

REGR_TREND

March 20, 2020 - 7:25 am UTC

Reviewer: Nimish Garg from India

Yes, i realized the issue with linear projection. So I am now working for projecting this curve in exponential way. If you can share any sample sql, that would be really helpful.
Chris Saxon

Followup  

March 23, 2020 - 4:07 pm UTC

The problem isn't with linear growth per se. It's that the last real value is greater than the trendline prediction. This can also happen when modelling exponential growth.

You need to add the difference between the last observed value and the last predicted value. Otherwise, the model will over/under-estimate by this factor (depending on whether the trend is below the actual value or above it).

More to Explore

DBMS_RANDOM

More on PL/SQL routine DBMS_RANDOM here