Oracle Table Growth Prediction

Most production databases grow over the course of time. Planning for growth is a very important task of every professional Oracle DBA. If resources are carefully planned out well in advance, such problems as the system being out of space are likely to be avoided.  Of course, alerts will be generated when the space utilization crosses established alert thresholds. It is very good when the DBA proactively resolves such space related issues.

One of the most important features of Oracle10g is its ability to predict the growth of the segments. The object_growth_trend prediction mechanism is based on data collected and stored by the AWR, and the growth trend reporting is also built into the Oracle database kernel and is available by default. The active space monitoring of individual segments in the database gives the up-to-the-minute status of individual segments in the system available to the database. This provides sufficient information, over time, to perform growth trending of individual objects in the database as well as the database as a whole.

The query below allows the estimation of the segment growth trend for the stats$sysstat table:

SQL> select

*

from

table(dbms_space.OBJECT_GROWTH_TREND

('PERFSTAT','STATS$SYSSTAT','TABLE'));

The output of this query might look like this, showing the growth trend for the table.  This is very useful for forecasting database growth and planning future disk storage needs:

TIMEPOINT                      SPACE_USAGE SPACE_ALLOC QUALITY

------------------------------ ----------- ----------- ------------

02.10.04 15:58:04,218000            592359     1048576 INTERPOLATED

03.10.04 15:58:04,218000            592359     1048576 INTERPOLATED

19.10.04 15:58:04,218000            592359     1048576 INTERPOLATED

20.10.04 15:58:04,218000            592359     1048576 INTERPOLATED

21.10.04 15:58:04,218000            592359     1048576 GOOD

22.10.04 15:58:04,218000            786887     1048576 INTERPOLATED

23.10.04 15:58:04,218000            826610     1048576 INTERPOLATED

31.10.04 15:58:04,218000           3072829     3145728 INTERPOLATED

01.11.04 15:58:04,218000           3072829     3145728 INTERPOLATED

02.11.04 15:58:04,218000           3678280     3678280 PROJECTED

03.11.04 15:58:04,218000           3764774     3764774 PROJECTED

04.11.04 15:58:04,218000           3851267     3851267 PROJECTED

05.11.04 15:58:04,218000           3937760     3937760 PROJECTED

06.11.04 15:58:04,218000           4024253     4024253 PROJECTED

The space_usage column shows how many bytes the stats$sysstat table actually consumes, and space_alloc reports the size, in bytes, of space used by the table.

This is an excerpt from my latest book "Oracle Tuning: The Definitive Reference".  You can buy it direct from the publisher for 50%-off and get instant access to the code depot of Oracle tuning scripts:

http://www.rampant-books.com/book_1002_oracle_tuning_definitive_reference_2nd_ed.htm