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

有同学在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

Comments

  1. liu.maclean says

    Why Does Space_Allocated_Total Column On Table Wrh$_Seg_Stat Contain Negative Numbers?

    Applies to:
    Oracle Server – Enterprise Edition – Version: 10.2.0.5 to 11.2.0.2 – Release: 10.2 to 11.2
    Information in this document applies to any platform.
    Goal
    When looking at the space_allocated_total column on table wrh$_seg_stat table there are many rows with negative numbers, how is this possible?

    SQL> select count(*) from wrh$_seg_stat where space_allocated_total select count(*) from wrh$_seg_stat where space_allocated_total create table scott.emp2 as select * from scott.emp;
    Table created.

    SQL> exec dbms_workload_repository.create_snapshot;
    PL/SQL procedure successfully completed.

    SQL> insert into scott.emp2 select * from scott.emp2;
    14 rows created.


    … repeat several times to build out the segments

    SQL> insert into scott.emp2 select * from scott.emp2;
    458752 rows created.

    SQL> commit;
    Commit complete.

    SQL> exec dbms_workload_repository.create_snapshot;
    PL/SQL procedure successfully completed.

    SQL> select count(*) from wrh$_seg_stat where space_allocated_total select object_id from dba_objects where object_name = ‘EMP2’;

    OBJECT_ID
    ———-
    82087

    SQL> select space_allocated_total from wrh$_seg_stat where obj# = 82087;

    SPACE_ALLOCATED_TOTAL
    ———————
    65536
    45088768

    SQL> truncate table scott.emp2;
    Table truncated.

    SQL> exec dbms_workload_repository.create_snapshot;
    PL/SQL procedure successfully completed.

    SQL> select space_allocated_total from wrh$_seg_stat where obj# = 82087;

    SPACE_ALLOCATED_TOTAL
    ———————
    65536
    45088768
    45088768
    -45023232

    SQL> select count(*) from wrh$_seg_stat where space_allocated_total < 0;

    COUNT(*)
    ———-
    1

    So essentially whenever an object shrinks in size between snapshots, negative values are possible and expected. Please note that view dba_hist_seg_stat might also exhibit similar behavior since it is a view that selects part of its data from table wrh$_seg_stat.
    References
    BUG:5842950 – GETTING NEGATIVE VALUES FOR SPACE_USED_TOTAL COLUMN IN THE DBA_HIST_SEG_STAT
    BUG:7175447 – DBA_HIST_SEG_STAT SEEMS TO BE GIVING INCORRECT DATA

  2. elvis says

    你好,看了你的脚本我有个疑问。sum(a.db_block_changes_delta) block_increase 你这个算出来的是块增大的大小
    如果我换成GB是不是sum(a.db_block_changes_delta)/1024/1024/1024,我的db_block_size是8196,还是这样换算成GB,sum(a.db_block_changes_delta)*8096/1024/1024/1024,请看下这两个哪个是正确的谢谢!

  3. DB_BLOCK_CHANGES_DELTA 应给也可以为负数吧?

  4. 153279584 says

    db_block_changes_delta 这个应该是数据库块改变,并不全是increase 的。多了update和delete这里是一个估计,并不精确,是这样吗?

  5. 我觉得如果只是要查数据的增量的话可以用SUM(a.SPACE_ALLOCATED_DELTA)/1024/1024/1024 “DATA_increase(GB)” 这个字段来查。

Comment

*

沪ICP备14014813号-2

沪公网安备 31010802001379号