详细聊聊Oracle表碎片对性能有多大的影响
前言
本文通过对Oracle 表碎片整理，对比了前后对数据库性能的影响。
?? 1.创建测试表
?1.1 建立表空间
SYS@EDB> select TABLESPACE_NAME,FILE_NAME from dba_data_files;
?1.2 创建ASSM表空间
CREATE TABLESPACE “JEAMES” DATAFILE ‘/u01/app/oracle/oradata/EDB/jeames01' SIZE 50M LOGGING ONLINE PERMANENT BLOCKSIZE 8192 EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO
?1.3 创建表及索引
##创建测试表t1,id列创建索引in_t1_id create table t1 tablespace JEAMES as select level as id from dual connect by level<=300000; create index in_t1_id on t1(id); analyze table t1 compute statistics; select count(*) from t1;
?? 2.查看表统计信息
select sum(bytes)/1024/1024 from dba_segments where segment_name=‘T1';
select sum(bytes)/1024/1024 from dba_segments where segment_name=‘IN_T1_ID';
SELECT blocks, empty_blocks, num_rows FROM user_tables WHERE table_name =‘T1';
总结：
查看表T1,段4M, 占用473个数据块,39个空块,索引IN_T1_ID段6M；
?? 3.空块占用空间
查看没有数据的块占用的空间
DBMS_STATS 包无法获取 EMPTY_BLOCKS 统计信息，所以需要用 analyze 命令再收集一次统计信息，估算表在高水位线下还有多少空间可用 ，这个值应当越低越好，表使用率越接近高水位线，全表扫描所做的无用功也就越少! !
SELECT TABLE_NAME, (BLOCKS * 8192 / 1024 / 1024) - (NUM_ROWS * AVG_ROW_LEN / 1024 / 1024) “Data lower than HWM in MB” FROM USER_TABLES WHERE table_name = ‘T1';
?? 4.查看执行计划
查看全表扫描cost为131，基于成本
explain plan for select * from t1; select * from table(dbms_xplan.display);
?? 5.删除大量数据
删除大部分数据,并收集统计信息,查看T1占用数据块和空块都没有减少
delete from t1 where id>10;
analyze table t1 compute statistics; SELECT blocks, empty_blocks, num_rows FROM user_tables WHERE table_name =‘T1';
?? 6.再次查看执行计划
查看全表扫描cost为125，基于成本, 使用率几乎没有下降
explain plan for select * from t1; select * from table(dbms_xplan.display);
?? 7.再次空块占用空间
SELECT TABLE_NAME, (BLOCKS * 8192 / 1024 / 1024) - (NUM_ROWS * AVG_ROW_LEN / 1024 / 1024) “Data lower than HWM in MB” FROM USER_TABLES WHERE table_name = ‘T1';
?? 8.整理表碎片
开启行迁移 alter table t1 enable row movement; 降低水位线 alter table t1 shrink space; 关闭行迁移 alter table t1 disable row movement; SYS@EDB> select sum(bytes)/1024/1024 from dba_segments where segment_name=‘T1'
SELECT TABLE_NAME, (BLOCKS * 8192 / 1024 / 1024) - (NUM_ROWS * AVG_ROW_LEN / 1024 / 1024) “Data lower than HWM in MB” FROM USER_TABLES WHERE table_name = ‘T1';
收集统计信息
analyze table t1 compute statistics;
?? 9.效果确认
占用数据块及空闲数据块下降,并且cost使用也下降
SELECT TABLE_NAME, (BLOCKS * 8192 / 1024 / 1024) - (NUM_ROWS * AVG_ROW_LEN / 1024 / 1024) “Data lower than HWM in MB” FROM USER_TABLES WHERE table_name = ‘T1';
select blocks,empty_blocks,num_rows from user_tables where table_name=‘T1';
explain plan for select * from t1; select * from table(dbms_xplan.display);
?? 10.技能拓展
1.再用alter table table_name move 时，表相关的索引会失效，
所以之后还要执行 alter index index_name
rebuild online; 最后重新编译数据库所有失效的对象
2. 在用 alter table table_name shrink space cascade 时，
3. 他相当于 alter table table_name move 和
alter index index_name rebuild online. 所以只要编译数据库失效的对象就可以；
4. Move 会移动高水位，但不会释放申请的空间，是在高水位以下(below HWM)的操作。
5. shrink space 同样会移动高水位，
6. 但也会释放申请的空间，是在高水位上下(below and above HWM)都有的操作。
原理不一样，move 是以 block 为单位重组数据，
行的 rowid 都会跟着变化，而 shrink 是以”行“为单位重组
数据，他是根据复杂的算法从逻辑+物理重组数据
move 速度快于 shrink.
Move 相当于 从 segment 底部 move 到 头。
Shrink 相当于先 delete，然后再 insert 这样产生很多 undo,redo
通常首选 MOVE
语法：
alter table <table_name> shrink space [ <null> | compact | cascade ];
alter table <table_name> shrink space compcat;
k segment shrink 分为两个阶段：
1、数据重组(compact):通过一系列 insert、delete 操作，
将数据尽量排列在段的前面。在这个过程中需
要在表上加 RX 锁，即只在需要移动的行上加锁。由于涉及到 rowid 的改变，
需要 enable row movement.同时要 disable 基于 rowid 的 trigger.这一过程对业务影响比较小。
2、HWM 调整：第二阶段是调整 HWM 位置，释放空闲数据块。
此过程需要在表上加 X 锁，会造成表上的所有
DML语句阻塞。在业务特别繁忙的系统上可能造成比较大的影响。
Shrink Space语句两个阶段都执行。Shrink
Space compact 只执行第一个阶段。
如果系统业务比较繁忙，可以先执行 Shrink Space compact
重组数据,然后在业务不忙的时候再执行 Shrink
Space 降低 HWM 释放空闲数据块。shrink 必须开启行迁移功能。
总结
