计算段增涨量的正确方法

跟踪段增长的脚本基本都基于 dba_hist_seg_stat 视图中的 space_used_delta 列，因此了解整个机制是如何工作的非常重要，以避免所有陷阱和相关错误以获得正确的结果。
dba_objects 中有两个最重要的列：
– object_id
– data_object_id

以及 dba_hist_seg_stat 中的相关列：
– obj#
– dataobj#象#

创建段时，object_id 和 data_object_id 是相同的

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

但是当对段（表/索引/LOB…）执行DDL（alter table move, truncate table…）时，segment_id 将改变

truncate table tseg_growth_test;
select * from dba_objects where object_name = 'TSEG_GROWTH_TEST';
object_id 	data_object_id
6165238		6165241

再次验证

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

data_object_id 仍然相同，再看看空间情况

select round(bytes/1024/1024,0) as mb from dba_segments where owner = user and segment_name = 'TSEG_GROWTH_TEST';
59 MB

在 tseg_growth_test 表中添加更多的数据，但不要在同一个快照间隔内（dba_hist_snapshot 的 snap_id）

insert /*+ append */ into trast_segmenata
select * from rms.tran_data_history where rownum < 1000000;
commit;

再验证使用的空间

select round(bytes/1024/1024,0) as mb from dba_segments where owner = user and segment_name = 'TSEG_GROWTH_TEST';
176 MB 

最后测试删除所有数据

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 

当然，段使用的空间是相同的，但该操作不会在 dba_hist_seg_stat 中生成新的行，原因是delete并不会回收空间

如果汇总 space_used_delta 列中的所有值，则会得到错误的结果（1596 MB，实际值为 176 MB）。
我在网上找到的一些脚本使用 avg 而不是 sum，但这也是错误的。
要获得正确的结果，您需要对某些 object_id 和时间 (snap_id) 间隔使用最新的 data_object_id。
这个脚本版本有一个限制：LOB 段计算仍然错误。
在开始使用 LOB 进行测试时，对 LOB 使用 space_allocated_delta 而不是 space_used_delta。
我已经测试了该方法，可以确认，对于LOB有可接受的错误 (10-15%)。
最后，我在这里提供了正确版本的脚本。 您需要输入要跟踪段增长的天数（限制是 AWR 以及过去可以走多远）。

with snapshot_tmp as
(
select /*+ materialize */ min(snap_id) snap from dba_hist_snapshot sn
where to_char(sn.begin_interval_time,'YYYY-MM-DD') between q'(&start_date)' and q'(&end_date)' 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
)
order by space_growth_mb desc nulls last;

如果要计算平均增长量，可以再套一层round(sum(space_growth_mb)/7,2)

select round(sum(space_growth_mb)/7,2) from
(
with snapshot_tmp as
(
select /*+ materialize */ min(snap_id) snap from dba_hist_snapshot sn
where to_char(sn.begin_interval_time,'YYYY-MM-DD') between q'(&start_date)' and q'(&end_date)' 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
)
order by space_growth_mb desc nulls last
)