ITPub博客

首页 > 数据库 > Oracle > 整理表碎片

整理表碎片

原创 Oracle 作者:jelephant 时间:2015-10-27 15:38:00 0 删除 编辑
一:准备测试数据,使用HR用户,创建T1表,插入约30W的数据,并根据object_id创建普通索引,表占存储空间34M左右,索引占6M左右的存储空间
    SQL> conn /as sysdba
    已连接。
    SQL> select default_tablespace from dba_users where username='HR';
     
    DEFAULT_TABLESPACE
    ------------------------------------------------------------
    USERS
     
    SQL> conn hr/hr
    已连接。
     
    SQL> insert into t1 select * from t1;
    已创建 74812 行。
     
    SQL> insert into t1 select * from t1;
    已创建 149624 行。
     
    SQL> commit;
    提交完成。
     
    SQL> create index idx_t1_id on t1(object_id);
    索引已创建。
     
    SQL> exec dbms_stats.gather_table_stats('HR','T1',CASCADE=>TRUE);
    PL/SQL 过程已成功完成。
     
    SQL> select count(1) from t1;
     
      COUNT(1)
    ----------
        299248
     
    SQL> select sum(bytes)/1024/1024 from dba_segments where segment_name='T1';
    SUM(BYTES)/1024/1024
    --------------------
                 34.0625
     
    SQL> select sum(bytes)/1024/1024 from dba_segments where segment_name='IDX_T1_ID';
    SUM(BYTES)/1024/1024
    --------------------
                       6
 
二:估算表在高水位线下还有多少空间可用,这个值应当越低越好,表使用率越接近高水位线,全表扫描所做的无用功也就越少!
DBMS_STATS包无法获取EMPTY_BLOCKS统计信息,所以需要用analyze命令再收集一次统计信息
    SQL> SELECT blocks, empty_blocks, num_rows FROM user_tables WHERE table_name ='T1';
     
        BLOCKS EMPTY_BLOCKS NUM_ROWS
    ---------- ------------ ----------
          4302 0 299248
     
    SQL> analyze table t1 compute statistics;
    表已分析。
     
    SQL> SELECT blocks, empty_blocks, num_rows FROM user_tables WHERE table_name ='T1';
     
        BLOCKS EMPTY_BLOCKS NUM_ROWS
    ---------- ------------ ----------
          4302 50 299248
     
    SQL> col table_name for a20
    SQL> SELECT TABLE_NAME,
      2 (BLOCKS * 8192 / 1024 / 1024) -
      3 (NUM_ROWS * AVG_ROW_LEN / 1024 / 1024) "Data lower than HWM in MB"
      4 FROM USER_TABLES
      5 WHERE table_name = 'T1';
     
    TABLE_NAME Data lower than HWM in MB
    -------------------- -------------------------
    T1 5.07086182
三: 查看执行计划,全表扫描大概需要消耗CPU 1175
    SQL> explain plan for select * from t1;
    已解释。
     
    SQL> select * from table(dbms_xplan.display);
     
    PLAN_TABLE_OUTPUT
    --------------------------------------------------------------------------------
    Plan hash value: 3617692013
    --------------------------------------------------------------------------
    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
    --------------------------------------------------------------------------
    | 0 | SELECT STATEMENT | | 299K| 28M| 1175 (1)| 00:00:15 |
    | 1 | TABLE ACCESS FULL| T1 | 299K| 28M| 1175 (1)| 00:00:15 |
    --------------------------------------------------------------------------
四:删除大部分数据,收集统计信息,全表扫描依然需要消耗CPU 1168
    SQL> delete from t1 where object_id>100;
    已删除298852行。
     
    SQL> commit;
    提交完成。
     
    SQL> select count(*) from t1;
     
      COUNT(*)
    ----------
           396
     
    SQL> exec dbms_stats.gather_table_stats('HR','T1',CASCADE=>TRUE);
    PL/SQL 过程已成功完成。
     
    SQL> analyze table t1 compute statistics;
    表已分析。
     
    SQL> SELECT blocks, empty_blocks, num_rows FROM user_tables WHERE table_name ='T1';
     
        BLOCKS EMPTY_BLOCKS NUM_ROWS
    ---------- ------------ ----------
          4302 50 396
     
     
    SQL> explain plan for select * from t1;
    已解释。
     
    SQL> select * from table(dbms_xplan.display);
     
    PLAN_TABLE_OUTPUT
    ------------------------------------------------------------------------------
    Plan hash value: 3617692013
    --------------------------------------------------------------------------
    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
    --------------------------------------------------------------------------
    | 0 | SELECT STATEMENT | | 396 | 29700 | 1168 (1)| 00:00:15 |
    | 1 | TABLE ACCESS FULL| T1 | 396 | 29700 | 1168 (1)| 00:00:15 |
    --------------------------------------------------------------------------
五:估算表在高水位线下还有多少空间是无数据的,但在全表扫描时又需要做无用功的数据
    SQL> SELECT TABLE_NAME,
      2 (BLOCKS * 8192 / 1024 / 1024) -
      3 (NUM_ROWS * AVG_ROW_LEN / 1024 / 1024) "Data lower than HWM in MB"
      4 FROM USER_TABLES
      5 WHERE table_name = 'T1';
     
    TABLE_NAME Data lower than HWM in MB
    -------------------- -------------------------
    T1 33.5791626
六:对表进行碎片整理,重新收集统计信息
    SQL> alter table t1 enable row movement;
    表已更改。
     
    SQL> alter table t1 shrink space cascade;
    表已更改。
     
    SQL> select sum(bytes)/1024/1024 from dba_segments where segment_name='T1';
     
    SUM(BYTES)/1024/1024
    --------------------
                    .125
     
    SQL> select sum(bytes)/1024/1024 from dba_segments where segment_name='IDX_T1_ID
    ';
     
    SUM(BYTES)/1024/1024
    --------------------
                   .0625
     
    SQL> SELECT TABLE_NAME,
      2 (BLOCKS * 8192 / 1024 / 1024) -
      3 (NUM_ROWS * AVG_ROW_LEN / 1024 / 1024) "Data lower than HWM in MB"
      4 FROM USER_TABLES
      5 WHERE table_name = 'T1';
     
    TABLE_NAME Data lower than HWM in MB
    -------------------- -------------------------
    T1 33.5791626
     
    SQL> exec dbms_stats.gather_table_stats('HR','T1',CASCADE=>TRUE);
    PL/SQL 过程已成功完成。
     
    这个时候,只剩下0.1M的无用功了,执行计划中,全表扫描也只需要消耗CPU 3
    SQL> SELECT TABLE_NAME,
      2 (BLOCKS * 8192 / 1024 / 1024) -
      3 (NUM_ROWS * AVG_ROW_LEN / 1024 / 1024) "Data lower than HWM in MB"
      4 FROM USER_TABLES
      5 WHERE table_name = 'T1';
     
    TABLE_NAME Data lower than HWM in MB
    -------------------- -------------------------
    T1 .010738373
     
     
    SQL> select * from table(dbms_xplan.display);
     
    PLAN_TABLE_OUTPUT
    --------------------------------------------------------------------------------
    Plan hash value: 3617692013
    --------------------------------------------------------------------------
    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
    --------------------------------------------------------------------------
    | 0 | SELECT STATEMENT | | 396 | 29700 | 3 (0)| 00:00:01 |
    | 1 | TABLE ACCESS FULL| T1 | 396 | 29700 | 3 (0)| 00:00:01 |
    --------------------------------------------------------------------------
     
    总共只有5个块,空块却有50个,明显empty_blocks信息过期
    SQL> select blocks,empty_blocks,num_rows from user_tables where table_name='T1';
     
        BLOCKS EMPTY_BLOCKS NUM_ROWS
    ---------- ------------ ----------
             5 50 396
     
    SQL> analyze table t1 compute statistics;
    表已分析。
     
    SQL> select blocks,empty_blocks,num_rows from user_tables where table_name='T1';
     
     
        BLOCKS EMPTY_BLOCKS NUM_ROWS
    ---------- ------------ ----------
             5 3 396

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

上一篇: DG搭建
下一篇: RAC故障诊断脚本
请登录后发表评论 登录
全部评论

注册时间:2013-12-07

  • 博文量
    143
  • 访问量
    626873