ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 查找表或索引增长的历史信息

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

原创 Linux操作系统 作者:hunterjoy 时间:2012-04-07 19:54:14 0 删除 编辑

在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

本文转载自http://www.oracledatabase12g.com/archives/script-awr-get-segment-grow-history.html

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/543979/viewspace-720634/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 手工创建AWR报告
请登录后发表评论 登录
全部评论

注册时间:2007-12-31

  • 博文量
    156
  • 访问量
    352629