⛳️ 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 必须开启行迁移功能。
