当前位置：首页 >

oracle+查表物理块数,如何统计一段时间内发生在某个表上的物理读写的块数

发布时间：2023/12/4 67 豆豆

生活随笔收集整理的这篇文章主要介绍了 oracle+查表物理块数,如何统计一段时间内发生在某个表上的物理读写的块数小编觉得挺不错的,现在分享给大家,帮大家做个参考.

script from maclean

http://www.askmaclean.com/archiv ... t-grow-history.html

Script:查找表或索引增长的历史信息

MARCH 22, 2012 BY MACLEAN LIU 4 COMMENTS

有同学在Oracle ALL STARS群中提问如何通过AWR来查找一段时间内，数据库段对象(堆表、索引)等的空间增长信息。

在Oracle 10g开始awr自动负载仓库引入了dba_hist_seg_stat视图，该视图记录了快照时间内segment-level段级的历史统计信息：DBA_HIST_SEG_STAT displays historical information about segment-level statistics. This view captures the top segments based on a set of criteria and captures information from V$SEGSTAT. The total value is the value of the statistics since instance startup. The delta value is the value of the statistics from the BEGIN_INTERVAL_TIME to the END_INTERVAL_TIME in the DBA_HIST_SNAPSHOT view.

我们可以通过以下SQL脚本来列出相关段对象在快照时间内的使用空间的历史变化信息：

column owner format a16

column object_name format a36

column start_day format a11

column block_increase format 9999999999

select obj.owner, obj.object_name,

to_char(sn.BEGIN_INTERVAL_TIME,'RRRR-MON-DD') start_day,

sum(a.db_block_changes_delta) block_increase

from dba_hist_seg_stat a,

dba_hist_snapshot sn,

dba_objects obj

where sn.snap_id = a.snap_id

and obj.object_id = a.obj#

and obj.owner not in ('SYS','SYSTEM')

and end_interval_time between to_timestamp('01-JAN-2000','DD-MON-RRRR')

and to_timestamp('02-FEB-2013','DD-MON-RRRR')

group by obj.owner, obj.object_name,

to_char(sn.BEGIN_INTERVAL_TIME,'RRRR-MON-DD')

order by obj.owner, obj.object_name

修改可得：