No question at this time
The DBA-Village forum
as RSS feed
Site Statistics| Ever registered users | 48757 | | Total active users | 1320 | | Act. users last 24h | 0 | | Act. users last hour | 0 | | Registered user hits last week | 24 | | Registered user hits last month | 1443 |
|
Go up
How to follow the growth of your database
| 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...... |
  |
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 |
| Subject: |
How to follow the growth of your database |
| Author: |
Michel Cadot, France |
| Date: |
Jan 19, 2004, 11:48, 7189 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, 7188 days ago |
| Score: |
|
| 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, 7181 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, 7179 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, 679 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, 679 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, 676 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
|