Oracle Top Growing Segments

This is the second post of a serie. First one here.

Now that you already have an idea regarding the size of the database top segments (first post), you might want to check the top growing segments, considering a specified number of days back.

You probably used some AWR information in past or generated an AWR report, at least. But if this is still new to you, AWR stands for Automatic Workload Repository. AWR is a built-in repository, used to collect, process, and maintain performance statistics for problem detection and self-tuning purposes. This gathered data is stored both in memory and in the database, and is displayed in both reports and views.

For additional information, you can check this official doc here.

We are going to use some AWR views:
– dba_hist_seg_stat: historical information captured from V$SEGSTAT about segment-level statistics.
– dba_hist_seg_stat_obj: names of the segments captured in the workload repository.

The information I’m using to estimate the object growth comes from the column SPACE_USED_DELTA. The delta values captured by the AWR snapshots represent the changes for each statistic over the time period. Note that I’m using SUM function to group the results by OBJECT_NAME and OBJECT_TYPE, so the growth for a partitioned table will consider all the partitions. If you want to consider the growth for each partition, just uncomment the SUBOBJECT_NAME column and update the order by clause to reflect the new location of GROWTH_MB:

select
so.owner,
so.object_name,
--so.subobject_name,
so.object_type,
so.tablespace_name,
round(sum(ss.space_used_delta)/1024/1024) growth_mb
from
dba_hist_seg_stat ss,
dba_hist_seg_stat_obj so
where
ss.obj# = so.obj#
and ss.dataobj# = so.dataobj#
and so.owner != '** MISSING **' -- segments already gone
and so.object_name not like 'BIN$%' -- recycle-bin
and so.object_type not like 'LOB%'
and ss.snap_id > (
select min(sn.snap_id)
from dba_hist_snapshot sn
where
sn.dbid = (select dbid from v$database)
and sn.end_interval_time > trunc(sysdate) - &DAYS_BACK
)
group by
so.owner,
so.object_name,
--so.subobject_name,
so.object_type,
so.tablespace_name
order by 5 desc
fetch first &TOP rows only;


Reminder: The queries used in this series were tested on 12.1.0.2 databases. Some of these use the “FETCH FIRST” clause to limit the number of rows returned, but if you are using older versions of Oracle Database, you can still use the old fashion like “ROWNUM”.

select * from (
... your select here ...
) where rownum <= &TOP;

Parameters:
&DAYS_BACK – specify how many days back you want the query to consider (please note that this number will depend on the AWR snapshots retention configured for your database).
&TOP – specify how many lines your query should return.

Filters you might want to use:
and so.object_type in (‘&OBJECT_TYPE’)
example: ‘TABLE’, ‘TABLE PARTITION’, ‘INDEX’…

Output example: