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

高水平位虚高的案例

--构造表
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