Calculating segment growth and related issues

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:
– object_id
– data_object_id

and related columns from the dba_hist_seg_stat:
– obj#
– dataobj#

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.

Get notified when a new post is published!

Name*

Email*

 Please accept terms & conditions