查询出每个时间段内数据库对象的增长量和查看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_JGS_SEGMENT_TARGET' and owner='USER01')
order by savtime;

这篇关于查询出每个时间段内数据库对象的增长量和查看Oracle表 历史统计信息的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！