Segment Space Growth History and Forecast

I have already written articles to get tablespace space usage history and forecast (10g and 11g, 12c and above) and database space usage history and forecast (10g and 11g, 12c and above). Here, I will explain how we can get the same information about segments, and can forecast the future growth of segments.

Just like DBA_HIST_TBSPC_SPACE_USAGE which we can use to find out tablespaces space usage history from the AWR repository, we have another view, DBA_HIST_SEG_STAT, that can be used to get the historical space usage for segments, based on which a future forecast can be made. However, usage of this view has certain limitations. Information in this view is updated on every AWR snapshot, and if an instance restart happens, segments growth information between last AWR snapshot and instance restart will be lost, and would not be available in AWR; hence information of segments size would not reflect a correct value. Nevertheless, we can get the data growth information of segments since the last instance restart because information will be correct. Once we get the historical space usage of segments, we can guess about their future growth.

I always suggest setting the AWR retention to 35 days at least, and AWR capture frequency set to 15 minutes. This document explains how we change the AWR snapshot interval and retention settings.

Initially I planned to write a single script just like I wrote for tablespace/database growth history and forecast, but I found it very complex, so I will use different queries and step to find the historical growth of a segment, based on which a forecast can be made for a future growth.

Following points should be noted before I explain the steps.

• For 12c and above, execute these steps in the respective container in which the segment exists for which space usage information is needed.
• For partitioned tables/indexes, each partition is a segment, and we will need to check sizing information for each partition separately. You can add up the resulted size of each partition to get the sizing information for the whole table/index.
• Information queried using these steps, is taken from the AWR data stored in the database.
• If you have AWR retention set to 35 days, but, there was an instance restart 3 days ago, these steps would provide information based on the last 3 days AWR data, because it has already been explained above that instance restart means that some information of data insertion/deletion in/from the segment could not have been written to the AWR.
• Sizing information here is in MB. You may modify the queries to match your requirement, if you want information to be returned in GBs, or in any other unit.

Steps to Get Segment Growth and Forecast

1)
Get the object ID (and current size, if needed) for the segment about which segment space usage information is needed. For this example, I am using a table TEST which is in a RAC database with 3 instances. Same queries will run perfectly on single instance database.

SQL> select owner,object_id from dba_objects where object_name='TEST'; OWNER                           OBJECT_ID ------------------------------ ---------- SALMAN                             427055 SQL> select bytes/1024/1024 SIZE_MB from user_segments where segment_name='TEST'; SIZE_MB -------------           588

2)
Check the startup time of each instance. Most importantly, the startup time of the instance that has been started lately because query will fetch size information after that time, because it will be most reliable information as explained above.

SQL> select instance_number,max(startup_time) STARTUPT_TIME, TRUNC(sysdate-trunc(max(startup_time))) NUM_DAYS from dba_hist_snapshot group by instance_number order by 2; INSTANCE_NUMBER      STARTUP_TIME                                  NUM_DAYS -----------------------------    -------------------------------------------------------------------------               2                           31-OCT-17 02.59.47.000 PM            8               3                           01-NOV-17 12.12.26.000 AM           7               1                           01-NOV-17 06.07.33.000 PM           7

Based on above information, instance 1 was the one started lately, around 7 days ago. Our coming query to fetch the data growth information will be based on AWR data captured after 01-NOV-17 06.07.33.000 PM, that is the startup time of instance 1.

3)
Query in the above step gives information about the instance startup time. However, we need first AWR Snapshot ID after this instance restart time. Data growth information will be based on AWR data captured starting that snapshot until the latest snapshot.

SQL> select instance_number,min(snap_id) snap_id from dba_hist_snapshot where (instance_number,startup_time) in (select instance_number,max(startup_time) from dba_hist_snapshot group by instance_number) group by instance_number order by snap_id; INSTANCE_NUMBER    SNAP_ID --------------- ----------               2     220220               3     220256               1     220328

So the snapshot ID 220328 is the first snapshot taken after the instance 1 startup on 01-NOV-17 06.07.33.000 PM

4)
Following is the query that will show how much the segment TEST has grown since last 7 days (Since startup of the instance 1), how much was per day growth in last 7 days, and how much it may grow during next 30 days.
Highlighted in yellow is the value to calculate the size growth per day for last n number of days, and highlighted in red is to calculate the expected growth in next n number of days. If our last instance startup was 20 days ago, the value highlighted in yellow will be 20. To forecast the growth for next 45 days, the values highlighted in red will be 20*45

SQL> select ROUND(SUM(space_allocated/1024/1024)) growth_mb, ROUND(SUM(space_allocated/1024/1024)/7) per_day_growth_mb, ROUND(SUM(space_allocated/1024/1024)/7*30) expected_growth_mb_next30days from (select max(space_allocated_total) space_allocated from dba_hist_seg_stat where obj#=427055 and snap_id>=220328 group by instance_number); GROWTH_MB    PER_DAY_GROWTH_MB        EXPECTED_GROWTH_MB_NEXT30DAYS ---------------------  -------------------------------------   ---------------------------------------------------------                    128                                             18                                                                              549

This method of finding segment growth history and forecast best suites the steadily growing segments. Forecasting about a segment that has sudden growth in size or sporadically growing segments may not have very accurate sizing information using this method.