No question at this time
DBA Top 10
1 M. Cadot 6300
2 B. Vroman 2700
3 P. Tsongayinwe 1500
4 P. Wisse 1300
4 J. Péran 1300
6 A. Kavsek 900
7 D. Walgude 400
7 J. Schnackenberg 400
9 J. Alcroft 200
10 A. Hudspith 100
10 L. Ywema 100
10 D. Johnson 100
10 B. B 100
About
DBA-Village
The DBA-Village forum
Forum as RSS
as RSS feed
Site Statistics
Ever registered users48709
Total active users1322
Act. users last 24h2
Act. users last hour0
Registered user hits last week33
Registered user hits last month217
Go up

How to follow the growth of your database
Next thread: Invalid Privilege
Prev thread: script

Message Score Author Date
Hi, To add something to my tips "How to follow ...... Michel Cadot Jan 19, 2004, 11:48
Hi, Following email received on my personal mai...... Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts Michel Cadot Jan 19, 2004, 19:12
Perhaps I'm missing something here. Your first sq...... Myron Wintonyk Jan 26, 2004, 23:41
Hi, This is because the first query shows the r...... Michel Cadot Jan 29, 2004, 11:19
Hi Michel, Could you please explain me your tips ...... Balaji M Nov 15, 2021, 09:00
The referenced tip is at: https://www.dba-villa...... Michel Cadot Nov 15, 2021, 10:08
Thank you... Balaji M Nov 18, 2021, 08:10

Follow up by mail Click here


Subject: How to follow the growth of your database
Author: Michel Cadot, France
Date: Jan 19, 2004, 11:48, 6888 days ago
Os info: Any
Oracle info: >=8i
Message: Hi,

To add something to my tips "How to follow the growth of your database" in Tips/SQL*Plus.

Once you have filled the table with data, you can follow the growth of, for instance, one of your tablespace using analytical functions.
I take here the example of tablespace SYSTEM.

SQL> select heure, sum(used_bytes), sum(max_bytes) from watch_file
2 where tablespace_name='SYSTEM'
3 group by heure;

HEURE SUM(USED_BYTES) SUM(MAX_BYTES)
---------- --------------- --------------
01/09/2004 130185296 214748365
01/10/2004 133968366 214748365
01/11/2004 137531436 214748365
01/12/2004 140874505 214748365
01/13/2004 143997574 214748365
01/14/2004 146900644 214748365
01/15/2004 149583714 214748365
01/16/2004 152046782 214748365
01/17/2004 154289852 214748365
01/18/2004 156312922 214748365
01/19/2004 158115991 214748365

11 rows selected.

Using the linear regression you can:

1- Estimate the size of the tablespace in 10 days:

SQL> select regr_intercept(used_bytes,heure-trunc(sysdate))+
2 regr_slope(used_bytes,heure-trunc(sysdate))*10 "Space used in 10 Days"
3 from (select heure, tablespace_name, sum(used_bytes) used_bytes
4 from watch_file group by heure, tablespace_name)
5 where tablespace_name='SYSTEM'
6 /

Space used in 10 Days
---------------------
187696686

1 row selected.

2- Estimate when all the space will be occupied:

SQL> select (m.max_bytes-regr_intercept(used_bytes,heure-trunc(sysdate)))
2 / regr_slope(used_bytes,heure-trunc(sysdate)) "Lack of space in"
3 from (select heure, tablespace_name, sum(used_bytes) used_bytes
4 from watch_file group by heure, tablespace_name) u,
5 (select tablespace_name, sum(max_bytes) max_bytes
6 from watch_file w
7 where heure = (select max(heure) from watch_file w2
8 where w2.tablespace_name=w.tablespace_name)
9 group by tablespace_name) m
10 where u.tablespace_name = 'SYSTEM'
11 and m.tablespace_name = 'SYSTEM'
12 group by m.max_bytes
13 /

Lack of space in
----------------
19.6852869

1 row selected.


Regards
Michel
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: How to follow the growth of your database
Author: Michel Cadot, France
Date: Jan 19, 2004, 19:12, 6888 days ago
Score:   Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts
Message: Hi,

Following email received on my personal mailbox, i specify the functions used.
In the functions regr_slope and regr_intercept, the first parameter is interpreted as a value of the dependent variable (a "y value"), and the second parameter is interpreted as a value of the independent variable (an "x value").
REGR_SLOPE returns the slope of the regression line.
REGR_INTERCEPT returns the y-intercept of the regression line.

In my previous example, you can calculate them:

SQL> select regr_intercept(used_bytes,heure-trunc(sysdate)) origin,
2 regr_slope(used_bytes,heure-trunc(sysdate)) slope
3 from (select heure, tablespace_name, sum(used_bytes) used_bytes
4 from watch_file group by heure, tablespace_name)
5 where tablespace_name='SYSTEM';

ORIGIN SLOPE
---------- ----------
159765991 2793069.46

1 row selected.

Regards
Michel
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: How to follow the growth of your database
Author: Myron Wintonyk, Canada
Date: Jan 26, 2004, 23:41, 6880 days ago
Message: Perhaps I'm missing something here. Your first sql tells me that in 10 days, you used 187696686 bytes. But, the size grew from 130185296 to 158115991. Perhaps I'm just daft, but that seems like 27930695.
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Re: How to follow the growth of your database
Author: Michel Cadot, France
Date: Jan 29, 2004, 11:19, 6878 days ago
Message: Hi,

This is because the first query shows the reality, that is what is calculated by summing the "bytes" values of dba_data_files, and the last one is the result of a linear regression that is the nearest line representing the data.
You can also see the difference on the origin (current day of the queries), the real size was 158115991 and the linear regression gave 159765991.
Actually, my data are rather quadratic and are bad represented with a line. So the slope is higher than the past average slope ((last_value-first_value)/number of days) because the linear regression anticipates that the slope will grow.
Depending on your data you have to choose your regression function. This was just an example (not the more accurate one :)

Regards
Michel
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Re: How to follow the growth of your database
Author: Balaji M, India
Date: Nov 15, 2021, 09:00, 378 days ago
Message: Hi Michel,
Could you please explain me your tips and steps on database growth.

Regards,
Balaji
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Re: How to follow the growth of your database
Author: Michel Cadot, France
Date: Nov 15, 2021, 10:08, 378 days ago
Message:
The referenced tip is at:
https://www.dba-village.com/village/dvp_tips.TipDetails?TipIdA=1770

Note that you can now have about the same thing with DBA_HIST_TABLESPACE and DBA_HIST_TBSPC_SPACE_USAGE views, if you are licensed to query them.

Regards
Michel
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Re: How to follow the growth of your database
Author: Balaji M, India
Date: Nov 18, 2021, 08:10, 375 days ago
Message: Thank you
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here