有同学在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
/
使用示例：
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> conn maclean/maclean Connected. SQL> SQL> SQL> SQL> create table check_size tablespace users as select * from dba_objects where rownum=0; Table created. SQL> exec dbms_workload_repository.create_snapshot; PL/SQL procedure successfully completed. SQL> insert into check_size select * from dba_objects; 75536 rows created. SQL> insert into check_size select * from check_size; 75536 rows created. SQL> / 151072 rows created. SQL> commit; Commit complete. SQL> insert into check_size select * from check_size; 302144 rows created. SQL> insert into check_size select * from check_size; 604288 rows created. SQL> commit; Commit complete. SQL> alter system checkpoint; System altered. SQL> exec dbms_workload_repository.create_snapshot; PL/SQL procedure successfully completed. SQL> @seg_hist OWNER OBJECT_NAME START_DAY BLOCK_INCREASE ---------------- ------------------------------------ ----------- -------------- DBSNMP BSLN_STATISTICS 2012-MAR-18 224 DBSNMP BSLN_STATISTICS_PK1 2012-MAR-18 192 MACLEAN CHECK_SIZE 2012-MAR-22 96176 SH CUSTOMERS 2012-MAR-17 0 SQL> select data_object_id from dba_objects where object_name='CHECK_SIZE'; DATA_OBJECT_ID -------------- 78062 SQL> select seg.snap_id, 2 seg.ts#, 3 seg.space_used_total, seg.space_allocated_total, seg.SPACE_ALLOCATED_DELTA from dba_hist_seg_stat seg where seg.DATAOBJ#=78062 / 4 5 6 7 8 SNAP_ID TS# SPACE_USED_TOTAL SPACE_ALLOCATED_TOTAL SPACE_ALLOCATED_DELTA ---------- ---------- ---------------- --------------------- --------------------- 354 4 30909079 36700160 36700160 355 4 123645655 149946368 113246208 SPACE_USED_DELTA Delta value for space used SPACE_ALLOCATED_DELTA Delta value for space allocated
Leave a Reply