One of the thinks that Oracle user would expect is to find on the Web script for segment growth report/analysis for the last x days.
Although there are many scripts available, all of them (at least all scripts that I’ve checked) does not provide correct result.
As scripts for tracking segment growth are all based on space_used_delta column from the dba_hist_seg_stat view, you need to know how the whole mechanism is working to avoid all pitfalls and related bugs to get the correct results.
The main reason that all scripts provides wrong results is the way how they are calculating segment growth.
There are two most important columns in dba_objects:
and related columns from the dba_hist_seg_stat:
When you create segment, object_id and data_object_id are the same.
create table tseg_growth_test tablespace users nologging as select * from rms.tran_data_history where rownum < 10000000; select object_id, data_object_id from dba_objects where object_name = 'TSEG_GROWTH_TEST'; object_id data_object_id 6165238 6165238 select round(bytes/1024/1024,0) as mb from dba_segments where owner = user and segment_name = 'TSEG_GROWTH_TEST'; 1185 MB
But when you perform DDL (alter table move, truncate table…) on segment (table/index/LOB…), segment_id will change the value as can be seen down below:
truncate table tseg_growth_test; select * from dba_objects where object_name = 'TSEG_GROWTH_TEST'; object_id data_object_id 6165238 6165241
Let’s check behavior of DML on segment.
insert /*+ append */ into TSEG_GROWTH_TEST select * from rms.tran_data_history where rownum < 500000; commit; select * from dba_objects where object_name = 'TSEG_GROWTH_TEST'; object_id data_object_id 6165238 6165241
As you can see, the data_object_id is still the same.
Let’s check used space for the segment of interest.
select round(bytes/1024/1024,0) as mb from dba_segments where owner = user and segment_name = 'TSEG_GROWTH_TEST'; 59 MB
Let’s add more rows into the tseg_growth_test table, but not in the same snap interval (snap_id from dba_hist_snapshot).
insert /*+ append */ into trast_segmenata select * from rms.tran_data_history where rownum < 1000000; commit;
Let’s check space used by the segment.
select round(bytes/1024/1024,0) as mb from dba_segments where owner = user and segment_name = 'TSEG_GROWTH_TEST'; 176 MB
Last test is to delete all rows.
delete from tseg_growth_test; select round(bytes/1024/1024,0) as mb from dba_segments where owner = user and segment_name = 'TSEG_GROWTH_TEST'; 176 MB
Of course, space used by segment is the same, but that action won’t generate new row in dba_hist_seg_stat.
object_id data_object_id space_used_delta action
6165238 6165238 1185 MB create table tseg_growth_test
6165238 6165241 0 MB truncate table tseg_growth_test
6165238 6165241 59 MB insert 500000 rows
6165238 6165241 176 MB insert 1000000 rows
6165238 6165241 176 MB delete all rows (this rows won’t be generated)
If you summarize all values from space_used_delta column, you would get wrong results (1596 MB where real value is 176 MB).
Some scripts I’ve found on web are using avg instead of sum, but that’s also wrong.
To get correct result you need to use the latest data_object_id for some object_id and time (snap_id) interval.
My version of the script had one limitation: LOBs segment calculation were still wrong.
While start testing with LOB, Oracle Support engineer told me to use space_allocated_delta instead of space_used_delta for LOBs.
I’ve tested suggested workaround, and can confirm, with acceptable error (10-15%), it is working fine.
Finally, I’m providing here correct version of the script. You need to enter number of days you want to track back segment growth (limitation is AWR and how far you can go in the past).
with snapshot_tmp as ( select /*+ materialize */ min(snap_id) snap from dba_hist_snapshot sn where sn.begin_interval_time > sysdate - &days_back order by 1), seg_stat_tmp as ( select /*+ materialize */ a.obj#, round(sum(space_used_delta)/1024/1024,0) as space_growth_mb1, round(sum(space_allocated_delta)/1024/1024,0) as space_growth_lob_mb1 from dba_hist_seg_stat a where snap_id >= (select snap from snapshot_tmp) and a.dataobj# = (select max(y.dataobj#) from dba_hist_seg_stat y where y.obj# = a.obj#) group by a.obj# ), obj_tmp as ( select /*+ materialize */ b.owner, (case when b.subobject_name is null then b.object_name else b.subobject_name end) obj_name, case when b.object_type in ('TABLE', 'TABLE PARTITION', 'TABLE SUBPARTITION') then b.object_name when b.object_type in ('INDEX', 'INDEX PARTITION', 'INDEX SUBPARTITION') then (select i.table_name from dba_indexes i where i.owner = b.owner and i.index_name = b.object_name) when b.object_type = 'LOB' then (select l.table_name from dba_lobs l where l.owner = b.owner and l.segment_name = b.object_name) when b.object_type = 'LOB PARTITION' then (select max(p.table_name) from dba_lob_partitions p where p.table_owner = b.owner and p.lob_name = b.object_name) else b.object_name end parent_obj_name, b.object_type, b.object_id, a.space_growth_mb1, space_growth_lob_mb1 from dba_objects b, seg_stat_tmp a where b.object_id = a.obj# ) select * from ( select owner, obj_name, parent_obj_name, object_type, sum(case when object_type in ('LOB', 'LOB PARTITION') then space_growth_lob_mb1 else space_growth_mb1 end) as space_growth_mb from obj_tmp group by owner, obj_name, parent_obj_name, object_type ) where space_growth_mb >=10 order by space_growth_mb desc nulls last;
I’ve packed the script into the scheduled store procedure that sends E-mail to DBA team every morning, but I’ll left to you to finish that part.