当前位置:首页 > 开发 > 数据库 > 正文

Oracle表高水平位的优化与监控

发表于: 2015-02-13   作者:czmmiao   来源:转载   浏览:
摘要: 高水平位虚高的案例 --构造表drop table t purge;create table t as select * from dba_objects;insert into t select * from t;insert into t select * from t;insert into t select * from t;insert into t select * from t;i

高水平位虚高的案例

--构造表
drop table t purge;
create table t as select * from dba_objects;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
commit;
---------------------------------------------------------------------------------------------------------------------
--测试表的大小及语句的效率
SQL> select bytes/1024/1024 from user_segments where segment_name='T';
BYTES/1024/1024
---------------
     264
SQL> select count(*) from t;
  COUNT(*)
----------
   2332096
统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      33350  consistent gets
          0  physical reads

---删除大量数据,再做试验如下,发现SEGMENT未见减少,依然是:
SQL> delete from t where rownum<=2000000;
已删除2000000行。
SQL> commit;
提交完成。
SQL> select bytes/1024/1024 from user_segments where segment_name='T';
BYTES/1024/1024
---------------
            264

---逻辑读也没有减少
SQL> select count(*) from t;
  COUNT(*)
----------
    332096
统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      33350  consistent gets
          0  physical reads
--用move重组数据后,高水平位释放(注意move操作会导致索引失效),segment占用的空间减少,逻辑读也一并减少。
SQL> alter table t move;
表已更改。
SQL> select bytes/1024/1024 from user_segments where segment_name='T';
BYTES/1024/1024
---------------
    38
SQL> select count(*) from t;
  COUNT(*)
----------
    332096
统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       4742  consistent gets
          0  physical reads
高水平位虚高的监控

未删除数据前
exec dbms_stats.gather_table_stats(ownname => 'HR',tabname => 'T',estimate_percent => 10,method_opt=> 'for all indexed columns',cascade=>TRUE) ; 


select num_rows,blocks from user_tab_statistics where table_name='T';
NUM_ROWS    BLOCKS

---------- ----------
2637370    37778


set autotrace off
delete from t where rownum<=2300000;
commit;

 

高水平位优化前
exec dbms_stats.gather_table_stats(ownname => 'LJB',tabname => 'T',estimate_percent => 10,method_opt=> 'for all indexed columns',cascade=>TRUE) ; 


select num_rows,blocks from user_tab_statistics where table_name='T';
  NUM_ROWS     BLOCKS
---------- ----------
     635730     37778

结论,异常的NUM_ROWS/BLOCKS往往是判断高水平位异常的入口,NUM_ROWS与BLOCKS的比例需要结合块大小和表每行的字节数来判断,最好是有正常状态的基数作为判断依据。

 

参考至:《基于案例学习sql优化》梁剑斌著

如有错误,欢迎指正

邮箱:czmcj@163.com


    

Oracle表高水平位的优化与监控

  • 0

    开心

    开心

  • 0

    板砖

    板砖

  • 0

    感动

    感动

  • 0

    有用

    有用

  • 0

    疑问

    疑问

  • 0

    难过

    难过

  • 0

    无聊

    无聊

  • 0

    震惊

    震惊

版权所有 IT知识库 CopyRight © 2009-2015 IT知识库 IT610.com , All Rights Reserved. 京ICP备09083238号