Oracle表变化趋势追踪记录 & 表 历史统计信息查看

Oracle表变化趋势追踪记录

DBA_HIST_SEG_STAT可以看出对象的使用趋势，构造如下SQL查询出每个时间段内数据库对象的增长量，其中DB_BLOCK_CHANGES_DELTA为块个数

select c.SNAP_ID,
to_char(c.END_INTERVAL_TIME, 'yyyy-mm-dd') SNAP_TIME,
a.OWNER,
a.OBJECT_NAME,
a.OBJECT_TYPE,
b.DB_BLOCK_CHANGES_DELTA
from dba_objects a,
(select SNAP_ID, obj#, DB_BLOCK_CHANGES_DELTA
from DBA_HIST_SEG_STAT
where DB_BLOCK_CHANGES_DELTA > 20000
order by snap_id desc, DB_BLOCK_CHANGES_DELTA desc) b,
DBA_HIST_SNAPSHOT c
where a.object_id = b.obj#
and object_type = 'TABLE'
and b.SNAP_ID = c.SNAP_ID
order by DB_BLOCK_CHANGES_DELTA
;

下面的SQL查询出某个时间段内，数据库对象的变化大小，单位以换算成GB

select a.OBJECT_NAME,
to_char(c.END_INTERVAL_TIME, 'yyyy-mm-dd') SNAP_TIME,
sum(b.DB_BLOCK_CHANGES_DELTA) / 1024 / 1024 GB
from dba_objects a,
(select
SNAP_ID, obj#, DB_BLOCK_CHANGES_DELTA
from DBA_HIST_SEG_STAT
where DB_BLOCK_CHANGES_DELTA > 20000
order by snap_id desc, DB_BLOCK_CHANGES_DELTA desc) b,
DBA_HIST_SNAPSHOT c
where a.object_id = b.obj#
and object_type = 'TABLE'
and b.SNAP_ID = c.SNAP_ID
group by a.OBJECT_NAME, c.END_INTERVAL_TIME
order by GB
;

Oracle表 历史统计信息查看
select obj#, savtime, rowcnt, blkcnt, avgrln, analyzetime
from sys.wri$_optstat_tab_history
where obj# = --5789486
(select object_id
from dba_objects
where object_name = 'RPT_3DNAND_JGS_SEGMENT_TARGET' and owner='MIDB01')
order by savtime;

FAQ: Database Performance Frequently Asked Questions (Doc ID 1360119.1)